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?

[matthewb4]

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

[somesoni2 · Accepted solution]

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 ]

[somesoni2]

How about you give updated answer a try.

[matthewb4]

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

[somesoni2]

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.

[matthewb4]

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?

[somesoni2]

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 *

[matthewb4]

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.