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

[arrowecssupport]

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.

[somesoni2]

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

[ctaf · Accepted solution]

You could try:

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

[LewisWheeler]

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).

[arrowecssupport]

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

[LewisWheeler]

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.