Calculations off of results of two stats searches

[hlarimer]

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.

[richgalloway]

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

[MuS · Accepted solution]

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]

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?

[MuS]

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 🙂

[hlarimer]

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'.'

[MuS]

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

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

[hlarimer]

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?