How exclude lists of hosts from search using lookup table?

[SplunkLunk]

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.

[woodcock]

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

[SplunkLunk]

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.

[woodcock]

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

[gcusello · Accepted solution]

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

[gcusello]

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

[SplunkLunk]

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?

[SplunkLunk]

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.