Splunk Search

How exclude lists of hosts from search using lookup table?

Path Finder

Good morning,

I've looked at some search topics here and haven't been successful in finding a working solution. I have a query that looks for hosts that haven't communicated in more than 24 hours:

| metadata type=hosts index=|where recentTime < now() - 86400| eval lastSeen = strftime(recentTime, "%F %T") | fields + host lastSeen

I want to exclude hosts that are under maintenance or been decommissioned. The decommissioned hosts will fall off after X number of days according to the Splunk Admin group. However, until then I want to prevent them from showing up in my alerts.

I created a lookup table with only one column (i.e., host) and put the host names in that column. Then, based on another post I used the following search to try and exclude them but they are still showing in the results:

| metadata type=hosts index= NOT [|inputlookup DecomMaint.csv]|where recentTime < now() - 86400| eval lastSeen = strftime(recentTime, "%F %T") | fields + host lastSeen

I'm sure it's something easy but this is my first lookup table. Can anyone see what I am doing wrong? Thanks in advance for any help.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Hi,
In Your search cut "index=".
You need

| metadata type=hosts NOT [|inputlookup DecomMaint.csv | fields host]|where recentTime < now() - 86400| eval lastSeen = strftime(recentTime, "%F %T") | fields + host lastSeen

If thename of thehost column in your lookup isn't host, remember to change it (| rename your_host_field AS host) before the field command.
Beware to the case of hosts, maybe you should changeall in uppercase or lowercase.

Bye.
Giuseppe

View solution in original post

0 Karma

Esteemed Legend

You have to move it to the where command like this:

| metadata type=hosts index=* | where NOT [|inputlookup DecomMaint.csv] recentTime < now() - 86400 | eval lastSeen = strftime(recentTime, "%F %T") | fields + host lastSeen

Path Finder

Thanks for the response. When trying it your way I still got an error. Don't know why. See my above comment to one of the other posters on how I got it working. At any rate it appears to be working now.

0 Karma

Esteemed Legend

This capability already exists in the MC. On your search head go to:
Settings -> Monitoring Console -> Settings -> General setup
Then
Settings -> Monitoring Console -> Forwarders
Do what the MC does (or just use the MC).

SplunkTrust
SplunkTrust

Hi,
In Your search cut "index=".
You need

| metadata type=hosts NOT [|inputlookup DecomMaint.csv | fields host]|where recentTime < now() - 86400| eval lastSeen = strftime(recentTime, "%F %T") | fields + host lastSeen

If thename of thehost column in your lookup isn't host, remember to change it (| rename your_host_field AS host) before the field command.
Beware to the case of hosts, maybe you should changeall in uppercase or lowercase.

Bye.
Giuseppe

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

You don't need to divide the first two items of your search, in this way search is quicker.
| metadata type=host NOT [| inputlookup DecomMaint.csv | fields host ] | ...
Bye. Giuseppe

0 Karma

Path Finder

That did not work for me. Once I did that the search returned no results where as if I did it the way I mentioned the search returned three host names I would have expected it to. The search that did not work was:

|metadata type=hosts NOT [|inputlookup DecomMaint.csv]
|where recentTime < now() - 86400
|eval lastSeen = strftime(recentTime, "%F %T")
|fields + host lastSeen

Am I doing something wrong?

0 Karma

Path Finder

Thanks. That sort of worked. I removed the "index=" but I had to add a search command. So now it looks like:

|metadata type=hosts
|search NOT [|inputlookup DecomMaint.csv]
|where recentTime < now() - 86400
|eval lastSeen = strftime(recentTime, "%F %T")
|fields + host lastSeen

Without the search command I was getting no results.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!