Search syntax for comparing events over 30-day timespan

[sbattista09]

The purpose of the query is to identify those events that occurred after 10/14/2017 01:00:00 that had not occurred in the 30 days prior to 10/13/2017 22:00:00. Not sure how to display this, any ideas?

This is what i have to work with-

   earliest=-30d@d latest=@m sourcetype=Apps (Hosted="A" OR Hosted="b" OR Hosted="c" OR Hosted="d" OR Hosted="e" OR Hosted="f" OR Hosted="g") AND sub_origin="*ONLINE*" AND (_time < strptime("2017-10-13 22:00", "%F %H:%M") OR _time > strptime("2017-10-14 01:00", "%F %H:%M"))
    | rex "msg\=\'(?<first22char>(.{22}))"  
    | eval older_than_X = if( _time < (strptime("2017-10-13 22:00", "%F %H:%M")),1,0)
    | stats max(older_than_X) as older_than_X, count by program_error, err_transaction, first22char  
    | search older_than_X=0
    | table count, program_error, err_transaction, first22char
    | rex mode=sed field="program_error" "s/\'//g" 
    | rex mode=sed field="err_transaction" "s/\'//g"

[elliotproebstel · Accepted solution]

If I understand you correctly, you're looking to locate log events from the time window 9/13/2017 22:00:00 through 10/13/2017 22:00:00 and compare them to log events that have been generated since 10/14/2017 01:00:00. This comparison will involve looking at one or more specific fields in order to determine if the event in the more recent time window is a repeat-type event or a brand-new-type event.

(Given that your time windows are static, I used an online epoch timestamp converter to convert the timestamps - but I didn't know your time zone, so I left them in GMT. You'll want to find the correct timestamps for your timezone.)

My approach is to use a base search to search the larger time window (the earlier window) and add to these events a field called older_event, which I set to 1. This marks all the events from the earlier time window. I then append a subsearch across the more recent time window, giving us a set of events from the two windows. In order to determine if an event in the more recent window is a repeat of an older event (based on both events having the same value for a field called comparison_field), I use the eventstats command to extend the older_event marking to matching newer events - calling this field duplicate_event. I then filter down to only events that do not contain the field duplicate_event.

Here's the code:
index=yourindex earliest=1505340000 latest=1507932000 your search criteria | eval older_event=1 | append [ search index=yourindex earliest=1507942800 your search criteria] | eventstats max(older_event) AS duplicate_event BY comparison_field | where isnull(duplicate_event)

[sbattista09]

Thanks! worked well!