Splunk Search

Use inputlookup to find servers not reporting

Explorer

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.

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

SplunkTrust
SplunkTrust

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

| tstats count where index=* by host | append [ | inputlookup mytable] | stats sum(count) as count by host | where count=0

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.

Esteemed Legend

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.

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Explorer

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
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

Glad you got it!

0 Karma

Esteemed Legend

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

SplunkTrust
SplunkTrust

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

SplunkTrust
SplunkTrust

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.

View solution in original post