Eval results and remove results based on conditions

[ARothman]

The goal of my search is to

1) display the details

2) show the count of viruses which have not been handled by our anti-virus

I will give examples of the fields I am concerned over and how I would like this to work below:

EventID="390730"; EventType="Viruses/spyware"; Action="Blocked"; ComputerName="MYCOMPUTER1"; EventTime="EARLIER DATE/TIME"; Status="Cleanable"; EventName="VIRUS1";

EventID="390739"; EventType="Viruses/spyware"; Action="Cleaned up"; ComputerName="MYCOMPUTER1"; EventTime="LATER DATE/TIME"; Status="Resolved"; EventName="VIRUS1";

So, I've realized I'm going to have to do a sub-search, but I can't quite understand how I would go about doing it. I want to be able to completely remove search results if the above situation exists... the situation being that a 'VIRUS1' was found on 'MYCOMPUTER1' and that it was Blocked and Cleanable... a later event showed that the 'VIRUS1' on 'MYCOMPUTER1' was Cleaned up and the event has been Resolved. By doing this, I can then have a list of viruses that the AV was not able to automatically cleanup and will need to be addressed by a support technician.

Help and tips will be greatly appreciated - Thanks.

[sanjayg]

Hi,

Could you please share what did you use instead of "Status" in latest command, to get the correct output for the above query.

"One other thing I noticed, which was causing me some grief, was that I was including 'Status' in the stats, so this was also causing me to see results which were unwanted."

Regards,
SG.

[jonuwz · Accepted solution]

So if you tag what's OK, then look for the latest OK-ness based on what makes the event unique ( I'm assuming ComputerName EventName and EventType ) then you can eliminate what's no longer an issue.

... | eval status=if((Action=="Blocked" AND Status=="Cleanable") OR (Action=="Cleaned up" AND Status=="Resolved"),"ok","issue") | stats latest(status) as current_status latest(EventTime) as LastEvent by ComputerName EventName EventType | where current_status=="issue"

[ARothman]

Looks like I'll be needing to update my 4.3 to 4.3.3 then - thanks again for the help

[jonuwz]

Thanks for the link. It's fixed in 4.3.3, so that explains why I haven't seen the problem.

[ARothman]

Thanks jonuwz, I'll do some playing around with that.

I found this article when I noticed that 'latest()' wasn't returning any results:
http://splunk-base.splunk.com/answers/42084/latest-function-in-stats-not-working-without-earliest

One other thing I noticed, which was causing me some grief, was that I was including 'Status' in the stats, so this was also causing me to see results which were unwanted.

[jonuwz]

you could do

values(EventTime) as LastEvent | eval LastEvent=mvindex(LastEvent,-1)

Not tested, but theoretically if should pop off the last non-null EventTime returned by the search..

Actually you might need mvindex(LastEvent,0) - depends if the results are in chronological order or not

Out of interest - where did you get the info about latest() being bugged ?

[ARothman]

When I saw this I kind of felt silly for not figuring it out on my own 😉

This almost works - problem I run into is that 'latest()' is currently bugged and only works if 'earliest()' is also included... which of course completely defeats the purpose.

I also changed it around a little bit and this -would- work, if latest wasn't bugged. Time for some more trial & error:

eval status=if(Status=="Not cleanable" OR Status=="Cleanup failed" OR Status=="Restart required","issue","ok") | stats latest(status) as current_status by Region ComputerName EventName Status | where current_status=="issue"