topic Re: comparing datetime with current datettime in Splunk Search

comparing datetime with current datettime

AnanthaS — Thu, 13 Jun 2024 14:04:41 GMT

following query yields no results:

index=shared_data source="lambda:maintenance_window_handler" sourcetype="httpevent" | where eventStartsFrom <= now() and eventEndsAt >= now()

but

index=shared_data source="lambda:maintenance_window_handler" sourcetype="httpevent" | where eventStartsFrom <= now()

and

index=shared_data source="lambda:maintenance_window_handler" sourcetype="httpevent" | where eventEndsAt >= now()

both works individually. All comparisons are made against epoch date format.

Can someone help me understand as what mistake I am doing here.

Re: comparing datetime with current datettime

gcusello — Thu, 13 Jun 2024 13:24:43 GMT

Hi @AnanthaS ,

probably the issue is that the boolean AND operato must be in uppercase.

then, don't use where after the main search, your search is slower!

put all the search terms in the main search

index=shared_data source="lambda:maintenance_window_handler" sourcetype="httpevent" (eventStartsFrom <= now() AND eventEndsAt >= now())

If your search continue to not working, probably you haven't any event where you can find both the fields entStartsFrom and eventEndsAt and you have to group them using the stats command.

Ciao.

Giuseppe

Re: comparing datetime with current datettime

ITWhisperer — Thu, 13 Jun 2024 18:12:17 GMT

Are eventStartsFrom and eventEndsAt both set in the events you want to retrieve or are they in separate but correlated events?

Re: comparing datetime with current datettime

AnanthaS — Fri, 14 Jun 2024 19:51:50 GMT

Both are set in the events as a field

Re: comparing datetime with current datettime

AnanthaS — Fri, 14 Jun 2024 20:10:00 GMT

Just tried making AND in Upper case, but didnt wrk

Re: comparing datetime with current datettime

yuanliu — Fri, 14 Jun 2024 20:55:44 GMT

Can you post some dataset as well as test time that you think should yield results but did not? (To eliminate the complexity of the test, you can compare with a fixed epoch time instead of now().) I ran the following and your where command gives 2 to 3 outputs depending on when in the calendar minute the emulation runs.

| makeresults count=10 | streamstats count as offset | eval _time = relative_time(_time, "-" . offset . "min"), eventStartsFrom = relative_time(_time, "+" . (10 - offset) . "min"), eventEndsAt = relative_time(eventStartsFrom, "+5min") | eval _time = now() ``` data emulation abvove ``` | fieldformat eventStartsFrom = strftime(eventStartsFrom, "%F %T") | fieldformat eventEndsAt = strftime(eventEndsAt, "%F %T") | where eventStartsFrom <= now() and eventEndsAt >= now()

One sample output is

_time	eventEndsAt	eventStartFrom	offset
2024-06-14 13:49:36	2024-06-14 13:54:36	2024-06-14 13:49:36	5
2024-06-14 13:49:36	2024-06-14 13:52:36	2024-06-14 13:47:36	6
2024-06-14 13:49:36	2024-06-14 13:50:36	2024-06-14 13:45:36	7

another output is

_time	eventEndsAt	eventStartFrom	offset
2024-06-14 13:53:11	2024-06-14 13:56:12	2024-06-14 13:51:12	6
2024-06-14 13:53:11	2024-06-14 13:54:12	2024-06-14 13:49:12	7

The final output uses _time field to display now().

Re: comparing datetime with current datettime

AnanthaS — Sat, 15 Jun 2024 06:26:52 GMT

Thanks for your time and help. I am posting my solution down in thread. Your suggestion of posting datasets I will take care in my future posts. though so as it is easy to get help.

Re: comparing datetime with current datettime

AnanthaS — Sat, 15 Jun 2024 06:30:45 GMT

I realize that now() function does not give 13 digits of epoch date time and only 10 digits length where as my other two fields viz. eventStartsFrom and eventEndsAt are having 13 digits.

eventStartsFrom = 1718394600000
now = 1718432273
eventEndsAt= 1718740200000

Hence, I multiplied now() by 1000 and then wrote this query below

index=shared_data source="lambda:maintenance_window_handler" sourcetype="httpevent" | eval nowdate = (now() * 1000 )| eval diffBeginDates = (nowdate - eventStartsFrom) | eval diffEndDates = (eventEndsAt - nowdate) | where diffBeginDates > 0 and diffEndDates > 0

After this the query behaved as intended. Thanks all for the help. (This thread can be closed now.)

Re: comparing datetime with current datettime

yuanliu — Sat, 15 Jun 2024 06:45:00 GMT

I realize that now() function does not give 13 digits of epoch date time and only 10 digits length where as my other two fields viz. eventStartsFrom and eventEndsAt are having 13 digits.
eventStartsFrom = 1718394600000
now = 1718432273
eventEndsAt= 1718740200000

You mean the two extracted fields are not epoch time, but epoch time expressed in milliseconds. Generally, it's a better idea to bring data to match now() so semantics is clearer. But considering that multiplication is more efficient than division, doing the opposite is perhaps better. I suggest to name now() * 1000 more semantically expressive, such as now_ms instead of calling it something "date". This helps future maintenance.

Re: comparing datetime with current datettime

AnanthaS — Sat, 15 Jun 2024 08:13:41 GMT

eventEndsAt and eventStartsFrom are epoch date format expresssed in milliseconds and now() is also epoch date format but not expressed in milliseconds format. I will rename the columns, thanks

Re: comparing datetime with current datettime

gcusello — Sat, 15 Jun 2024 09:34:23 GMT

hi @AnanthaS ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉