search to show hosts missing specific winevent log

[jztilly]

Hi there,

I've got a basic search to provide the most recent timestamp for a successful backup using wineventlog data:

index="wineventlog" source="WinEventLog:Application" SourceName="Symantec System Recovery" host=*grp*
| search Message=*6C8F1F7E* OR Message=*6C8F1F7D* OR Message=*6C8F1F7A*
| dedup host
| table host, _time

However, I'm really struggling to come up with a search that shows me all the *grp* hosts whether they have the successful backup strings in the Message field  (*6C8F1F7E* or *6C8F1F7D* or *6C8F1F7A*) or not.

My closest attempt seems to be this:

index="wineventlog" source="WinEventLog:Application" SourceName="Symantec System Recovery" host=*pgrp* 
| eval success = case(Message like "%6C8F1F7E%",1,Message like "%6C8F1F7D%",1,Message like "%6C8F1F7A%",1,Message like "%",0)
| stats sum(success) as Successes by host
| where Successes < 1

My hope is for a table with the following columns:

• Host
• Last successful backup date/time or "N/A" if there was no successful backup in the selected timerange
• Days since last backup

Any help or advice would be greatly apprecated!

Cheers

[bowesmana · Accepted solution]

Something along the lines of

index="wineventlog" source="WinEventLog:Application" SourceName="Symantec System Recovery" host=*pgrp* 
| eval success = if(match(Message, "6C8F1F7E|6C8F1F7D|6C8F1F7A%"), 1, 0)
| stats max(_time) as LastBackup max(eval(if(success=1,_time, 0))) as LastSuccessfulBackup sum(success) as Successes by host
| fillnull value="N/A" LastSuccessfulBackup
| eval DaysSinceLastBackup=round((now()-LastBackup)/86400)

(Edit:Fixed typo in eval)

[jztilly]

Thanks for the reply! I've been trying to get that to produce a result but not having much luck.

The original error after copy/paste is: Error in 'eval' command: The expression is malformed. Expected ).

I've been going through line-by-line and got the second line working by changing it to this:

index="wineventlog" source="WinEventLog:Application" SourceName="Symantec System Recovery" host=*pgrp* 
| eval success = if(Message like "%6C8F1F7E%|%6C8F1F7D%|%6C8F1F7A%", 1, 0)

but each subsequent line fails from there.

edit : nope, my edit to line 2 doesn't work either 😁

[bowesmana]

@jztilly 

My bad - fixed some typos - I seem to remember hitting post just as I was running out the door 😞