How to use dedup with two event IDs sensitive to time?

[SplunkLunk]

Good afternoon,

I don't think I'm going to explain this well, but I'll try. I'm currently running a search for Windows Updates status via the Windows System Event Log. EventID=20 is a failure. EventID=19 is a success. The current query looks like this:

index=[my index] host=* source=WinEventLog:System EventID=20 OR EventID=19 | xmlkv | search updateTitle!="Update for System Center Endpoint Protection*" updateTitle!="Windows Malicious Software Removal Tool*" updateTitle!="Definition Update for Microsoft Endpoint Protection*"
|sort -_time
|rename _time as Time updateTitle AS "Update Title"
|dedup 1 host "Update Title"
|table Time, host, Name, "Update Title", EventID
|convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

So, this provides only one entry per host in the results. However, what I really want from these results are only the EventIDs=20 and only if they don't have a EventID=19 that is more recent (that would indicate the update failed, but then was successful at some point). Only getting the events that are "20"s with no "19"s after that tells me the update still isn't installing.

I can't only display the EventIDs that come back "20" right now since there could be a new EventID "19". I was trying to use the stats(first) command somehow, but I wasn't having much luck. Advice?

[DalJeanis]

Use eventstats to find information about a group of records without altering the records.

index=[my index] host=* source=WinEventLog:System EventID=20 OR EventID=19 
| xmlkv 
| search updateTitle!="Update for System Center Endpoint Protection*" updateTitle!="Windows Malicious Software Removal Tool*" updateTitle!="Definition Update for Microsoft Endpoint Protection*"

| rename COMMENT as "Find the last 19, and last 20, then kill everything except a last 20 that is after all 19s)"
| eventstats max(eval(if(EventID=19,_time,null()))) as last19, max(eval(if(EventID=20,_time,null()))) as last20 by host updateTitle 
| where  EventID=20 AND _time=last20 AND (_time>last19 OR isnull(last19)) 

| rename _time as Time updateTitle AS "Update Title"
| table Time, host, Name, "Update Title", EventID
| convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

You might find these three lines easier to read instead of the one eventstats line above.

| eval time19=if(EventID=19,_time,null())
| eval time20=if(EventID=20,_time,null())
| eventstats max(time19) as last19, max(time20) as last20 by host updateTitle 

[SplunkLunk]

This works! I noticed that in my original search if I just added the |WHERE EventID=20 after the |table command I get the same results as your solution above. The search seemed to run faster that way. Are they both doing the same thing essentially? My query says to keep 1 entry of both host and "Update Title" if they are the same. Since I think dedup keeps the most recent event, wouldn't that pick the most recent 19 or 20 event. Then I exclude the 19s when displaying leaving only 20s which is what I want.