Splunk Search

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

typicallywrecke
Engager

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.

0 Karma
1 Solution

gcusello
Legend

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

View solution in original post

0 Karma

gcusello
Legend

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

0 Karma

kagamalai
Explorer

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

 

 

 

0 Karma

typicallywrecke
Engager

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.

0 Karma

anmolpatel
Builder
0 Karma
Get Updates on the Splunk Community!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...