Splunk Search

How do I join two data models in a TSTATS without using JOIN or APPENDCOLS?

ymourtaza
New Member

Hello all,

I would like to pick the community's brains on this:

How do I join two data models in a TSTATS without using JOIN or APPENDCOLS?

Here are the actual searches that I have right now (one is using JOIN, one is using APPENDCOLS)

| tstats summariesonly=true latest(_time) as _time count AS "Count of Web" dc(Web.src) AS "Distinct Count of src" from datamodel=Web where (nodename = Web) groupby Web.src, Web.dest, Web.url, Web.http_user_agent, sourcetype, Web.user 
| rename Web.src as src_ip
| join type=outer src_ip 
    [| tstats count AS "Count of Allowed Traffic" dc(All_Traffic.src_ip) AS "Distinct Count of src_ip" from datamodel=Network_Traffic where (nodename = All_Traffic.Traffic_By_Action.Allowed_Traffic) (All_Traffic.dest_ip!="10.*") (All_Traffic.direction="out*") groupby All_Traffic.src_ip, All_Traffic.dest_ip, sourcetype, All_Traffic.action, _time]

| tstats summariesonly=true latest(_time) as _time count AS "Count of Web" dc(Web.src) AS "Distinct Count of src" from datamodel=Web where (nodename = Web) groupby Web.src, Web.dest, Web.url, Web.http_user_agent, sourcetype, Web.user
| rename Web.src as src_ip
| appendcols 
    [| tstats count AS "Count of Allowed Traffic" dc(All_Traffic.src_ip) AS "Distinct Count of src_ip" from datamodel=Network_Traffic where (nodename = All_Traffic.Traffic_By_Action.Allowed_Traffic) (All_Traffic.dest_ip!="10.*") (All_Traffic.direction="out*") groupby All_Traffic.src_ip, All_Traffic.dest_ip, sourcetype, All_Traffic.action, _time]
    | stats latest(_time) as _time index values(dest) as dest_ip values(web.url) as web.url count by src_ip

Thanks!

Tags (3)
0 Karma

hettervik
Builder

I think the way to go for combining tstats searches without limits is using "prestats=t" and "append=true". Note that you maybe have to rewrite the searches quite a bit to get the desired results, but it should be possible.

Something like so:

| tstats summariesonly=true prestats=t latest(_time) as _time count AS "Count of Web" dc(Web.src) AS "Distinct Count of src" from datamodel=Web where (nodename = Web) groupby Web.src, Web.dest, Web.url, Web.http_user_agent, sourcetype, Web.user
| tstats prestats=t append=true count AS "Count of Allowed Traffic" dc(All_Traffic.src_ip) AS "Distinct Count of src_ip" from datamodel=Network_Traffic where (nodename = All_Traffic.Traffic_By_Action.Allowed_Traffic) (All_Traffic.dest_ip!="10.*") (All_Traffic.direction="out*") groupby All_Traffic.src_ip, All_Traffic.dest_ip, sourcetype, All_Traffic.action, _time
| rename Web.* as *, All_Traffic.* as *
| table _time index dest url src_ip
| stats latest(_time) as _time index values(dest) as dest_ip values(url) as url count by src_ip

 

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...