Let's say on Splunk, I have a table with the fields 'month', 'year', and 'count'. I want the month corresponding to the max count for each year. So, the resulting table should only have one month per year.
I've tried using the stats and chart max functions, but I can't figure out how to use them to get what I want, or if it's even possible.
Is there any way to accomplish this using Splunk?
I ended up using the streamstats command.
Given a table with fields month,year, and count,
<some search>
| streamstats max(count) as mc by year
| sort +year, -count
| streamstats first(mc) as mc
| where count = mc
Essentially, I'm using streamstats to max across each month in each year, storing a running max for each entry as a new column. Then, I sort it so that the largest max count is at the top of each year group, so that I can then select the first one as the max entry.
I also had the same requirement. I had log data with the fields 'loadtime', 'application', and 'username' fields. First I wanted to compute the maximum value of loadtime for all application. Then,create a table/chart which should contain a single row for each application having application name and maximum load time. Table should also have user field's value for the maximum loadtime calculated for each application. Below is the splunk query which I used for achieving above:
search_string|streamstats max(loadtime) as max_time by application|sort +application -loadTime|streamstats first(max_time) as max_time by application|where loadtime=max_time|table application,max_time,username
