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]

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! 🙂