topic How to remove time value (null) from string value that includes date and time value in Getting Data In

How to remove time value (null) from string value that includes date and time value

jsnyderlmco — Wed, 29 Apr 2020 15:55:20 GMT

Hello all, I am trying to remove the time portion of the string value of a field that resides in our indexed data. The expiration field contains a string value as shown below as a date and time.

Query:

sourcetype="db" unique_id="00-201" expiration="*"
| eval mytime=strftime(strptime(expiration, "%m/%d/%Y"),"%m/%d/%Y") 
| table unique_id expiration | dedup unique_id

Results:

unique_id        expiration
00-201               2022-08-12 00:00:00.0

Goal: Perform a query to display expiration field value as 06-04-2022 and sort from oldest to newest

Re: How to remove time value (null) from string value that includes date and time value

richgalloway — Wed, 29 Apr 2020 17:41:07 GMT

It's not clear if you want to remove the timestamp from the event or just sort events by a timestamp that's not _time. I'll assume the latter.

Try this:

sourcetype="db" unique_id="00-201" expiration="*"
| dedup unique_id
| eval sortTime= strptime(expiration, "%m/%d/%Y")
| sort + sortTime
| eval expTime = strftime(sortTime, "%m-%d-%Y")
| table unique_id expTime

Re: How to remove time value (null) from string value that includes date and time value

jsnyderlmco — Wed, 29 Apr 2020 21:22:46 GMT

Thank you for the quick response. It worked perfectly. One of the challenges we were experiencing was the inability to sort the expiration date as a date field. I appreciate the assist.