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

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

atammana_splunk — Fri, 24 Jul 2015 19:21:17 GMT

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?

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

mreynov_splunk — Fri, 24 Jul 2015 20:22:27 GMT

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")

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

richgalloway — Fri, 24 Jul 2015 20:23:37 GMT

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.

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

somesoni2 — Fri, 24 Jul 2015 21:26:58 GMT

Try this

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

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

atammana_splunk — Mon, 27 Jul 2015 16:56:02 GMT

Worked like a charm. Thank you!

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

n5zap — Sun, 04 Feb 2018 05:51:58 GMT

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.

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

n5zap — Sun, 04 Feb 2018 05:52:30 GMT

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