topic Re: How do you aggregate logic based on dates? in Splunk Search

How do you aggregate logic based on dates?

joydeep741 — Mon, 31 Dec 2018 06:03:43 GMT

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 ?

Re: How do you aggregate logic based on dates?

badarsebard — Mon, 31 Dec 2018 06:51:28 GMT

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

Re: How do you aggregate logic based on dates?

renjith_nair — Mon, 31 Dec 2018 07:28:00 GMT

@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

Re: How do you aggregate logic based on dates?

woodcock — Sun, 03 Feb 2019 01:19:00 GMT

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 ]

Re: How do you aggregate logic based on dates?

woodcock — Tue, 12 Feb 2019 00:03:59 GMT

Did this work for you, @joydeep741?