How to show the exceptions and not the matches to a search

[jamesofthedead8]

I'm trying to create a dashboard that will identify when a server stops sending data through to Splunk.

host="hostname1" OR host="hostname2" OR host="hostname3"  | dedup host | stats count by host

So it shows no results until one of those servers stops sending data and then it'll show hostname1 for example.

Or if anyone case suggests a better way of doing this overall I'd appreciate it.

[wmyersas]

There's no need to dedup host following immediately with stats count by host 🙂

[jamesofthedead8]

Legacy of a different search I'm doing. Left it in by mistake 🙂

[gcusello]

Hi, @jamesofthedead84,
you should create a lookup containing all the server to check (called e.g. perimeter.csv) where there's at least one field (host) but it could contain also other information (e.g. IP, site, description, etc...).
Then you should run a search like this:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

In this way you can create an alert that fires when an host of your lookup doesn't send logs to internal.

You can also use this search to display in a dashboard the status of your servers:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| rangemap field=total severe=0-0 low=1-1000000000 default=severe
| rename host AS HostName range AS Status
| table HostName Status

Ciao.
Giuseppe

[jamesofthedead8]

Is there any way to do it without the lookup? Trying to reduce the faff.

[gcusello]

Hi, @jamesofthedead84,
You could append as many rows as the number of hosts to check, but it's non reasonable solution that's useful if you have to check few servers!

Using a lookup you have an additional work to manage it, but in this way you're sure about the perimeter to monitor.

If it's acceptable for you, you could automatically update the lookup every night extracting the host list from logs, scheduling a search like this

| metasearch index=_internal earliest=-30d
| dedup host
| table host
| outputlookup perimetr.csv

But in this way you partially lose the control of your perimeter because if you dismiss a server, you continue to check it for 30 days and receive alerts for a false positive.
In addition, in the perimeter.csv lookup you could insert other useful information as IP address, site, operative system, etc...

You eventually could use an intermediate approach creating a lookup for the dismissed servers and exclude them from the lookup creation, something like this:

| metasearch index=_internal earliest=-30d NOT [ | inputlookup exclusions.csv | fields host ]
| dedup host
| table host
| outputlookup perimetr.csv

Anyway, the best approach is to extract the lookup from a really updated CMDM.

Ciao.
Giuseppe

[jamesofthedead8]

Hey Giuseppe,

I've followed your original suggestion. But it's showing all hosts across our entire environment. I've tried putting in a WHERE to filter out the stuff that is reporting as I'm not bothered about stuff that is working. Just the nonreporting ones. Any ideas?

Many thanks
James

[gcusello]

Hi, @jamesofthedead84,
yes, you can filter the results using the perimeter.csv lookup:

 | metasearch index=_internal  [ | inputlookup perimeter.csv | fields host ]
 | eval host=lower(host)
 | stats count BY host
 | append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
 | stats sum(count) AS total BY host
 | where total=0

But it's not relevant for the alert because this search displays only the hosts in lookup that didn't send logs.
It's instead relevant if you want to display the status of all hostes (my second search), because there could be other servers of your infrastructure that you wanto to eclude from the results:

 | metasearch index=_internal [ | inputlookup perimeter.csv | fields host ]
 | eval host=lower(host)
 | stats count BY host
 | append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
 | stats sum(count) AS total BY host
 | rangemap field=total severe=0-0 low=1-1000000000 default=severe
 | rename host AS HostName range AS Status
 | table HostName Status

Ciao.
Giuseppe

[jamesofthedead8]

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append
[| inputlookup AvailHosts.csv
| eval host=lower(host), count=0
| fields host count ]
| stats sum(count) AS total BY host
| rangemap field=total NonReporting=0-0 Reporting=1-10000000 default=Reporting
| rename host AS HostName range AS Status
| stats sum(count) AS total BY host
| where total=0

I've renamed the rangemap fields and I've created the lookup

It's saying it can't access the lookup table but I've put the table into Splunk and created the definitions. I get the error below?

Error in 'lookup' command: Lookups: The lookup table 'AvailHosts.csv' does not exist or is not available.

Thanks

James

[gcusello]

Hi, @jamesofthedead84,
at first check the name of the lookup because the inputlookup command is case sensitive and check that there's the extension(.csv) in the lookup filename and lookup definition.
then, if you already created the Lookup Definition, check the permissions of the lookup.

Ciao.
Giuseppe