Splunk Search

Calculations off of results of two stats searches

hlarimer
Communicator

I have 2 searches:

index=av_log sourcetype=sophos_threat_events | dedup ComputerName FullFilePath | stats count by ThreatType

and

search index=av_log sourcetype=sophos_threat_events Status = Resolved | dedup ComputerName FullFilePath | stats count by ThreatType  

Each search will create a results table showing 3 Threat types and a count for each. The first search is totals, the second search are resolved. I would like one table that would show each and show the percentage of Threats resolved for each ThreatType.

Tags (1)
0 Karma
1 Solution

MuS
Legend

Hi hlarimer,

try this:

search index=av_log sourcetype=sophos_threat_events Status="Resolved" | dedup ComputerName FullFilePath | stats count(eval(Status="Resolved")) AS resolved_count count AS Total by ThreatType | eval perc=resolved_count*100/Total | table Total resolved_count ThreatType perch

Maybe you need to adapt this search a bit because it is not tested 😉

cheers, Mus

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Perhaps something like this will do the job.

index=av_log sourcetype=sophos_threat_events | dedup ComputerName FullFilePath | stats count as ThreatCount by ThreatType | join ThreatType [search index=av_log sourcetype=sophos_threat_events Status = Resolved | dedup ComputerName FullFilePath | stats count as ResolvedCount by ThreatType] | table ThreatType ThreatCount ResolvedCount
---
If this reply helps you, Karma would be appreciated.

MuS
Legend

Hi hlarimer,

try this:

search index=av_log sourcetype=sophos_threat_events Status="Resolved" | dedup ComputerName FullFilePath | stats count(eval(Status="Resolved")) AS resolved_count count AS Total by ThreatType | eval perc=resolved_count*100/Total | table Total resolved_count ThreatType perch

Maybe you need to adapt this search a bit because it is not tested 😉

cheers, Mus

hlarimer
Communicator

I had to remove the Status="Resolved" from the initial search to start with the full results but otherwise this worked great (and removed a type from perc in the table command). Thank you for this solution.

An additional question, I would like to add an additional match to the eval (Status=Resolved) part of the search. I would like to match Status=Matched and UserName!=adm. Any idea how to do this?

0 Karma

MuS
Legend

Sure try this:

eval(Status=Resolved OR Status=Matched AND UserName!=adm)

And sorry for the typo 😉 writting SPL on an iPad is not that easy 🙂

0 Karma

hlarimer
Communicator

I was thinking that was the solution too but I am getting this message:

Error in 'stats' command: The eval expression for dynamic field eval(Status="Resolved" AND UserName=*adm*) is invalid. Error='The expression is malformed. An unexpected character is reached at 'adm'.'

0 Karma

MuS
Legend

sorry, format messed up in your comment.... so try this:

eval(Status="Resolved" AND UserName="*adm*")

0 Karma

hlarimer
Communicator

I'm not getting errors anymore but I'm still having issues with the last eval. The following two searches should return the same results , correct?

index=av_log sourcetype=sophos_threat_events | dedup ComputerName FullFilePath | stats count(eval(UserName="adm")) AS Resolved_by_Service_Desk

index=av_log sourcetype=sophos_threat_events | dedup ComputerName FullFilePath | search UserName=adm | stats count AS Resolved_by_Service_Desk

But the first result gives me 0 and the second gives me 44, any idea why?

0 Karma
Get Updates on the Splunk Community!

Bridging the Gap: Splunk Helps Students Move from Classroom to Career

The Splunk Community is a powerful network of users, educators, and organizations working together to tackle ...

Preparing your Splunk Environment for OpenSSL3

The Splunk platform will transition to OpenSSL version 3 in a future release. Actions are required to prepare ...

Unleash Unified Security and Observability with Splunk Cloud Platform

     Now Available on Microsoft AzureThursday, March 27, 2025  |  11AM PST / 2PM EST | Register NowStep boldly ...