Splunk Search

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

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

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

SplunkTrust
SplunkTrust

Try this

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

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, an upvote would be appreciated.
0 Karma

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

Explorer

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

Splunk Employee
Splunk Employee

Worked like a charm. Thank you!

0 Karma

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