How to get spl to look for the latest timestamp for all the sourcetypes?

[Deepz2612]

Hi,

i have a set of sourcetypes in a lookup.

Now my spl has to look for the latest timestamp for all the sourcetypes in the last one hour and report for the sourcetype for which the events are not available in the last one hour

[ITWhisperer]

| metadata type=sourcetypes
| where lastTime < relative_time(now(),"-1h")

[gcusello]

Hi @Deepz2612,

if you haven't to check if there are events with sourcetypes not in the lookup, you could run something like this:

index=* [ | inputlookup your_lookup | fields sourcetype ]
| stats count BY sourcetype
| append [ | inputlookup your_lookup | eval count=0 | fields sourcetype count ] 
| stats sum(count) AS total BY sourcetype
| where total=0

You could also use tstats to have a more performant search:

| tstats count WHERE index=* BY sourcetype
| search [ | inputlookup your_lookup | fields sourcetype ]
| append [ | inputlookup your_lookup | eval count=0 | fields sourcetype count ] 
| stats sum(count) AS total BY sourcetype
| where total=0

Ciao.

Giuseppe