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