How do I combine " |stats count by host " and "| stats distinct_count(host)" in one table?

[pretzel2]

I can search for events and run stats count by host.

And I can run a search of distinct number of hosts.

I want to combine both in one table. I want count of events by host and a count of hosts.

I actually want to create an alert based on the number of hosts returned.

[gjanders · Accepted solution]

| stats count, dc(host) by host

Should work just fine, you can create alerts that work on the number of rows returned, or you can use a Custom Triggering Condition to trigger only when criteria are met or you could integrate it into your search, for example:

| stats count, dc(host) AS distinctCount by host
| where distinctCount > 10

And then alert when more than 0 results appear

[pretzel2]

Thank you for the reply! The first suggestion works, sort of. I get a table with a columns, host, count, and distinctCount. distinctCount for each host is "1". When I add the where condition it doesn't work.
I have to move where distinctCount > 0 to get any table.

[pretzel2]

I think what I need is this https://answers.splunk.com/answers/614705/how-to-trigger-second-search-based-on-first-search.html

So first search and alert on the distinct number of hosts (with my condition) then trigger a second search that give more details. I'll give it a try and report back.

[pretzel2]

Using the "map" command worked, in this case triggering second search if threshold of 2 or more is reached.

index= source= host="something*"
| stats distinct_count(host) as distcounthost

| eval tokenForSecondSearch=case(distcounthost>=2,"true")

| map search="search index= source= host="something*"
| stats count by host,source | sort - count"

[mstjohn_splunk]

hi @pretzel2

It looks like you and @gjanders were able to figure out your query. Would you mind approving this answer if it helped ya? Thanks.

[pretzel2]

I think trigger conditions is the way to go.

[gjanders]

I think if you use the trigger condition or a where clause to filter out the data you want, you might also want to look at eventstats.

index= source= host="something*"
| eventstats distinct_count(host) as distcounthost
| where distcounthost > 1

Would for example add the "distcounthost" to each event but not lose the raw data, so you won't need a map or a report + alert...

[pretzel2]

thought I had map working .. as a second search but it's not working .. I get 10000 stats and think I'm running Splunk into the ground. My basic point is this, I want to create an alert based on a count of something (that works) but then I want to send my ops team more details about the alert, hostname, ldap server, etc, fields that I already have defined. Maybe put that in a report and tie that to the alert using map?