topic Re: Using summary index data to compare event count between different days in Splunk Search

Using summary index data to compare event count between different days

capilarity — Tue, 29 Sep 2020 11:00:57 GMT

I'm using a cool search I found on Answers to compare the event count from yesterday to the same day last week for our DC's
This search works really well and I have created a dashboard showing which servers are generating the most events, and more importantly, any which show a reduction in their activity.
The problem is the search is really big!! 25 - 30 million events and takes an age to run. I scheduled it to run overnight, but still takes a while per server to complete. Running it during the day takes forever....

So I have tried Summary indexing, I have it running well, and can get all the data in fraction of the time for adhoc serches, but I can't get it to run the same comparison search. Problem is, the summary index has an event count field (psrsvd_gc) and I can get an event count using the orig_host field, but I can't work out how to plug them into this search to compare yesterday with last week:

index=wineventlog sourcetype="XmlWinEventLog:Security" host=DOMAINCONTROLLER earliest=-8d@d latest=@d | eval WhichDay = case(_time>=relative_time(now(),"-1d@d"),"Yesterday", _time<=relative_time(now(),"-7d@d"),"LastWeek",1==1,null()) | stats count(eval(WhichDay=="Yesterday")) as Yesterday count(eval(WhichDay=="LastWeek")) as LastWeek | eval ChangePercent = (((Yesterday/LastWeek)-1)*100) |table ChangePercent

The summary index search:

index=wineventlog source="WinEventLog:Security" host="DC"| sistats count by host

and this is the comparison search using the summary index:

index=summary source="Summary - DC Daily Event Count" orig_host=DOMAINCONTROLLER earliest=-8d@d latest=@d | eval WhichDay = case(_time>=relative_time(now(),"-1d@d"),"Yesterday", _time<=relative_time(now(),"-7d@d"),"LastWeek",1==1,null()) | stats count(eval(WhichDay=="Yesterday")) as Yesterday count(eval(WhichDay=="LastWeek")) as LastWeek | eval ChangePercent = (((Yesterday/LastWeek)-1)*100) |table ChangePercent

Any help gratefully received!!

Re: Using summary index data to compare event count between different days

somesoni2 — Wed, 14 Sep 2016 16:01:41 GMT

Give this a try

index=summary source="Summary - DC Daily Event Count" orig_host=DOMAINCONTROLLER earliest=-8d@d latest=@d | eval WhichDay = case(_time>=relative_time(now(),"-1d@d"),"Yesterday", _time<=relative_time(now(),"-7d@d"),"LastWeek",1==1,null()) | where isnotnull(WhichDay) | chart count over index by WhichDay  | eval ChangePercent = (((Yesterday/LastWeek)-1)*100) |table ChangePercent

Re: Using summary index data to compare event count between different days

capilarity — Tue, 29 Sep 2020 11:01:21 GMT

Thanks, works like a dream!!

There was a typo in the my original search, so for the benefit of anyone else using this, if you want to compare yesterday with the same day a week ago, the "WhichDay" eval statement should use -1d@d for "Yesterday" and -8d@d for "LastWeek" or you're comparing the wrong days

eval WhichDay = case(_time>=relative_time(now(),"-1d@d"),"Yesterday", _time<=relative_time(now(),"-8d@d"),"LastWeek",1==1,null())