topic Re: Stats Max count for field values over time in Splunk Search

Stats Max count for field values over time

DanielFordWA — Thu, 18 Dec 2014 12:37:46 GMT

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

Re: Stats Max count for field values over time

pedromvieira — Mon, 28 Sep 2020 18:28:29 GMT

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

Re: Stats Max count for field values over time

DanielFordWA — Thu, 18 Dec 2014 14:14:25 GMT

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.

Re: Stats Max count for field values over time

pedromvieira — Thu, 18 Dec 2014 16:17:46 GMT

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

Re: Stats Max count for field values over time

DanielFordWA — Fri, 19 Dec 2014 10:15:23 GMT

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

Re: Stats Max count for field values over time

pedromvieira — Fri, 19 Dec 2014 12:22:06 GMT

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

Re: Stats Max count for field values over time

DanielFordWA — Fri, 19 Dec 2014 13:38:11 GMT

Works like a charm, Thanks very much for the help!