topic Re: Joining accelerated data models using tstats in Splunk Search

Joining accelerated data models using tstats

himynamesdave — Tue, 14 Feb 2017 18:16:33 GMT

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 😞

Re: Joining accelerated data models using tstats

DalJeanis — Tue, 14 Feb 2017 22:45:42 GMT

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

Re: Joining accelerated data models using tstats

starcher — Wed, 15 Feb 2017 00:42:05 GMT

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

Re: Joining accelerated data models using tstats

esix_splunk — Wed, 15 Feb 2017 02:05:08 GMT

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

Re: Joining accelerated data models using tstats

esix_splunk — Wed, 15 Feb 2017 02:08:47 GMT

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

Re: Joining accelerated data models using tstats

starcher — Wed, 15 Feb 2017 13:25:35 GMT

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

Re: Joining accelerated data models using tstats

starcher — Wed, 15 Feb 2017 13:28:01 GMT

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.

Re: Joining accelerated data models using tstats

dwaddle — Wed, 15 Feb 2017 13:33:01 GMT

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

Re: Joining accelerated data models using tstats

esix_splunk — Wed, 15 Feb 2017 14:26:47 GMT

Ok I reupvoted 🙂

Re: Joining accelerated data models using tstats

himynamesdave — Wed, 15 Feb 2017 15:31:02 GMT

1 thanks! This works.

Re: Joining accelerated data models using tstats

himynamesdave — Wed, 15 Feb 2017 15:33:03 GMT

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

Re: Joining accelerated data models using tstats

himynamesdave — Wed, 15 Feb 2017 15:33:19 GMT

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

Re: Joining accelerated data models using tstats

starcher — Wed, 15 Feb 2017 15:36:02 GMT

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

Re: Joining accelerated data models using tstats

dwaddle — Wed, 15 Feb 2017 15:38:44 GMT