Splunk Search

How do you aggregate logic based on dates?

joydeep741
Path Finder

I have a query which gives a "per day count of a particular field" in the last 60 days.

Example:
TIME COUNT
01-11-2018 43
01-11-2018 66
01-11-2018 87
.
.
.
30-12-2018 76
31-12-2018 66

Now, I wish to SUM these counts in the following way:
- SUM of first 10 days
- SUM of first month
- SUM of second month

Can I do that in a single query ?

Tags (3)
0 Karma

woodcock
Esteemed Legend

Add something like this to the end of your existing search:

| eval thisMonthStart=relative_time(now(), "@mon")
| eval lastMonthStart=relative_time(now(), "-1mon@mon")
| eval first10days = if(((_time >= thisMonthStart) AND (_time <= relative_time(thisMonthStart, "+10d"))), 1, 0)
| eval thisMonth = if((_time >= lastMonthStart), 1, 0)
| eval lastMonth = if(((_time >= lastMonthStart) AND (_time <= thisMonthStart)), 1, 0)
| multireport
[ | where first10days==1 | stats sum(COUNT) AS first10days ]
[ | where thisMonth==1 | stats sum(COUNT) AS thisMonth ]
[ | where lastMonth==1 | stats sum(COUNT) AS lastMonth ]
0 Karma

woodcock
Esteemed Legend

Did this work for you, @joydeep741?

0 Karma

renjith_nair
Legend

@joydeep741,

Try

"your current search to get the count"|eval mon=strftime(strptime(TIME,"%d-%m-%Y"),"%m")|streamstats count as sno|streamstats count as month by mon
|streamstats count(eval(if(month==1,1,null()))) as month
|eventstats sum(eval(if(sno<11,count,null()))) as first10,sum(eval(if(month==1,count,null()))) as firstMonth,
            sum(eval(if(month==2,count,null()))) as secondMonth  |fields - mon,month,sno

Please note that , here first 10, first month, second month etc are based on the order of your events. This should work for any time range/duration you select

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

badarsebard
Communicator

Events typically have a field extracted from timestamps that represents the month of the timestamp. If your above data doesn't have this then you'll need to use a rex or eval to get it. Then, in order to get the "first ten days" I'd suggest adding a fake zeroth month to that field for those days. Then getting your sums is a matter of splitting a stats sum by month.

Assuming you have the month and day fields, try something like the following:
| eval month=if(month == 11 AND day < 11, mvappend(month-1), month)
| stats sum(count) by month

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...