topic 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? in Splunk Search https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258835#M77565 <P>Try like this (run anywhere sample, adjust per your need)</P> <PRE><CODE>| 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... </CODE></PRE> <P><STRONG>Updated</STRONG><BR /> 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.</P> <PRE><CODE>| 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 ] </CODE></PRE> Tue, 24 Jan 2017 17:13:13 GMT somesoni2 2017-01-24T17:13:13Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258834#M77564 <P>Is it possible to write a search to show <STRONG>count values per hour '_time' bins</STRONG> for the last 12 hours <STRONG>as columns</STRONG>, sorted by a specific field. I also need to do this via tstats. What I want would look like the following...</P> <PRE><CODE>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 | ... ... | ... | ... | ... | ... | ... | ... | ... </CODE></PRE> Tue, 24 Jan 2017 17:02:06 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258834#M77564 matthewb4 2017-01-24T17:02:06Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258835#M77565 <P>Try like this (run anywhere sample, adjust per your need)</P> <PRE><CODE>| 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... </CODE></PRE> <P><STRONG>Updated</STRONG><BR /> 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.</P> <PRE><CODE>| 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 ] </CODE></PRE> Tue, 24 Jan 2017 17:13:13 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258835#M77565 somesoni2 2017-01-24T17:13:13Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258836#M77566 <P>so do I need to change sourcetype in this case to my desired field name? I'm using a datamodel</P> Tue, 24 Jan 2017 17:20:50 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258836#M77566 matthewb4 2017-01-24T17:20:50Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258837#M77567 <P>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.</P> Tue, 24 Jan 2017 17:42:22 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258837#M77567 somesoni2 2017-01-24T17:42:22Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258838#M77568 <P>Is there any way I could make the last part (static strings) <STRONG>dynamic</STRONG> (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?</P> Tue, 24 Jan 2017 18:07:02 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258838#M77568 matthewb4 2017-01-24T18:07:02Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258839#M77569 <P>If you don't do that, the sort order would be different. It'll show columns <CODE>sourcetype Now -1h -10h -11h -12h -2h -3h -4h...</CODE>. If thats ok, you can replace last table command with <CODE>| table sourcetype Now *</CODE></P> Tue, 24 Jan 2017 19:04:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258839#M77569 somesoni2 2017-01-24T19:04:12Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258840#M77570 <P>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</P> <P>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.</P> Tue, 24 Jan 2017 19:22:27 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258840#M77570 matthewb4 2017-01-24T19:22:27Z 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? https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258841#M77571 <P>How about you give updated answer a try.</P> Tue, 24 Jan 2017 20:02:46 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-show-count-values-per-hour-time-bins/m-p/258841#M77571 somesoni2 2017-01-24T20:02:46Z