topic How to find 7days rolling events count in Splunk Search

How to find 7days rolling events count

mnj1809 — Sat, 18 Sep 2021 05:20:39 GMT

Hello,

I want to find the 7 days rolling sum as per the attached sample data. For example in the attached sample data, 7d_rolling_count for 18 Sep should be the sum of previous 7 today_count counts (i.e. from 17 Sep to 11 Sep ) and 7d_rolling_count for 17 Sep should be the sum of previous 7 days today_count (i.e. from 16 Sep to 10 Sep and so on. I am only concerned to calculate the rolling average till first 8 days (i.e till 11 Sep).

Thanks for your time in advance.

Re: How to find 7days rolling events count

ITWhisperer — Sat, 18 Sep 2021 09:25:33 GMT

| makeresults | eval _raw="max_time_each_day data_source today_count 7d_rolling_count Sep 18,2021 00:00:00 AM EDT XYZ 54732 458796 Sep 17,2021 18:28:48 PM EDT XYZ 43688 480685 Sep 16,2021 23:59:57 PM EDT XYZ 68091 475145 Sep 15,2021 23:59:58 PM EDT XYZ 126693 404557 Sep 14,2021 23:59:51 PM EDT XYZ 52283 415302 Sep 13,2021 23:59:57 PM EDT XYZ 57014 413114 Sep 12,2021 23:59:59 PM EDT XYZ 56279 406662 Sep 11,2021 23:59:51 PM EDT XYZ 54748 400261 Sep 10,2021 23:59:49 PM EDT XYZ 65577 387929 Sep 09,2021 23:59:59 PM EDT XYZ 62551 424401 Sep 08,2021 23:59:57 PM EDT XYZ 56105 467392 Sep 07,2021 23:59:51 PM EDT XYZ 63028 503296 Sep 06,2021 23:59:48 PM EDT XYZ 54826 547150 Sep 05,2021 23:59:56 PM EDT XYZ 49827 595720 Sep 04,2021 23:59:54 PM EDT XYZ 48347 645476 Sep 03,2021 23:59:52 PM EDT XYZ 53245 690273 Sep 18,2021 00:00:00 AM EDT PQR 99023 689248 Sep 17,2021 12:42:28 PM EDT PQR 99096 691634 Sep 16,2021 20:42:59 PM EDT PQR 98932 694056 Sep 15,2021 20:44:11 PM EDT PQR 98680 696617 Sep 14,2021 20:42:19 PM EDT PQR 98397 699328 Sep 13,2021 20:43:22 PM EDT PQR 98103 702110 Sep 12,2021 20:40:20 PM EDT PQR 98042 704888 Sep 11,2021 20:38:30 PM EDT PQR 97998 707618 Sep 10,2021 20:41:00 PM EDT PQR 101482 706782 Sep 09,2021 20:40:40 PM EDT PQR 101354 605428 Sep 08,2021 20:41:30 PM EDT PQR 101241 504187 Sep 07,2021 20:40:34 PM EDT PQR 101108 403079 Sep 06,2021 20:36:44 PM EDT PQR 100885 302194 Sep 05,2021 20:39:06 PM EDT PQR 100820 201374 Sep 04,2021 20:36:35 PM EDT PQR 100728 100646 Sep 03,2021 20:39:35 PM EDT PQR 100646 0" | multikv forceheader=1 | eval _time=strptime(max_time_each_day,"%b %d,%Y") | sort 0 _time | streamstats sum(today_count) as rolling_by_data_source time_window=8d by data_source | eval rolling_by_data_source=rolling_by_data_source-today_count | table max_time_each_day data_source today_count 7d_rolling_count rolling_by_data_source

Re: How to find 7days rolling events count

mnj1809 — Sat, 18 Sep 2021 10:28:09 GMT

Hello Dear,

I need the data to be sort by descending order of the max_time_each_day and by data_source. like below:

But the output of your query is as below:

Re: How to find 7days rolling events count

ITWhisperer — Sat, 18 Sep 2021 10:35:30 GMT

OK add the sort

| makeresults | eval _raw="max_time_each_day data_source today_count 7d_rolling_count Sep 18,2021 00:00:00 AM EDT XYZ 54732 458796 Sep 17,2021 18:28:48 PM EDT XYZ 43688 480685 Sep 16,2021 23:59:57 PM EDT XYZ 68091 475145 Sep 15,2021 23:59:58 PM EDT XYZ 126693 404557 Sep 14,2021 23:59:51 PM EDT XYZ 52283 415302 Sep 13,2021 23:59:57 PM EDT XYZ 57014 413114 Sep 12,2021 23:59:59 PM EDT XYZ 56279 406662 Sep 11,2021 23:59:51 PM EDT XYZ 54748 400261 Sep 10,2021 23:59:49 PM EDT XYZ 65577 387929 Sep 09,2021 23:59:59 PM EDT XYZ 62551 424401 Sep 08,2021 23:59:57 PM EDT XYZ 56105 467392 Sep 07,2021 23:59:51 PM EDT XYZ 63028 503296 Sep 06,2021 23:59:48 PM EDT XYZ 54826 547150 Sep 05,2021 23:59:56 PM EDT XYZ 49827 595720 Sep 04,2021 23:59:54 PM EDT XYZ 48347 645476 Sep 03,2021 23:59:52 PM EDT XYZ 53245 690273 Sep 18,2021 00:00:00 AM EDT PQR 99023 689248 Sep 17,2021 12:42:28 PM EDT PQR 99096 691634 Sep 16,2021 20:42:59 PM EDT PQR 98932 694056 Sep 15,2021 20:44:11 PM EDT PQR 98680 696617 Sep 14,2021 20:42:19 PM EDT PQR 98397 699328 Sep 13,2021 20:43:22 PM EDT PQR 98103 702110 Sep 12,2021 20:40:20 PM EDT PQR 98042 704888 Sep 11,2021 20:38:30 PM EDT PQR 97998 707618 Sep 10,2021 20:41:00 PM EDT PQR 101482 706782 Sep 09,2021 20:40:40 PM EDT PQR 101354 605428 Sep 08,2021 20:41:30 PM EDT PQR 101241 504187 Sep 07,2021 20:40:34 PM EDT PQR 101108 403079 Sep 06,2021 20:36:44 PM EDT PQR 100885 302194 Sep 05,2021 20:39:06 PM EDT PQR 100820 201374 Sep 04,2021 20:36:35 PM EDT PQR 100728 100646 Sep 03,2021 20:39:35 PM EDT PQR 100646 0" | multikv forceheader=1 | eval _time=strptime(max_time_each_day,"%b %d,%Y") | sort 0 _time | streamstats sum(today_count) as rolling_by_data_source time_window=8d by data_source | eval rolling_by_data_source=rolling_by_data_source-today_count | sort 0 -data_source -_time | table max_time_each_day data_source today_count 7d_rolling_count rolling_by_data_source