Joining accelerated data models using tstats

[himynamesdave]

Hi guys -

I have 3 data models, all accelerated, that I would like to join for a simple count of all events (dm1 + dm2 + dm3) by time.

3 single tstats searches works perfectly.

Search 1
| tstats summariesonly=t count from datamodel=DM1 where (nodename=NODE1) by _time

Search 2
| tstats summariesonly=t count from datamodel=DM2 where (nodename=NODE2) by _time

Search 3
| tstats summariesonly=t count from datamodel=DM3 where (nodename=NODE3) by _time

However, I'm not quite sure of how (and what the recommended approach) to join by count them is. Join, append, multisearch, eval, etc are all failing me 😞

[starcher]

using the append command runs into sub search limits. You should use the prestats and append flags for the tstats command.

| tstats prestats=t summariesonly=t count from datamodel=DM1 where (nodename=NODE1) by _time, nodename | tstats prestats=t summariesonly=t append=t count from datamodel=DM2 where (nodename=NODE2) by _time, nodename | tstats prestats=t summariesonly=t append=t count from datamodel=DM3 where (nodename=NODE3) by _time, nodename | stats count by _time, nodename

[himynamesdave]

This solution also works (cannot mark 2 answers as correct, sadly). Thanks to the awesome Answers Community, as always.

[starcher]

True though technically one that gets around the append command susbearch/limits is 'more correct' for larger data sets 😉

[dwaddle]

[dwaddle]

No, I'm pretty sure this does work, as long as you're willing to live with prestats=true. Try this on for size:

| tstats prestats=true count where index=_internal by index 
| tstats prestats=true append=true count where index=* by index 
| stats count by index

[himynamesdave]

This solution also works (cannot mark 2 answers as correct, sadly). Thanks to the awesome Answers Community, as always.

[starcher]

I had originally pasted the actual command without the append=t. That has been fixed.

[esix_splunk]

I downvoted this post because not a valid solutions, tstats has to be at the beginning of the search pipeline.

[starcher]

It is a valid solution. I just failed to paste the copy with the append=t in the search example though I mentioned it in the comment. I have fixed the example.

[esix_splunk]

Ok I reupvoted 🙂

[DalJeanis · Accepted solution]

 | tstats summariesonly=t count from datamodel=DM1 
     where (nodename=NODE1) by _time nodename
     | rename count as reccount
 | append
   [ | tstats summariesonly=t count from datamodel=DM2 
     where (nodename=NODE2) by _time nodename 
     | rename count as reccount]
 | append
   [ | tstats summariesonly=t count from datamodel=DM3 
     where (nodename=NODE3) by _time nodename 
     | rename count as reccount]
 | table _time nodename count

Now you have all three series joined together you can graph them alongside each other, or if you just want to add all three kinds of events, you can run that through this -

| stats sum(reccount) as reccount by _time

---

...and you could drop nodename out of most of the search that way.

[himynamesdave]

• 1 thanks! This works.

[esix_splunk]

DalJeanis version should work with some tweaking. To use tstats in this manner, it has to be in the beginning of the search pipeline. There should be some concern around the append and maximum number of events that it will return. For large datasets, this could be an issue..

Here's an example search using _internal..

| tstats count where index=_internal AND sourcetype=mongod by sourcetype 
| append 
    [| tstats count where index=_internal AND sourcetype=scheduler by sourcetype] 
| append 
    [| tstats count where index=_internal AND sourcetype=splunkd by sourcetype ] 
| append 
    [| tstats count where index=_internal AND sourcetype=splunk_version by sourcetype ]
| stats list(sourcetype) as ST dc(sourcetype) as st_DC 

For accelerated models, you should use the summariesonly=t for best results...

This will be super fast! Love it...