Splunk Search

Eval results and remove results based on conditions

Path Finder

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.

Tags (3)
0 Karma
1 Solution

Influencer

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"

View solution in original post

New Member

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.

0 Karma

Influencer

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"

View solution in original post

Path Finder

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

0 Karma

Influencer

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

0 Karma

Path Finder

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.

0 Karma

Influencer

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 ?

Path Finder

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 currentstatus by Region ComputerName EventName Status | where currentstatus=="issue"

0 Karma