topic Re: How do I combine " |stats count by host " and "| stats distinct_count(host)" in one table? in Splunk Search

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

pretzel2 — Mon, 01 Oct 2018 21:56:01 GMT

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.

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

gjanders — Mon, 01 Oct 2018 22:03:03 GMT

| 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

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

pretzel2 — Tue, 02 Oct 2018 13:15:26 GMT

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.

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

pretzel2 — Tue, 02 Oct 2018 14:13:13 GMT

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.

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

pretzel2 — Tue, 02 Oct 2018 15:30:03 GMT

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"

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

mstjohn_splunk — Tue, 02 Oct 2018 20:14:28 GMT

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.

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

pretzel2 — Tue, 02 Oct 2018 20:35:24 GMT

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?

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

pretzel2 — Tue, 02 Oct 2018 20:39:00 GMT

I think trigger conditions is the way to go.

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

gjanders — Tue, 02 Oct 2018 21:46:57 GMT

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...