Splunk Search

Match A lookup of Hosts & IP's to metadata search, to find hosts logging and not logging (where the hostname might be an IP address)

mmwilson
Explorer

Hi Splunk Community!

I have a search i'm trying to build out, that will ultimately power some Dashboards to find hosts that are logging, or not logging, from a asset group lookup table.

Our asset group lookup has dozens of fields, but for this example, we only need to really focus on two:

|inputlookup sample.csv
         Hostname           IP Address
    1.) host1.foo.com   1.1.1.1
    2.) host2.foo.com   2.2.2.2
    3.) host3           3.3.3.3

I would like to run the input table against the host logging to splunk (i have been using |metadata type=hosts, but am open to suggestions)

   |metadata type=hosts |fields host
         host
    1.) host1.foo.com
    2.) 2.2.2.2
    3.) host3.foo.com

There are a couple of scenarios that i'm running into for the host field in splunk, based on our logging environment. From the lookup table sample above, we could see in splunk:

1.) host=Hostname -- a 1:1 match from the lookup Hostname field, to the host field in Splunk, no problem. In my search, this is working fine.
2.) host="IP Address" -- We are seeing this host in splunk by its IP address, rather than its Hostname.
3.) host="Hostname*" -- This systems hostname is configured with a non FQDN, but by the time it reaches Splunk one has been appended.

This is the search that i've been using:

| inputlookup sample.csv 
| eval IP='IP Address' 
| eval Hostname=lower(Hostname) 
| join type=left 
    [| metadata type=hosts 
    | rename host AS IP 
    | table IP firstTime lastTime recentTime ] 
    [| metadata type=hosts 
    | eval host=lower(host) 
    | rename host AS Hostname 
    | table Hostname firstTime lastTime recentTime] 
| eval "First Logged"=strftime(firstTime,"%Y-%m-%d %H:%M:%S") 
| eval "Recently Logged"=strftime(recentTime,"%Y-%m-%d %H:%M:%S")  
| eval last_logged = round((now() - lastTime)/86400) 
| replace -1 with 0 in last_logged 
| eval last_logged = if(last_logged<30, "Logging", last_logged) 
| eval last_logged = if(last_logged>=30, "Stopped Logging", last_logged) 
| eval last_logged = if(isnull(last_logged) OR last_logged="", "Never Logged", last_logged) 
| table Hostname "IP Address" "First Logged" "Recently Logged" last_logged

I believe that the area that im running into issues is the join. I dont think that splunk is handling join type=left [subsearch][subsearch] the way that I was hoping that it would.

If i had a search that worked perfectly, it would load the asset list, and compare logging hosts in splunk to the Hostname or "IP Address" fields in the lookup. and would account for partial matches (host=host3*). If i have to use a differnet search rather than the type that im using above, i understand, however an ideal output would be:

      Hostname  IP Address  First Logged    Recently Logged last_logged
    host1.foo.com   1.1.1.1 5/4/18 21:42    5/6/18 16:08    Logging
    host2.foo.com   2.2.2.2                                 Never Logged
    host3           3.3.3.3 3/12/16 6:23    2/5/18 09:32    Stopped Logging

Thanks for reading, and hopefully, the help! 🙂

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