Splunk Search

Joining accelerated data models using tstats

Contributor

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 😞

0 Karma
1 Solution

SplunkTrust
SplunkTrust
 | 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.

View solution in original post

SplunkTrust
SplunkTrust

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

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

alt text

0 Karma

SplunkTrust
SplunkTrust

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

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Splunk Employee
Splunk Employee

Ok I reupvoted 🙂

0 Karma

SplunkTrust
SplunkTrust
 | 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.

View solution in original post

Contributor
  • 1 thanks! This works.
0 Karma

Splunk Employee
Splunk Employee

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...