lookup table to find systems not reporting

[hartfoml]

I have a look-up table of systems reporting to splunk from one of my users. He would like to know when one of the systems on the list is not reporting to splunk in the last 24 hours.

Any suggestions?

[martin_mueller · Accepted solution]

Assuming your table has a field called host, something roughly like this:

| inputlookup your_host_lookup | join type=left host [metadata type=hosts] | fillnull recentTime | where recentTime < relative_time(now(), "-24h")

[martin_mueller]

That's a fully paid-for invitation, right? 🙂

[hartfoml]

I had to add the data I wanted to display on the end like this | inputlookup MyHostList.csv | join type=left host [metadata type=hosts] | fillnull recentTime | where recentTime < relative_time(now(), "-24h") | eval Comment="This systems has not reported to CLS in the last 24 Hours"| table host Comment

Thanks Martin for the help. Hope to meet you at the Users conference 2014

[martin_mueller]

This will list hosts from your lookup table that either don't appear in the output of metadata or that appear but have a recentTime older than 24 hours ago.

fillnull replaces null values with zero for the recentTime field, allowing the comparison with "24 hours ago" to work properly for hosts that are in the lookup table but are not listed by metadata.

[hartfoml]

Thanks Martin, but will this find the host that is reporting or the host that is not reporting. I'm unsure about what the "fillnull" does?