topic Re: Use inputlookup to find servers not reporting in Splunk Search

Use inputlookup to find servers not reporting

hippe21 — Tue, 04 Apr 2017 20:59:52 GMT

Here's the scenario: server102 has not reported data in the last 15 minutes. I want to use my inputlookup in conjunction with a subsearch, to show server102 in my search results, as it did not report data (and also setup an alert based on this).

My inputlookup contains the following:

environment,host
dev,server001
dev,server002
prod,server101
prod,server102

Using the following search, I'm getting all of the hosts returned. I only want my prod server102 to return in my results, since the log entry has not shown up on that host.

|  inputlookup myserverlist.csv | search environment=prod NOT [search index=my_index "search_request" ] | dedup host | fields host

Do I need to have some matching field (other than host) between the csv file, and the server logs? Server logs contain [index | host] but NOT environment. Could this be why?

** updated - I'm using the inputlookup to keep track of which hosts belong to which environment, and want that list (in my case all prod servers > environment=prod), to search for all prod hosts that do NOT have "search_request" in their logs.

Re: Use inputlookup to find servers not reporting

beatus — Tue, 29 Sep 2020 13:32:20 GMT

hippe21,

You can use a subsearch to accomplish this:

|inputlookup test1.csv | search NOT [search index=_internal |dedup host | table host]

This search will take your CSV and elemenate hosts found in the subsearch. The results in your case woulkd be a table with:

environment,host
prod,server102

Obliviously, modify the subsearch and CSV names to suit your environment.

If you'd like to look at your data as the only indicator, i'd recommend | tstats:

| tstats count, latest(_time) AS last_seen where index=* by sourcetype,host | eval timeDiff=now()-last_seen | search timeDiff>900

Change "900" to how long you'd like to consider something missing in seconds. | tstats is going to be significantly faster than | metadata.

Re: Use inputlookup to find servers not reporting

niketn — Tue, 04 Apr 2017 21:45:04 GMT

@hippe21... You dont need a lookup file if you want to monitor hosts for events...

 | metadata type=hosts index=<YourIndexName>
 | eval lastDataDuration=(now()-lastTime)/60
 | where lastDataDuration>15

This should report all hosts that have not pushed an event in last 15 min. Refer to Splunk documentation https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Metadata and my recent answer on similar question https://answers.splunk.com/answers/515455/alert-for-lack-of-conent-for-one-host.html#answer-515460

Re: Use inputlookup to find servers not reporting

woodcock — Tue, 04 Apr 2017 22:23:57 GMT

I agree with @nikenilay but if you really want to cover the bases, you will do both like this:

| metadata type=hosts index=<YourIndexName>
| appendpipe [|inputlookup myserverlist.csv | eval lastTime = 0]
| dedup host
| eval lastDataDuration=(now()-lastTime)/60
| where lastDataDuration>15

Re: Use inputlookup to find servers not reporting

hippe21 — Tue, 04 Apr 2017 23:04:24 GMT

^ updated ... I left out the key fact that I'm looking for a specific term, not being logged on the other servers, and using the inputlookup as my master list. So I need to find all hosts in a specific environment, that do not contain "search_request" in their logs. My host logs do not have "environment", only index | host as configured fields.

Re: Use inputlookup to find servers not reporting

beatus — Tue, 29 Sep 2020 13:32:31 GMT

You'd probably be best off with something like this then:

|inputlookup test1.csv | search NOT [search index=my_index search_request|dedup host | table host]

That will show any hosts in your lookup table that do not contain the term in the subsearch.

Re: Use inputlookup to find servers not reporting

hippe21 — Tue, 04 Apr 2017 23:12:14 GMT

That's bizarre, I've tried this, and for whatever reason all my hosts are returning. I want to specifically choose the environment (based on 'myserverlist.csv' environment column), and then from there, find all hosts within that environment that do not have "search_request". This query returns ALL hosts in my csv:

|  inputlookup myserverlist.csv | search environment=prod NOT [search index=my_index "search_request" ] | dedup host | fields host

Re: Use inputlookup to find servers not reporting

hippe21 — Tue, 04 Apr 2017 23:26:39 GMT

Ahh, that's why, I was using | dedup host | fields host and not |dedup host | table host #derp 😄 Thanks!

Re: Use inputlookup to find servers not reporting

beatus — Tue, 04 Apr 2017 23:27:25 GMT

Glad you got it!

Re: Use inputlookup to find servers not reporting

starcher — Wed, 05 Apr 2017 05:05:54 GMT

What you want is a sentinel lookup. Not using subsearch on the logs. It might work at low volume. But it will break at scale.

Make a lookup with two columns. Host and count. Put a zero in the count column for all hosts you want to alert for.

Then do like this

That will give you all hosts your table says should be logging if no logs are in the search for the time window you ran it over.

Re: Use inputlookup to find servers not reporting

woodcock — Wed, 05 Apr 2017 14:04:14 GMT

Right, this is pretty much the same thing as my answer but definitely use appendpipe over append because append has subsearch limits (10.5K) but appendpipe does not.