comparing datetime with current datettime

[AnanthaS]

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.

[AnanthaS · Accepted solution]

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.)

[gcusello]

hi @AnanthaS ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

[yuanliu]

---

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.

[AnanthaS]

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

[yuanliu]

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().

[AnanthaS]

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. 

[ITWhisperer]

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

[AnanthaS]

Both are set in the events as a field

[gcusello]

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

[AnanthaS]

Just tried making AND in Upper case, but didnt wrk