How to compare two fields with current date to raise an alert?

[AL3Z]

Hi all,

I'm trying to  make a query  which is not working as expected could you pls help me out in raising an alert.

I have a field name first_find value  "2021-06-07T09:04:09.130Z" and last_find values "2023-02-15T16:15:52.506Z"are in this format, I believe it is in UTC format, I need a search to make if first_find OR last_find matches with current date the alert should triggered. My SH is set to IST time zone would it make any impact on search ? Do i need to convert the field values  time zone from UTC to IST to get a alert out of it ?

[richgalloway]

The only way to compare dates is to first convert them in epoch (integer) form using the strptime function.  The current time, returned by now(), is already in epoch form.  Splunk accounts for time zone differences when converting to epoch.

| eval first=strptime(first_time, "%Y-%m-%dT%H:%M:%S.%3N%Z"),
       last=strptime(last_time, "%Y-%m-%dT%H:%M:%S.%3N%Z")
``` Compare each timestamp to see if they are today's date.
    'relative_time(now(), "@d")' returns the timestamp for midnight (0:00) today. ```
| eval first_is_today = if(first > relative_time(now(), "@d"), 1, 0)
| eval last_is_today = if(last > relative_time(now(), "@d"), 1, 0)

[AL3Z]

Hi,

@richgalloway 

Do we need to  change the time format of the fields to current time zone format, while  comparing  it with current date  ? UTC to IST 
Eg.  first_find value  ""%Y-%m-%dT%H:%M:%S.%3NZ""   

[richgalloway]

If you use "%Z" instead of "Z" on the end of the format string then Splunk will know the time zone and handle it correctly.  The letter "Z" is another abbreviation for "UTC" and "%Z" is the format string variable for 'time zone name/abbreviation'.

[AL3Z]

 my scenario I'm looking at if any of first OR last matches with todays date,

to display those id's only. If the above query which you have mentioned works you definitely deserves Karma.

[richgalloway]

As I've said before, timestamps must be in epoch (integer) form for Splunk to compare them.  Use the strptime function to convert first_detected and last_detected to integers.  Then compare them to todays_date.

... 
| eval fd = strptime(first_detected, "%Y-%m-%dT%H:%M:%S.%3N%Z"),
       ld = strptime(last_detected, "%Y-%m-%dT%H:%M:%S.%3N%Z")
| where (fd >= todays_date OR ld >= todays_date)

For display purposes, you can use the fieldformat command to show todays_date in human-readable form.

| fieldformat todays_date = strptime(todays_date, "%Y-%m-%dT%H:%M:%S.%3N%Z")

[AL3Z]

Hi, @richgalloway 

why had you used > symbol in the search 

Stats values ( first_detected),values( last_detected) or

Stats earliest ( first_detected),latest( last_detected

Which one is preferable as per the above snapshot..

Thanks 👍

[richgalloway]

The greater-than symbol is used to determine if the timestamp is newer than a given value.  In this case, more recent than 00:00 this morning (it's from today).

The values and earliest functions are not necessarily interchangeable.  The former returns all unique values of a field whereas earliest returns the value with the oldest _time value.  The latter usually is easier to work with since it does not return a multi-valued field, but that depends on the objective.