Yes definitely working too hard. You are a miracle worker. I played around with eventstats earlier on and couldn't get it to work for me. Added a few missing quotes and boom, 6 searches condensed into one!
The purpose of this report is to show accounts that were present at indexing yesterday but deleted over the course of the day and therefore not present today at time of indexing (once deleted they are gone from the database). The subsequent searches after finding the difference between yesterday and today were meant to decipher whether they were new or missing and then the last search just populates the list of those that were deleted. Your search did all of that.
Final search in case curious:
index="clients" clientType="Demo Account" earliest=-1d@d latest=now
| bin _time as Day span=1d
| eventstats min(Day) as Yesterday max(Day) as Today
| stats values(Day) as Day values(Yesterday) as Yesterday values(Today) as Today by account_number, name
| where mvcount(Day) = 1
| eval Flag=case(Today=Yesterday, "ERROR - Only a single Day found",
Today>Yesterday+86400, "ERROR - More than two days found",
Day=Today, "New",
Day=Yesterday, "Missing",
true(), "ERROR - Mental failure, seek counseling")
| where Flag = "Missing"
| fields - "Yesterday" "Today" "Day"
| rename name as "Location Name", account_number as "Account Number"
As those who are getting the results only need to know when there are "Missing" results I've added an addition |where condition and just setup an alert instead of a report.
Thank you so much for your help! 😄
-Megan
... View more