Dashboards & Visualizations
Highlighted

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

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.

Highlighted

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

Legend

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

Highlighted

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

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

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

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

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

| 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

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

Builder

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

0 Karma
Highlighted

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

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

0 Karma