How to search and compare virus alert data to see if a client had an infection that was not removed, but was successfully removed later?

[zimmermann_0]

Hi all

We have virus alerts in Splunk. What I would like to do is to create on overview to check if a certain client had an infection where AV was not able to remove it, but maybe a couple of hours later, the AV was able to successfully remove it. The target would be a list of "really" infected devices.

Let's assume these two example events happen:

First Event:

Mar  4 09:32:07 Malware detected (Malware Name: BAD VIRUS, 2. Computer name: COMPUTER, Detection time(UTC time): 3/3/2016 10:07:53 AM, Malware file path: FILEPATH, Remediation action: Quarantine, Action status: Failed)

And then a bit later a 2nd one occurs:

Mar  4 12:32:07 Malware detected (Malware Name: BAD VIRUS, 2. Computer name: COMPUTER, Detection time(UTC time): 3/3/2016 11:07:53 AM, Malware file path: FILEPATH, Remediation action: Quarantine, Action status: Succeeded)

The following fields I have:
_time
malware --> Malware Name
computer --> the affected device
action_status --> Succeeded or Failed

Basically, the comparison of the two events should be like:
show devices where
malwareOLD = malwareNEW
computerOLD = computerNEW
_timeOLD different than _timeNEW
action_statusOLD = Failed and action_statusNEW IS NOT Succeeded

I hope you understand what I mean.

[woodcock · Accepted solution]

Avoid transaction whenever possible so try this:

... | stats values(*) dc(_time) AS numTimes earliest(status) AS firstStatus latest(status) AS lastStatus BY computer malware | where numTimes>1 and firstStatus="Falled" AND NOT lastStatus="Succeeded"

Or maybe even just this:

... | stats values(*) dc(_time) AS numTimes BY computer malware | where numTimes>1 AND status="Falled" AND NOT status="Succeeded"

[DMohn]

You could try using the transactioncommand here ...

 your_base_search | transaction malware, computer endswith="Succeeded" keepevicted=true | search closed_txn=0

[zimmermann_0]

thanks to both, woodcock's solution seems to do what I need 🙂