I have the query below and I'm trying to get the count of hosts affected by the vulnGrouping split by priority. Whereas currently the query return the total count for both combined.
The SPL is grouping like software by a high level name (i.e., Adobe, Cisco Software, Oracle Software, etc.), then I have applied logic to determine the Risk level. Lastly getting a count of the IPv4 addresses affected.
| eval vulnGrouping=case(plugin_name like "Adobe%", "Adobe", plugin_name like "Google%", "Google Chrome", plugin_name like "Oracle%", "Oracle Software", plugin_name like "Cisco%", "Cisco Software")
| stats values(priority) dc(ipv4) by vulnGrouping
The output is similar to below:
vulnGrouping | values(priority) | dc(ipv4) |
Adobe | Critical High | 100 |
Google Chrome | Critical High | 500 |
Where I'd like to be is something like this:
vulnGrouping | values(priority) | dc(ipv4) |
Adobe | Critical High | 75 25 |
Google Chrome | Critical High | 150 350 |
Any ideas or help is greatly appreciated.
Do it like this
| eval vulnGrouping=case(plugin_name like "Adobe%", "Adobe", plugin_name like "Google%", "Google Chrome", plugin_name like "Oracle%", "Oracle Software", plugin_name like "Cisco%", "Cisco Software")
| stats dc(ipv4) as IPs by vulnGrouping priority
| stats list(priority) as priority list(IPs) as IPs by vulnGrouping
You must use list(X) not values(X) for each of the fields, otherwise they will not line up when aggregating on the last stats
Hope this helps
See if this format is useful for you
| eval vulnGrouping=case(plugin_name like "Adobe%", "Adobe", plugin_name like "Google%", "Google Chrome", plugin_name like "Oracle%", "Oracle Software", plugin_name like "Cisco%", "Cisco Software")
| chart dc(ipv4) by vulnGrouping priority
This worked ok but put the High and critical into their own column. The accepted solution was more correct. Thank you so much for your suggestion 🙂
Do it like this
| eval vulnGrouping=case(plugin_name like "Adobe%", "Adobe", plugin_name like "Google%", "Google Chrome", plugin_name like "Oracle%", "Oracle Software", plugin_name like "Cisco%", "Cisco Software")
| stats dc(ipv4) as IPs by vulnGrouping priority
| stats list(priority) as priority list(IPs) as IPs by vulnGrouping
You must use list(X) not values(X) for each of the fields, otherwise they will not line up when aggregating on the last stats
Hope this helps
Perfect! Thank you 😊