Use Lookup Table To Identify Hosts That Have/Have Not Logged

[typicallywrecke]

So I'm trying to do something that may or may not be possible.

I want to first create a lookup table that maps IP addresses to host names. I then want to use metadata or tstats to pull a list of systems that haven't logged within a certain timeframe, and then convert those IP addresses to the corresponding hostnames in the lookup table. This will provide useful for personnel who need to look at a hostname and immediately know what host it is, without needing to know the IP address of each host on the network.

I believe I have the right metadata and tstats commands, but I am not sure how to then run those results against the lookup table for the IP address to hostname field conversion. This is ultimately going to be dumped into a table as a dashboard widget, and I'm not even sure if I can do all those things.

[gcusello · Accepted solution]

Hi @typicallywrecked,
if you already have a lookup with all the hosts to monitor (called e.g. perimeter.csv with the following fields. host, IP) you have to run a simpe search like this:

| metadata index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv |eval host=lower(host), count=0 | fields host IP count ]
| stats sum(count) AS total value(IP) AS IP BY host
| eval IP=if(isnull(IP),"Not present in lookup",IP), status=if(total=0,"Missing","Present")
| table host IP status

This search runs for servers that have the Splunk Universal Forwarder because it uses Splunk internal logs.
You can also use it to check appliances without the Universal Forwarder using the same approach but using a different index in the main search.

You can use this search for an alert replacing the last two rows with | where total=0.
or display it in graphical mode.

Ciao.
Giuseppe

[kagamalai]

Hi ,

I used below query but not getting any output - I have lookup table dc.csv with host, IP field.

Could you please look and advise 

| metadata index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup dc.csv |eval host=lower(host), count=0 | fields host IP count ]
| stats sum(count) AS total value(IP) AS IP BY host
| eval IP=if(isnull(IP),"Not present in lookup",IP), status=if(total=0,"Missing","Present")
| table host IP status

[typicallywrecke]

Thank you so much for the information. I get all the data from my lookup table, but everything shows as missing probably due to the way the index and my lookup table are working together. I think I'm going to have to do a lot more to get this to work.

For now, I'm just creating 2 panels. A panel using metadata that shows the systems that have not reported in over 24 hours hours (but have sent logs within the past 30 days) - this is just giving me IP addresses but isn't a lot of systems. Then I'm using another panel next to it that basically goes through the normal events and shows the number of unique hosts seen in events coming across the network.

Should work for now...because my brain about to explode trying to figure the metadata/lookup table.

[anmolpatel]

Have a look at this answer
https://answers.splunk.com/answers/642929/how-to-lookup-match-on-ip-return-user-value-within.html