Check Critical Assets that are Logging

[Zer0F8th]

Hi All,

So I have a lookup table with the following fields: FQDN, Hostname, and IP. I need to check to see which of these assets in the lookup table are logging (about 700 assets) and which aren't in the last 7 days. I used the following basic SPL to get a list of hosts which are logging:

| tstats earliest(_time) latest(_time) count where index=* earliest=-7d by host
 

The issue I'm having is that the host output in the above SPL comes through in different formats, it may be a FQDN or a Hostname, or an IP address. How do I use my lookup table to check if the assets in the lookup table are logging without having to do 3 joins on FQDN, Hostname and IP? Here was a SPL query that somewhat worked but it is too inefficient:

| inputlookup lookup.csv
| eval FQDN=lower(FQDN)
| eval Hostname=lower(Hostname)
| join type=left FQDN [
|tstats latest(_time) as lastTime where index=* earliest=-7d by host 
| rename host as FQDN
| eval FQDN=lower(FQDN)
| eval Days_Since_Last_Log = round((now() - lastTime) / 86400) 
| convert ctime(lastTime)
]
| join type=left Hostname [
|tstats latest(_time) as lastTime where index=* earliest=-7d by host 
| rename host as Hostname
| eval Hostname=lower(Hostname)
| eval Days_Since_Last_Log = round((now() - lastTime) / 86400) 
| convert ctime(lastTime)
]
| join type=left IP[
|tstats latest(_time) as lastTime where index=* earliest=-7d by host 
| rename host as IP
| eval IP=lower(IP)
| eval Days_Since_Last_Log = round((now() - lastTime) / 86400) 
| convert ctime(lastTime)
]
| rename lastTime as LastTime
| fillnull value="NULL"
| table FQDN, Hostname, IP, Serial, LastTime, Days_Since_Last_Log

I'm somewhat new to Splunk so thank you for the help!

[gcusello]

Hi @Zer0F8th,

you have to start from the main search, please try this:

| tstats 
     count 
     WHERE index=* earliest=-7d 
     BY host
| append [
     | inputlookup lookup.csv | eval count=0 | fields FQDN count ]
| append [
     | inputlookup lookup.csv | eval count=0 | fields IP count ]
| append [
     | inputlookup lookup.csv | eval count=0 | fields Hostname count ]
| eval host=coalesce(host, FQDN, IP, Hostname)
| stats sum(count) AS total BY host
| where total=0

Ciao.

Giuseppe