topic Re: Calculations off of results of two stats searches in Splunk Search

Calculations off of results of two stats searches

hlarimer — Tue, 18 Nov 2014 20:30:54 GMT

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.

Re: Calculations off of results of two stats searches

MuS — Tue, 18 Nov 2014 20:40:24 GMT

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

Re: Calculations off of results of two stats searches

richgalloway — Tue, 18 Nov 2014 20:40:55 GMT

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

Re: Calculations off of results of two stats searches

hlarimer — Tue, 18 Nov 2014 21:10:20 GMT

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?

Re: Calculations off of results of two stats searches

MuS — Tue, 18 Nov 2014 21:18:12 GMT

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 🙂

Re: Calculations off of results of two stats searches

hlarimer — Tue, 18 Nov 2014 21:40:10 GMT

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

Re: Calculations off of results of two stats searches

MuS — Tue, 18 Nov 2014 21:45:27 GMT

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

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

Re: Calculations off of results of two stats searches

hlarimer — Mon, 28 Sep 2020 18:13:12 GMT

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?