Splunk Search

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

pretzel2
Path Finder

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.

0 Karma
1 Solution

gjanders
SplunkTrust
SplunkTrust
| 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

Alerts for Splunk Admins https://splunkbase.splunk.com/app/3796/
Version Control for Splunk https://splunkbase.splunk.com/app/4355/

View solution in original post

gjanders
SplunkTrust
SplunkTrust
| 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

Alerts for Splunk Admins https://splunkbase.splunk.com/app/3796/
Version Control for Splunk https://splunkbase.splunk.com/app/4355/

View solution in original post

pretzel2
Path Finder

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.

0 Karma

pretzel2
Path Finder

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.

0 Karma

pretzel2
Path Finder

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"

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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
Path Finder

I think trigger conditions is the way to go.

0 Karma

gjanders
SplunkTrust
SplunkTrust

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

Alerts for Splunk Admins https://splunkbase.splunk.com/app/3796/
Version Control for Splunk https://splunkbase.splunk.com/app/4355/
0 Karma

pretzel2
Path Finder

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?

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.