I'm trying to create a search to determine which hosts in a CSV file don't have any events associated with it within Splunk.
Essentially what I'm trying to do is target these specific hosts contained within this CSV file to determine the hosts that haven't had any vulnerability scans run on them in the last 30 days.
I already have a functioning inputlookup subsearch which pulls the hostname field from the csv and renames it as Hostname to match the event field in splunk.
From there I run the stats command to determine the latest time a log has come in (end_time) rename it lastTime and group by Hostname. Then run a few calculations to determine the duration between now and the last reported time, and finally run a search to display records that only have a difference greater than or equal to 30 days and display the results in a table with columns Hostname and "Last Reported".
| fields + hostname
| rename hostname as Hostname] index=endpoint sourcetype=qualys
| stats max(end_time) as lastTime by Hostname
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(lastTime)
| eval timeDiff=now()-lastTime
| eval hourDiff=timeDiff/3600
| eval dayDiff=hourDiff/24
| convert ctime(lastTime)
| sort -dayDiff
| search dayDiff>=30
| rename lastTime AS "Last Reported"
| table Hostname, "Last Reported"
Ideally, I'd like to be able to see a 1 to 1 association with all of the hosts in my csv file. If i have 50 records in a CSV file, my search should also return 50 results.
My problem however, is that as expected, the results are only returning records that have events associated with them. Meaning that I'm not reporting on hosts which have never been scanned.
Is there any way to return all of the hosts listed from my csv file and if there is no event record simply set the Last Reported date in my results table to NULL?
Thanks so much!
... View more