Splunk Search

Eval results and remove results based on conditions

ARothman
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

jonuwz
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

sanjayg
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

jonuwz
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"

ARothman
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

jonuwz
Influencer

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

0 Karma

ARothman
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

jonuwz
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 ?

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...