Splunk Search

How can I change a field to a date field?

maria2691
Path Finder

Hello Everyone

I have a field Month which has values like April 2017, May 2018,...
I am calculating with these using a stats command and I would like to sort by the Months in descending order.
When I use the sort command the values are sorted by Alphabetical order.
Hence I used strftime to convert the field into a date field, however I do not get any result.
Please find the query I have used below and help me with the corrections!

    source=*
    | fillnull value=0 "Budget Overrun percentage" 
    | eval Month=SUBSTR('Month',4, 15) 
    | eval "Budget Overrun percentage"= SUBSTR('Budget Overrun percentage', 1,1) 
    | stats values("Budget Overrun percentage") by Month 
    | sort by Month

Thanks
Maria Arokiaraj

Tags (2)
0 Karma

micahkemp
Champion

Can you include what Month looks like prior to your changing it with substr?

0 Karma

maria2691
Path Finder

Hello @elliotproebstel

This does not seem to be working 😞
Not getting any results when using these commands.

0 Karma

493669
Super Champion

when you try below what output you are receiving? d

source=* | fillnull value=0 "Budget Overrun percentage" 
0 Karma

elliotproebstel
Champion

By line 5, if Month contains values like "April 2017", "May 2018", etc., then the following should work to replace line 6:

| eval parsable_date="01 ".Month
| eval sort_date=strptime(parsable_date, "%d %B %Y")
| sort sort_date

And if you don't like seeing those extra fields there, you could remove them by adding this to the end:

| fields - sort_date parsable_date

Basically, I create parsable_date because I don't think strptime can create an epoch date string for a date that doesn't have a day specified.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...