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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...