Splunk Search
Highlighted

How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

Path Finder

Is it possible to write a search to show count values per hour '_time' bins for the last 12 hours as columns, sorted by a specific field. I also need to do this via tstats. What I want would look like the following...

Field  | Now    | -1h    | -2h    | -3h    | -4h    | -5h    | ...
value1 | count0 | count1 | count2 | count3 | count4 | count5 | ...
value2 | count0 | count1 | count2 | count3 | count4 | count5 | ...
value3 | count0 | count1 | count2 | count3 | count4 | count5 | ...
value4 | count0 | count1 | count2 | count3 | count4 | count5 | ...
value5 | count0 | count1 | count2 | count3 | count4 | count5 | ...
...    | ...    | ...    | ...    | ...    | ...    | ...    | ...
Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

SplunkTrust
SplunkTrust

Try like this (run anywhere sample, adjust per your need)

| tstats count WHERE index=_internal sourcetype=* earliest=-12h@h by _time sourcetype span=1h | eval period=floor((now()-_time)/3600) | eval period=if(period=0,"Now","-".period."h" )| chart limit=0 sum(count) over sourcetype by period | table sourcetype Now "-1h" "-2h" "-3h"...list all other hours. needed this table command for sorting of columns...

Updated
To use the table command with dynamic name of fields for hour bins. This requires that the time range is set in the time-range picker and not provided in-line in the search.

| tstats count WHERE index=_internal sourcetype=* by _time sourcetype span=1h | eval period=floor((now()-_time)/3600) | eval period=if(period=0,"Now","-".period."h") | chart limit=0 sum(count) over sourcetype by period | table sourcetype [| gentimes start=-1 | addinfo | eval t=mvrange(info_min_time,info_max_time,3600) | table t | mvexpand t | eval t=floor((now()-t)/3600) | reverse| eval t=if(t=0,"Now","-".t."h") | stats list(t) as search delim="," | nomv search ]

View solution in original post

Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

Path Finder

so do I need to change sourcetype in this case to my desired field name? I'm using a datamodel

0 Karma
Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

SplunkTrust
SplunkTrust

yes... you would need to change the WHERE clause to use your data model as well. The use of _time and span shoudl remain same.

0 Karma
Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

Path Finder

Is there any way I could make the last part (static strings) dynamic (Now "-1h" "-2h" "-3h"...) so that if I use this query for a dashboard and have inputs for either bin or earliest, it could change these on the fly?

0 Karma
Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

SplunkTrust
SplunkTrust

If you don't do that, the sort order would be different. It'll show columns sourcetype Now -1h -10h -11h -12h -2h -3h -4h.... If thats ok, you can replace last table command with | table sourcetype Now *

0 Karma
Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

Path Finder

Well I was thinking there might be a way to do it with a for each loop or something like it to append the period values together to create a giant string then use that as the parameters in the table command (which would be in correct order)... but i couldn't get it to work, idk

Only reason I ask is because if I want different time bins or to go back further than 12h I wouldn't want to have to re-edit the dashboard's query every time, I would want to use inputs for this.

0 Karma
Highlighted

Re: How to write a search to show count values per hour _time bins for the last 12 hours as columns, sorted by a specific field?

SplunkTrust
SplunkTrust

How about you give updated answer a try.

0 Karma