Hi, am looking to pull together a table chart of our threat data that contains 3 columns: threat, totalhosts and uniquehosts. Each row of the table displays a count of totalhosts and uniquehosts for each threat.
I have currently created the following basic query, but am now struggling:
sourcetype=threats | dedup Hosts | chart count(Hosts) by Threat | rename count(Hosts) as UniqueHosts
This gives me the UniqueHosts column, but how do i get the totalhosts column as well? If i remove the dedup, i bascially get the totalhosts, but then obviously dont get the uniquehosts, so i am looking for a way to include them both.
Many Thanks!
sourcetype=threats | chart count(Hosts) as totalHosts, distinct_count(Hosts) as uniqueHosts by Threat
though if you have more than 100,000 distinct hosts, you'll need:
sourcetype=threats | stats count(Hosts) as countHosts by Threat | chart sum(countHosts) as totalHosts, count as uniqueHosts by Threat
sourcetype=threats | chart count(Hosts) as totalHosts, distinct_count(Hosts) as uniqueHosts by Threat
though if you have more than 100,000 distinct hosts, you'll need:
sourcetype=threats | stats count(Hosts) as countHosts by Threat | chart sum(countHosts) as totalHosts, count as uniqueHosts by Threat