topic Re: How to find a list of serial numbers not reported in 7 days in Splunk Search

How to find a list of serial numbers not reported in 7 days

arrowecssupport — Wed, 10 Feb 2016 15:46:31 GMT

I need to find list of serial numbers that have been extracted as a field value where they have not been seen in over 7 days.
Sometimes our systems stop reporting to the network share and we want to report on this.

This is like an opposite of the normal search of "show me 7 days worth of logs" it's complicated because you have to know what has already been seen to know what's now missing.

Re: How to find a list of serial numbers not reported in 7 days

LewisWheeler — Wed, 10 Feb 2016 16:23:43 GMT

Add your serial numbers as a static input file, then create a search to create an outer join from the input file and your search string to return the serial numbers NOT seen in the last 7 days. If your serial number list is dynamic this will not work (you haven't mentioned it changes so assume is static).

Re: How to find a list of serial numbers not reported in 7 days

ctaf — Wed, 10 Feb 2016 16:29:23 GMT

You could try:

... | stats latest(_time) AS latest_time by serial_number | eval seven_days_ago=now()-604800 | where latest_time > seven_days_ago

Re: How to find a list of serial numbers not reported in 7 days

somesoni2 — Wed, 10 Feb 2016 16:30:20 GMT

The best approach here will be to have a lookup table file which will get updated daily to list all serial numbers reported yesterday with yesterday's date. Once the lookup is getting updated daily, you can just query the latest date on the lookup to see which serial numbers are not reporting for 7 days.

Schedule search to run daily:

Search:     
    your base search | stats max(_time) as report_date by serial_number | append [| inputlookup serial_numbers.csv ] | stats max(report_date) as report_date by serial_number | outputlookup serial_numbers.csv


Start time: -1d@d    Finish time:-  @d

Cron:  10 02 * * *

You can backfill above search for say 15 days or 30 days.

Your alert to find serial numbers not reported for 7 days.

| inputlookup serial_numbers.csv | eval age=now()-report_date | where age>7*86400

Re: How to find a list of serial numbers not reported in 7 days

arrowecssupport — Wed, 10 Feb 2016 16:34:13 GMT

.....thanks for the help. It is dynamic sorry 😄

Re: How to find a list of serial numbers not reported in 7 days

LewisWheeler — Wed, 10 Feb 2016 16:39:19 GMT

I'd go with @ctaf answer then - if you run that over the past 8 days and as an alert every day it'll trigger whenever its greater than 7 days although important to note it'll only trigger once per serial number, the next day it won't be able to alert on the serial number which hasn't appeared for 9 days. This is the main limitation of a time orientated search with dynamic checks like this in my opinion. I've used something similar to track forwarders going offline successfully, but only reports once then stops.