Splunk Search

Using summary index data to compare event count between different days

capilarity
Path Finder

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!!

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

capilarity
Path Finder

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())

0 Karma

somesoni2
Revered Legend

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
0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...