topic Re: How do I compare the log date with the time picker date? in Splunk Search

How do I compare the log date with the time picker date?

twh1 — Wed, 26 Sep 2018 17:34:01 GMT

I want to check the records for which CREATE_TIME matches based on my date selection from time picker control. Currently, I am using the below query, which is always checking only for today's date.

 index=os_na sourcetype="oracle_os:healthcheck" "ADR Home =" | multikv | table HOSTNAME INCIDENT_ID PROBLEM_KEY CREATE_TIME TIMESTAMP | dedup INCIDENT_ID | eval create_day=substr(CREATE_TIME, 1, 10) | eval now_day = strftime(now(), "%m/%d/%Y") | where INCIDENT_ID!=" " AND create_day==now_day

Could you please help me to get desired result.

Re: How do I compare the log date with the time picker date?

kamlesh_vaghela — Wed, 26 Sep 2018 17:37:32 GMT

@twh1

Can you please share sample data for | table HOSTNAME INCIDENT_ID PROBLEM_KEY CREATE_TIME TIMESTAMP?

Do CREATE_TIME and TIMESTAMP be same?

Re: How do I compare the log date with the time picker date?

twh1 — Wed, 26 Sep 2018 17:45:12 GMT

Hi @kamlesh_vaghela ,

you can find the same at below link.

https://answers.splunk.com/answers/685745/how-do-you-get-tabular-event-with-field-value-pair.html

CREATE_TIME and TIMESTAMP is different times.

Re: How do I compare the log date with the time picker date?

kamlesh_vaghela — Wed, 26 Sep 2018 17:58:51 GMT

Hi @twh1

As per your requirement to filter event on the basis of selected time picker which needs to be applied on CREATE_TIME field I have used addinfo command. http://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/Addinfo

Please try this?

YouR_SEARCH
| eval created_time=strptime(CREATE_TIME,"%Y-%m-%d %H:%M:%S.%N") 
| addinfo 
| where (info_max_time="+Infinity" OR (created_time<info_max_time AND created_time>info_min_time))

My sample search.

| makeresults 
| eval _raw="
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              
 -------------------- ----------------------------------------------------------- ---------------------------------------- 
 102753               ORA 15064                                                   2018-05-24 15:38:50.242000 -04:00       
 107689               ORA 29740                                                   2018-05-24 17:04:00.414000 -04:00       
 112801               ORA 32701                                                   2018-05-24 20:59:14.420000 -04:00 
 " 
| multikv 
| eval created_time=strptime(CREATE_TIME,"%Y-%m-%d %H:%M:%S.%N") 
| addinfo 
| where (info_max_time="+Infinity" OR (created_time<info_max_time AND created_time>info_min_time))

Thanks

Re: How do I compare the log date with the time picker date?

twh1 — Thu, 27 Sep 2018 13:09:24 GMT

Hi @kamlesh_vaghela ,
I tried above option and selected date range as 24th May but not getting any result.

Re: How do I compare the log date with the time picker date?

kamlesh_vaghela — Thu, 27 Sep 2018 13:21:41 GMT

Hi @twh1

if you executing my given search then you have to select 2018-05-24 date to get data.

Re: How do I compare the log date with the time picker date?

twh1 — Thu, 27 Sep 2018 16:29:24 GMT

Hi @kamlesh_vaghela ,
I have selected the date range for which I have data, but still no data coming. (i.e. create date have data on 24th May to 27th May and I selected date as 24th May 00:00 - 24:00)

Re: How do I compare the log date with the time picker date?

varun8159 — Wed, 10 Oct 2018 09:07:17 GMT

Do you need earliest and latest token values of Time range picker to be used as filter for CREATE_TIME?

Re: How do I compare the log date with the time picker date?

twh1 — Thu, 11 Oct 2018 11:16:57 GMT

Yes @varun8159 . I want to compare if date date range is selected as 24th May to 25th May. I want to display the records, for which we have CREATE_TIME in between 24th May to 25th May.