How to search for Entries Between DateTime field value?

akpuvvada — Tue, 14 Feb 2023 22:20:21 GMT

I am trying to find entries between a date-time range based on a field in the event 'Date'. It date-time value of the field is in the format - %Y-%m-%d %H:%M:%S.

I am using below query:

(index=myindex) ((strptime(Date, "%Y-%m-%d %H:%M:%S")>=strptime("2023-02-11 00:00:00", "%Y-%m-%d %H:%M:%S")) AND (strptime(Date, "%Y-%m-%d %H:%M:%S")>=strptime("2023-02-11 23:59:59", "%Y-%m-%d %H:%M:%S")))

It is giving me below Error:

Error in 'search' command: Unable to parse the search: Comparator '>=' has an invalid term on the left hand side: (Date "%Y-%m-%d %H:%M:%S").

Sample Data:

{ "InterfaceNumber": "I-XXXX", "SystemCode": "sys", "Element": "JobID:bw0a104of", "Data": "", "Severity": "Error", "ProcessContext_ProcessId": "bw0a104of", "Subject": "/api/login", "Date": "2023-02-14 12:25:01", "ErrorMessages": "This is an Error", "ProcessContext_ProjectName": "MyApp.application", "HostName": "localhost.mydomain.com", "ProcessContext_RestartedFromCheckpoint": false, "ProcessContext_EngineName": "MyApp" }

Not sure what I am doing wrong. Any help appreciated.

Re: Search for Entries Between DateTime field value

bowesmana — Tue, 14 Feb 2023 21:00:36 GMT

You can't do these type of criteria on the search command, as parsing data has to be done in the eval - unless you set up a calculated field that does these evals for you.

This is how you would do it

(index=myindex) | eval Date_value=strptime(Date, "%Y-%m-%d %H:%M:%S") | eval min_Date=strptime("2023-02-11 00:00:00", "%Y-%m-%d %H:%M:%S") | eval max_Date=strptime("2023-02-12 00:00:00", "%Y-%m-%d %H:%M:%S") | where (Date_value >= min_Date AND Date_value < max_Date

Note that your second comparison said >= but I imagine you means <=.

However, I also changed the second strptime for end date to be 00:00:00 on the 12th and used less than rather than <=, as yours will miss any events that come in between 23:59:59 and 00:00:00 (1000 milliseconds gap).

Note that your data range is always searched by the _time field, so if your Date field has the same value as the _time field, then you don't need any of this, you just use earliest/latest values in the search or time picker.

topic Re: Search for Entries Between DateTime field value in Splunk Search

How to search for Entries Between DateTime field value?

Re: Search for Entries Between DateTime field value