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
@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,
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"
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?
@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 ?
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.