topic Re: How to achieve search to match the fields with current date? in Splunk Search

How to achieve search to match the fields with current date?

AL3Z — Tue, 31 Jan 2023 18:00:12 GMT

Hi,

Need a search query to find the either if first_find and last_find values matches with the current date should raise an alert .

first_find last_find fields are in

2020-04-30T13:18:13.000Z

2023-01-15T14:12:18.000Z

format need this in 2020-04-30 format

2. Instead of receiving all the alerts we require, if today's date matches the first _find or the last_find, raise an alert
*todays date will change every day do not bound that with actual todays date*

note : last_find , first_find are multi valued fields..

Thanks...

Re: How to achieve search to match the fields with current date?

yuanliu — Wed, 01 Feb 2023 05:36:01 GMT

If first_find and last_find are strings and the format is as illustrated, you can simply match with now().

| where strftime(now(), "%F") == substr(first_find, 1, 10) OR strftime(now(), "%F") == substr(last_find, 1, 10)

Re: How to achieve search to match the fields with current date?

AL3Z — Mon, 20 Feb 2023 14:33:05 GMT

@yuanliu,

using this sub string function do we get desired output if the first find and last find fields in events are in ISO 8601 format ? how we can normalize it ??

Re: How to achieve search to match the fields with current date?

yuanliu — Tue, 21 Feb 2023 07:24:15 GMT

Doesn't ISO 8601 use the exact 10-character "yyyy-mm-dd" representation for dates? That's exactly what my code snippet is designed to do. substr is just a shortcut to compare dates. Or do yo mean if the fields are not in ISO 8601 format?

Re: How to achieve search to match the fields with current date?

AL3Z — Tue, 21 Feb 2023 08:28:51 GMT

hi, @yuanliu
My field values "last_find": "2023-02-15T16:15:52.506Z"
"first_find": "2021-06-07T09:04:09.130Z" are in utc time and my search head is ist time zone.
do we need to convert the utc to ist time zone to get desired alerts ?

Re: How to achieve search to match the fields with current date?

yuanliu — Wed, 22 Feb 2023 07:42:30 GMT

Yes, you need to explain any such data characteristics in order for others to be helpful. If I'm looking up correctly, IST is 5.5 hours ahead of UTC.

| where strftime(now() - 19800, "%F") == substr(first_find, 1, 10) OR strftime(now() - 19800, "%F") == substr(last_find, 1, 10)

This will match date in UTC. You also forget to say which date you want to match. Although for date match, the difference can probably be neglected. But if you really want, you can force the match to go the other way.

| eval first_find = strptime(first_find, "%FT%H:%M:%S.%3N%Z") | eval last_find = strptime(last_find, "%FT%H:%M:%S.%3N%Z") | where strftime(now(), "%F") == strftime(19800 + first_find, "%F") OR strftime(now(), "%F") == strftime(19800 + last_find, "%F")

Re: How to achieve search to match the fields with current date?

bowesmana — Wed, 22 Feb 2023 08:14:00 GMT

It depends on what you want with those dates. If you have a UTC date, you can parse it to an epoch time with

| eval utc_epoch=strptime(utc_date, "%FT%T.%Q%Z")

Then you can make whatever comparisons against 'today' you need to make. Is "today" UTC or IST?

If a last_find is 2023-02-15T21:15:52.506Z, is that 15 Feb "today" in UTC or ""yesterday" for IST?

when you reformat that utc epoch using strftime, it will be done in YOUR timezone

| eval date=strftime(utc_epoch, "%F")

Re: How to achieve search to match the fields with current date?

AL3Z — Sun, 26 Feb 2023 15:20:36 GMT

My understanding is that the objective is to identify the first_detect or last_detect matches with the current date based on the provided snapshot. I now comprehend that modifying the UTC format is unnecessary as Splunk will handle it automatically. Prior to comparing the time with fields, we must convert it to epoch format right.

thanks

Re: How to achieve search to match the fields with current date?

yuanliu — Thu, 23 Feb 2023 17:25:10 GMT

That is correct. For most time/date calculations, it is advantageous to use epoch or another numeric representation. Your ask is about matching only dates, therefore the last step to extract the "date" portion from the time. If the data source and search head share the same time zone, there's some shortcut you can take as I illustrated in the first answer. (It is rather surprising that your search head would run something other than UTC. Unless you are running it on a personal device, it is always advantageous to use the same time zone across your deployment, and UTC is often the easiest choice.)

Re: How to achieve search to match the fields with current date?

AL3Z — Sat, 25 Feb 2023 12:30:22 GMT

@yuanliu ,

Can we use this alternative search

| eval todays_date=now()

| eval todays_date=strftime(todays_date,"%Y-%m-%d"), first_detected=strftime(strptime(first_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d"), last_detected=strftime(strptime(last_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d")
| where todays_date=first_detected OR todays_date=last_detected

Thanks

Re: How to achieve search to match the fields with current date?

yuanliu — Sat, 25 Feb 2023 19:05:44 GMT

Using a named variable todays_date makes the code more readable and improves maintainability. That is good. But this search would give the same result as my first answer because it doesn't take into account the time zone difference. Here is a breakdown.

Semantically, "%Y-%m-%d" is identical to the shortcut "%F".
Mathematically, strftime(strptime(first_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d") always gives the date in the original timezone, therefore the exact same output as substr(first_detected, 1, 10). The disadvantage, of course, is more compute and memory cost.

You need to decide whether you want todays_date to be in the data source's time zone (UTC) or the search heads' time zone. If you want to align with search heads, you need to also ask: are all search head in IST, or will it change from head to head?

Assuming you want todays_date to be in IST, you can do

| eval todays_date=relative_time(now(), +5.5h) | eval todays_date=strftime(todays_date,"%Y-%m-%d"), first_detected=strftime(strptime(first_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d"), last_detected=strftime(strptime(last_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d") | where todays_date=first_detected OR todays_date=last_detected

Re: How to achieve search to match the fields with current date?

AL3Z — Sun, 26 Feb 2023 00:42:31 GMT

@yuanliu ,

All my SH's are in utc,only my user sh in ist.

Re: How to achieve search to match the fields with current date?

yuanliu — Sun, 26 Feb 2023 05:12:33 GMT

In other words, you cannot predetermine the user's locale. If so, you need to first calculate offset at the user's search head.

| eval offset = strptime("2000-01-01Z", "%F%Z") - strptime("2000-01-01", "%F") | eval todays_date=strftime(now() + offset,"%Y-%m-%d"), first_detected=strftime(strptime(first_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d"), last_detected=strftime(strptime(last_detected,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y-%m-%d") | where todays_date=first_detected OR todays_date=last_detected