Getting Data In

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

jsnyderlmco
Engager

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

alt text

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

jsnyderlmco
Engager

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.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.