Display both unique and total columns in a chart table

[pj]

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!

[gkanapathy · Accepted solution]

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