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

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

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

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...