Splunk Search

How to extract month from a date field and sort by this without using the _time timestamp?

atammana_splunk
Splunk Employee
Splunk Employee

Hi all, I'm a bit new to Splunk - I'm trying to sort some data by month, but I'm running into some roadblocks doing so. I'd like to create a separate field, "month", based on the month value in a field called "date" with format "YYYY/MM/DD HH:MM:SS". I've tried

   *code* |  eval month = strftime(date,"%m") | stats sum(field1) by field2, field3, month

but it doesn't seem to be working for this format, as the "month" field shows up blank for all results and I get no results when trying to sort by this field. However, when I try to reorganize the date itself into a different format, it works:

 *code* | eval new_date=strftime(strptime(date,"%Y/%m/%d"),"%m/%d/%y") | stats sum(field1) by field2, field3, new_date

And the output is as expected, with the information sorted by relevant fields and the new_date field formatted as MM/DD/YY.

Any thoughts on how I can do something similar and just get either "01" or "January" as the month field output?

0 Karma
1 Solution

mreynov_splunk
Splunk Employee
Splunk Employee

if you are wanting to extract month from event time, Splunk already does this for you by storing the month in date_month field.
if it's another time field you are working with, you need to make sure you convert your time into epoch time before extracting the month, like you are doing in the second example.

Try: new_date=strftime(strptime(date,"%Y/%m/%d"),"%m")

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try this

 *code* | eval new_date=strftime(strptime(date,"%Y/%m/%d"),"%m") | stats sum(field1) by field2, field3, new_date

richgalloway
SplunkTrust
SplunkTrust

Your first attempt failed because date field is not an epoch timestamp.

You should be able to use the standard date_month field. That will give you the name of the month in which the event occurred.

---
If this reply helps you, Karma would be appreciated.
0 Karma

mreynov_splunk
Splunk Employee
Splunk Employee

if you are wanting to extract month from event time, Splunk already does this for you by storing the month in date_month field.
if it's another time field you are working with, you need to make sure you convert your time into epoch time before extracting the month, like you are doing in the second example.

Try: new_date=strftime(strptime(date,"%Y/%m/%d"),"%m")

n5zap
Explorer

I downvoted this post because it is not always the case that splunk automatically extracts the date_* fields.

atammana_splunk
Splunk Employee
Splunk Employee

Worked like a charm. Thank you!

0 Karma

n5zap
Explorer

Note that Splunk does not always do this. I got here because I have an event type that never includes the date_* fields. I'm sure there is a technical reason, but I don't know what it is yet and need to extract the month because Splunk is not doing so.

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 ...