Splunk Search

Stats Max count for field values over time

DanielFordWA
Contributor

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

Tags (3)
0 Karma
1 Solution

pedromvieira
Communicator

You could use timechart or stats to achieve that.
Just replace my first line with your base search.

View solution in original post

pedromvieira
Communicator

You could use timechart or stats to achieve that.
Just replace my first line with your base search.

DanielFordWA
Contributor

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.

0 Karma

pedromvieira
Communicator

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
0 Karma

DanielFordWA
Contributor

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

0 Karma

pedromvieira
Communicator

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
0 Karma

DanielFordWA
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...