Manipulating eval and stats to get desired results

[Robbie1194]

Hi guys,

I'm trying to search our Qualys vulnerability data to the average cvss score for all vulnerabilities with the serverity high or critical, however, I want my average to be done over ALL of our qualys assets, not just the devices that have a high or critical vulnerability. For example,

| from datamodel:"Vulnerabilities"."Vulnerabilities"
| stats dc(IP) as IP_count, values(severity) as severity, values(cvss) as cvss
| search severity=high OR severity=critical
| stats values(IP_count) as IP_count, sum(cvss) as cvss by severity
| eval average = (cvss/IP_count)
| table average, severity

I've tried using the above search to distinct count ALL IP's then once I've got that value, i've tried to filter to only the the IPs that have high or critical severity vulns. I've then tried to use an eval statement to average this to give me my desired end result but nothing seems to be working because of the way that Splunk passes through it's stats values. I've also tried using appendcols and couldn't get it working either. Does anyone have any ideas/suggestions on how if/how this is possible?

Cheers!

[woodcock]

Try this:

| from datamodel:"Vulnerabilities"."Vulnerabilities" 
| multireport
[ search severity=high OR severity=critical  | stats dc(IP) AS severe ]
[ stats dc(IP) AS total ]
| eval average = severe/total

[somesoni2]

Give this a try

| from datamodel:"Vulnerabilities"."Vulnerabilities" 
| statsvalues(cvss) as cvss by severity IP
| eval higherSevcvss=if(severity="high" OR severity="critical",cvss,null())
| stats dc(IP) as IP_count, sum(higherSevcvss) as cvss by severity
| eval average = (cvss/IP_count) 
| table average, severity