How do i display the latest event from two event IDs by computer name?

[willsy]

Hello,

I am trying to complete a query that allows me to see both the latest failed and successful backups from event logs for each computer. The issue i am having is that when i run the query i get both the failed and successful backups for the computer not just the most recent failure or success.

for example if server A fails to backup, but then the backup is re-run and it passes i only want to see that it passes, Or on the flip side to that, if Server A successfully backs up and then fails at a different time, i would like to see that its most recent event is a failure.

in my example eventcode 21 is a success, event code 22 is a failure.

index=windows_health-servers sourcetype="WinEventLog:Application" (EventCode=21 OR EventCode=22)
| where match(Machine_BackedUp,"DC(A|P)OV\w{2}\d{3}")

| eval Outcome=if(EventCode=21,"Success","Fail")
|chart values(Machine_BackedUp) as "Computer Name" latest(EventCode) as EventCode latest(_time) as Date by Outcome
| fields - EventCode
| convert ctime("Date")

Any help is greatly appreciated.

Willsy

[renjith_nair · Accepted solution]

@willsy,
Try moving the chart/stats before the Outcome eval

index=windows_health-servers sourcetype="WinEventLog:Application" (EventCode=21 OR EventCode=22) 
| where match(Machine_BackedUp,"DC(A|P)OV\w{2}\d{3}") 
| stats latest(EventCode) as EventCode,latest(_time) as Date by Machine_BackedUp
| eval Outcome=if(EventCode=21,"Success","Fail") 
| fields - EventCode 
| convert ctime("Date")|rename Machine_BackedUp as "Computer Name"

[willsy]

Thats certainly alot better than what i had before, so thank you for that. The individual events are there but i still have in the search

DCAOVSG001 failed backup 12/06/2018
DCAOVSG001 successfull backup 12/07/2018

is there a way, that i can only see the very latest one? so in this case i would only see the successful backup as opposed to both?

[renjith_nair]

@willsy, are you adding date or any other field in the by clause?

Just to test, can you try this ?

index=windows_health-servers sourcetype="WinEventLog:Application" (EventCode=21 OR EventCode=22) 
 | where match(Machine_BackedUp,"DC(A|P)OV\w{2}\d{3}") 
 | stats latest(EventCode) as EventCode by Machine_BackedUp

Is it displaying both eventcodes for machine DCAOVSG001 ?

[willsy]

Hello, thank you for getting back to me,
Thats working as follows;

Machine Backedup Event Code
DCAOVSG001 22
DCAOVSG002 21
DCAOVSG003 21

Which is the basis of what i am after, i just need to change the 21 to pass, 22 to fail message and then add a date.

So what ive done is added
| eval Outcome=if(EventCode=21,"Success","Fail")
| fields - EventCode
| convert ctime("Date")|rename Machine_BackedUp as "Computer Name"

onto the bottom of your search so that it looks like this:

index=windows_health-servers sourcetype="WinEventLog:Application" (EventCode=21 OR EventCode=22)
| where match(Machine_BackedUp,"DC(A|P)OV\w{2}\d{3}")
| stats latest(EventCode) as EventCode by Machine_BackedUp
| eval Outcome=if(EventCode=21,"Success","Fail")
| fields - EventCode
| convert ctime("Date")|rename Machine_BackedUp as "Computer Name"

i need to sort the time out by you my good sir are both a scholar and a gent. Thank you ever so much.