How can I calculate field value percentages for multiple data sets?

[andrewgbennett3]

For the purpose of this question, a given event contains the following fields:
vulnerability name, data center, ip address

Events are generated on a vulnerability name basis, so that each event will have a unique vulnerability name but may have duplicate values as other events for ip address and data center. Each data center has a different number of ip addresses associated with it.

Goal:
Identify the count for 5 unique field values (vulnerability name) per data center. Calculate the percent of unique ip address field values per data center that also have one of the 5 specified vulnerability name field values.

Current search:

some search (vulnerability name=”vuln 1“ OR vulnerability name=”vuln 2“ OR vulnerability name=”vuln 3“ OR vulnerability name=”vuln 4“ OR vulnerability name=”vuln 5“) 
|stats count by Data Center,Vulnerability name

Current output:

Data Center     Vulnerability Name  Count

Data Center A       Vuln 1             75       
Data Center A       Vuln 3             20       
Data Center A       Vuln 4             6        
Data Center A       Vuln 5             15       
Data Center B       Vuln 2             75       
Data Center B       Vuln 5             18       
Data Center C      Vuln 3             24    

The problem with my search at this point is that it eliminates any events that do not contain one of the 5 unique vulnerabilities. Therefore, I cannot reference the total number of IP addresses that may belong to a data center in order to calculate a percentage.

Desired Output:

Data Center     Vulnerability Name  Count       Vulnerable IP Addresses

Data Center A       Vuln 1             75            37%
Data Center A       Vuln 3             20            10%
Data Center A       Vuln 4             6              3%
Data Center A       Vuln 5             15            7%
Data Center B       Vuln 2             75            12%
Data Center B       Vuln 5             18            3%
Data Center C      Vuln 3             24             48%

Note: For this sample desired output, Data Center A has 200 IP addresses, B has 600, and C has 100.

[somesoni2 · Accepted solution]

Give this a try

some search 
|stats count as Count by "Data Center","Vulnerability name"
| eventstats sum(Count) as Total by "Data Center"
| where 'Vulnerability name'="vuln 1" OR 'Vulnerability name'="vuln 2" OR 'Vulnerability name'="vuln 3" OR 'Vulnerability name'="vuln 4" OR 'Vulnerability name'="vuln 5"
| eval "Vulnerable IP Addresses"=round(Count*100/Total) | fields - Total

[andrewgbennett3]

Thanks for the quick response! That search does provide the desired format. The only issue is the number of events being referenced for the "Total by Data Center"

The number of IP addresses and the number of events is not equal. Because events are created per vulnerability, there can be 20+ events per ip address. The eval references the "Total" which is based off of ALL events which causes the percentage to be inaccurate.

[somesoni2]

So the percentage is calculated based on distinct IP addresses with that vulnerability? If yes, give this a shot

some search 
 |stats count as Count dc( by "Data Center","Vulnerability name" ip_address
 | eventstats dc(ip_address)  as TotalIP by "Data Center"
 | stats sum(Count) as Count max(TotalIP) as TotalIP dc(ip_address) as IPCount by "Data Center","Vulnerability name"
 | where 'Vulnerability name'="vuln 1" OR 'Vulnerability name'="vuln 2" OR 'Vulnerability name'="vuln 3" OR 'Vulnerability name'="vuln 4" OR 'Vulnerability name'="vuln 5"
 | eval "Vulnerable IP Addresses"=round(IPCount *100/TotalIP ) | fields - TotalIP IPCount

[andrewgbennett3]

This works perfectly, thanks so much!