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

[ymourtaza]

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!

[hettervik]

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