How to add a chart that shows the average vulnerabilities per host grouped by Service

[gbhw]

Hi,

I created a vulnerability dashboard that looks like this:

VulnerabilityId, Host, Service
123, HostA, Mail
234, HostA, Mail
345, HostB, Mail
123, HostC, HR
234, HostC, HR

Now I want to add a chart that shows a vulnerability index per Service. It should show the average vulnerabilities per host grouped by Service.
- Service Mail got 3 vulnerabilities divided by 2 hosts = 1,5 per host
- Service HR got 2 vulnerabilities divided by 1 host = 2 per host

Any idea how I can achieve this?

[jacobpevans · Accepted solution]

Greetings @gbhw,

You're looking for the dc (distinct count) stats command. You can read more about stats commands here: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Stats

This is what you need for your search:

| stats dc(Host) as Host_Count dc(VulnerabilityId) as Vulnerability_Count by Service
| eval Vulnerabilites_Per_Host = Vulnerability_Count / Host_Count

Here's a full run-anywhere search demonstrating this with your sample data:

           | makeresults | eval VulnerabilityId="123", Host="HostA", Service="Mail"
| append [ | makeresults | eval VulnerabilityId="234", Host="HostA", Service="Mail" ]
| append [ | makeresults | eval VulnerabilityId="345", Host="HostB", Service="Mail" ]
| append [ | makeresults | eval VulnerabilityId="123", Host="HostC", Service="HR" ]
| append [ | makeresults | eval VulnerabilityId="234", Host="HostC", Service="HR" ]
| stats dc(Host) as Host_Count dc(VulnerabilityId) as Vulnerability_Count by Service
| eval Vulnerabilites_Per_Host = Vulnerability_Count / Host_Count