Splunk Search

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

RyanDonnelly22
Explorer

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.

Tags (1)
0 Karma
1 Solution

RyanDonnelly22
Explorer

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

View solution in original post

0 Karma

RyanDonnelly22
Explorer

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

0 Karma

RyanDonnelly22
Explorer

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

0 Karma

vik_splunk
Communicator

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

0 Karma

RyanDonnelly22
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...