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, Karma 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, Karma would be appreciated.
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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...