How do you compare multiple events to filter out the same host with different field values?

[RyanDonnelly22]

I have alert logs coming in from an AV tool and when a tech is working on an alert assigned it to themselves, it generates a new log file, same when it is closed.

This is the basic search I have for all the events:

index="AV"

|rename assignedTo.username as Owner

| rename alertTypeDetails.detail.agenthostname as agenthostname
|rename alertTypeDetails.source as source
| eval "Source"=coalesce(source,agenthostnamee," N/A ")
| rename alertTypeDetails.detail.virus as virus
| rename alertTypeDetails.detail.category as category
| eval "Malware"=coalesce(iocnames, virus, category, " N/A ")
| eval Owner=if(isnull(Owner)," ",Owner)

| eval Time=strftime(_time, " %m/%d/%Y %H:%M:%S")
| stats values(risk) as Severity values(message) as Message values(Malware) as Malware values(Owner) as Owner values(state) as Status values(customer_id) as Helix values(Time) as Time count by Source

| sort -Status

I want to exclude the hosts that have additional events where the values of Owner is not " " and the Status is not Open, so I can just see the new events that haven't been assigned or closed yet.

[RyanDonnelly22 · Accepted solution]

I was able to find an answer.

I need to use the 'where' command at the end of my search

| stats values(risk) as Severity values(message) as Message values(Malware) as Malware values(Owner) as Owner values(state) as Status values(customer_id) as Helix values(Time) as Time count by Source
| where Status !="Closed" and Owner=" "
| sort -Status

[RyanDonnelly22]

Here is an example of the results we see:

(first host, we would want to filter down to just see hosts like this)

Source: 10.0.0.14 Severity: Medium
Message: FIREEYE NX ALERT [SmartVision-Event] Malware: user enumeration attempt

Owner: Status: Open Time: 8/8/2019 11:37

count: 1

(second and third host, would want to filter out all of these due to one of their Owner fields being full and one of the Status fields being set to Closed)

Source: 10.0.0.11 Severity: Low

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user2@corp.com Status: Closed Time: 8/8/2019 10:07

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user2@corp.com Status: Open Time: 8/8/2019 10:27

count: 2

Source: 10.0.0.10 Severity: Low

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user1@corp.com Status: Closed Time: 8/8/2019 10:27

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user1@corp.com Status: Open Time: 8/8/2019 11:12

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user1@corp.com Status: Time: 8/8/2019 11:27
count: 3

[vik_splunk]

Hi @RyanDonnelly22 , a few sample events would help correlate with the query posted.

[RyanDonnelly22]

Here is an example looking at the result of the current search we use. We would want to be able to filter it out to only the ones that are just open:

(first host, we would want to filter down to see events like this)

Source: 10.0.0.14 Severity: Medium
Message: FIREEYE NX ALERT [SmartVision-Event] Malware: user enumeration attempt

Owner: Status: Open Time: 8/8/2019 11:37

count: 1

(second and third host, would want to filter out all of these due to the Owner field being full in at least one of the events, and one of the Status fields being set to Closed)

Source: 10.0.0.11 Severity: Low

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user2@corp.com Status: Closed Time: 8/8/2019 10:07

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user2@corp.com Status: Open Time: 8/8/2019 10:27
count: 2

Source: 10.0.0.10 Severity: Low

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user1@corp.com Status: Closed Time: 8/8/2019 10:27

Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: user1@corp.com Status: Open Time: 8/8/2019 11:12
Message: FIREEYE NX ALERT [Riskware-Callback] Malware: adware.downware

Owner: Status: Time: 8/8/2019 11:27
count: 3