I have the following data
_time Product count
21/10/2014 Ptype1 21
21/10/2014 Ptype2 3
21/10/2014 Ptype3 43
21/10/2014 Ptype4 6
21/10/2014 Ptype5 17
22/10/2014 Ptype1 18
22/10/2014 Ptype3 23
22/10/2014 Ptype5 26
23/10/2014 Ptype1 14
23/10/2014 Ptype2 8
23/10/2014 Ptype3 52
23/10/2014 Ptype4 4
23/10/2014 Ptype5 1
I would like to search over a period of time and show the max count by Product. with results displayed below.
_time Product count
21/10/2014 Ptype1 21
22/10/2014 Ptype5 26
23/10/2014 Ptype2 8
23/10/2014 Ptype3 52
23/10/2014 Ptype4 4
Is this possible?
Thanks,
Dan
You could use timechart or stats to achieve that.
Just replace my first line with your base search.
timechart
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Timechart
| inputlookup test.csv | convert mktime(_time) timeformat="%d/%m/%Y"
| timechart useother=f usenull=f cont=f span=1d sum(count) by Product
stats
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Stats
| inputlookup test.csv | convert mktime(_time) timeformat="%d/%m/%Y"
| convert ctime(_time) AS Date timeformat="%d/%m/%Y"
| stats sum(count) by Date Product
You could use timechart or stats to achieve that.
Just replace my first line with your base search.
timechart
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Timechart
| inputlookup test.csv | convert mktime(_time) timeformat="%d/%m/%Y"
| timechart useother=f usenull=f cont=f span=1d sum(count) by Product
stats
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Stats
| inputlookup test.csv | convert mktime(_time) timeformat="%d/%m/%Y"
| convert ctime(_time) AS Date timeformat="%d/%m/%Y"
| stats sum(count) by Date Product
Thanks for the reply. I cant get this working though.
Just to calirfy. If I search over the month of december, I would expect the below result.
21/10/2014 Ptype1 21
22/10/2014 Ptype5 26
23/10/2014 Ptype2 8
23/10/2014 Ptype3 52
23/10/2014 Ptype4 4
At the moment I am still gettting data for every day. The requirement is to get the max count by product over the time range and display what day that max occured on.
Try dedup. It will showcase the first occurrence.
Replace the first line with your base search.
| inputlookup test.csv | convert mktime(_time) timeformat="%d/%m/%Y"
| convert ctime(_time) AS Date timeformat="%d/%m/%Y"
| stats sum(count) AS # by Date Product
| stats max(#) AS # by Date Product
| sort Date - #
| dedup Date
Results:
Date Product #
21/10/2014 Ptype3 43
22/10/2014 Ptype5 26
23/10/2014 Ptype3 52
Thanks, during the time range the higest count for Ptype2 and Ptype4 are also on the 23/10/2014, so I would want the results to be
21/10/2014 Ptype1 21
22/10/2014 Ptype5 26
23/10/2014 Ptype2 8
23/10/2014 Ptype3 52
23/10/2014 Ptype4 4
Just change the sort order before the dedup.
| inputlookup test.csv | convert mktime(_time) timeformat="%d/%m/%Y"
| convert ctime(_time) AS Date timeformat="%d/%m/%Y"
| stats sum(count) AS # by Date Product
| stats max(#) AS # by Date Product
| sort Product - #
| dedup Product
| sort Date Product
Results
Date Product #
21/10/2014 Ptype1 21
21/10/2014 Ptype4 6
22/10/2014 Ptype5 26
23/10/2014 Ptype2 8
23/10/2014 Ptype3 52
Works like a charm, Thanks very much for the help!