topic Re: fix date format to extracted eval field in Getting Data In

How to fix date format to extracted eval field?

pp3295 — Wed, 10 Aug 2022 14:18:46 GMT

index="indnewwrapper" | search rfq_id: | join [ search index="indnewwrapper" | search rfq_id: | eval validateEmailMessagecomplete=if(like(_raw,"%validateEmailMessage()%"), sent_date,"1900-01-01 12:00:00.000") ] | eval validateEmailMessagecomplete1=strftime(strftime(validateEmailMessagecomplete,"%A %B %d %Y %I:%M:%S %p %Z"),"%Y-%m-%d %H:%M:%S") | table pRFQ_Id,validateEmailMessagecomplete,validateEmailMessagecomplete1

I am finding a string in a search and extracting a validateEmailMessagecomplete date. using like function.

i am getting desired output but i am not able to change to datetime format validateEmailMessagecomplete1 it shows blank value

i searched various post on the forum. but did not found desired solution.

Re: fix date format to extracted eval field

ITWhisperer — Wed, 10 Aug 2022 09:57:54 GMT

You are using strftime twice, you need to use strptime for the inner function to parse the string into an epoch time before formatting it

| eval validateEmailMessagecomplete1=strftime(strptime(validateEmailMessagecomplete,"%A %B %d %Y %I:%M:%S %p %Z"),"%Y-%m-%d %H:%M:%S")

Re: fix date format to extracted eval field

pp3295 — Wed, 10 Aug 2022 10:02:29 GMT

checked by your way. still now luck. But thanks for your support.

Re: fix date format to extracted eval field

ITWhisperer — Wed, 10 Aug 2022 10:09:28 GMT

Epoch dates run from 1970 not 1900 - try this

| eval validateEmailMessagecomplete=if(like(_raw,"%validateEmailMessage()%"), sent_date,"1970-01-01 00:00:00.000")

Having said that, what is it you are trying to achieve with the join command? Perhaps there is another way to approach it

Re: fix date format to extracted eval field

pp3295 — Wed, 10 Aug 2022 10:18:26 GMT

index="indnewwrapper" | search rfq_id:
| join [ search index="indnewwrapper" | search rfq_id:
| eval validateEmailMessagecomplete=if(like(_raw,"%validateEmailMessage()%"), sent_date,"1980-01-01 12:00:00.000") ]
| eval validateEmailMessagecomplete1=strptime(validateEmailMessagecomplete,"%A %B %d %Y %I:%M:%S %p %Z")
| table pRFQ_Id,validateEmailMessagecomplete,validateEmailMessagecomplete1

Re: fix date format to extracted eval field

ITWhisperer — Wed, 10 Aug 2022 10:35:52 GMT

OK, assuming the sent_date matches the format string you are using, the string you are using if validateEmailMessage doesn't exist in _raw should match this format. Try it this way

| eval validateEmailMessagecomplete=if(like(_raw,"%validateEmailMessage()%"), sent_date,"Thursday January 01 1970 01:00:00 AM BST") ]

Re: fix date format to extracted eval field

pp3295 — Wed, 10 Aug 2022 11:06:41 GMT

thanks for your reply. i think problem is

when i individually use table sent_date , i found blank rows. because of this solution is not working. can we omit blank rows .

Re: fix date format to extracted eval field

ITWhisperer — Wed, 10 Aug 2022 11:43:53 GMT

OK The time formats have to match the format being used.

| eval validateEmailMessagecomplete=if(like(_raw,"%validateEmailMessage()%"), sent_date,"1970-01-01 00:00:00.000") ] | eval validateEmailMessagecomplete1=strptime(validateEmailMessagecomplete,"%Y-%m-%d %H:%M:%S.%3N")

Re: fix date format to extracted eval field

pp3295 — Wed, 10 Aug 2022 11:48:53 GMT

thanks bhai ( bro ), its working. showing values

I am new to splunk, learning from this forum and youtube. do you know any good channels for splunk learning.

Re: fix date format to extracted eval field

ITWhisperer — Wed, 10 Aug 2022 11:58:09 GMT

Hard to say what is good - it depends on your learning style - there are tutorials, and courses, there are presentations from .conf and BSides, there are example dashboards and other apps in splunkbase, and then there's just trying stuff out in a sandbox environment just to see what it does.