topic Re: How do you do sort values in format %m/%y ? in Splunk Search

How do you do sort values in format %m/%y ?

dfigurello — Sat, 12 Jul 2014 01:34:21 GMT

Hi splunkers,

I have a search, which return a field called duration. The field durantion is like 03/2013 (%m/%y). I would like to sort these values.

When I runs my search, I have following values:

(...) | stats count by duration | sort - duration

12/2013  1
11/2013  1
10/2013  1
09/2013  1
08/2013  1
07/2014  1 *** Look my problem
07/2013  1 ***
06/2014  1
05/2014  1 
04/2014  1
03/2014  1
02/2014  1
01/2014      1

Any idea?

Cheers!

Re: How do you do sort values in format %m/%y ?

lguinn2 — Sat, 12 Jul 2014 07:34:49 GMT

You could do this

yoursearchhere
| eval datetime = strptime(duration,"%m/%Y")
| sort datetime
| fields - datetime

Re: How do you do sort values in format %m/%y ?

dfigurello — Sun, 13 Jul 2014 04:52:25 GMT

Hey Iguinn,

Thank you for your response, but when I use the chart command it doesn't work.

I created a csv file to replicate the problem in the customer, for example:

date gender
01/2014 F
01/2013 M
01/2014 F
01/2014 M
11/2013 F
11/2013 M
10/2013 F
10/2014 M
10/2013 F
09/2013 F
09/2013 M
09/2013 F

I want to create a chart for analyze the gender and period.

This is my search:

source="C:\...\teste.csv" | chart count over duration by gender | sort duration

http://answers.splunk.com//storage/splunk1error_1.jpg

Any idea ?

Tks!

Re: How do you do sort values in format %m/%y ?

lguinn2 — Sun, 13 Jul 2014 05:39:18 GMT

Do this

source="C:\...\teste.csv" | eval datetime = strptime(duration,"%m/%Y") | chart count over datetime by gender | fieldformat datetime=strftime(datetime,"%m/%Y")

Re: How do you do sort values in format %m/%y ?

dfigurello — Sun, 13 Jul 2014 06:00:11 GMT

Iguinn,

When I runs source="C:...\teste.csv"
| eval datetime = strptime(duration,"%m/%Y") , I saw that datetime field is not create. I believe that is my problem.

Tks!

Re: How do you do sort values in format %m/%y ?

somesoni2 — Sun, 13 Jul 2014 06:13:45 GMT

Try this

source="C:\...\teste.csv" | eval datetime = strptime("01/".duration,"%d/%m/%Y") | chart count over datetime by gender | fieldformat datetime=strftime(datetime,"%m/%Y")

The strptime needs day part as well to work.

Re: How do you do sort values in format %m/%y ?

dfigurello — Sun, 13 Jul 2014 06:20:01 GMT

I get it!

Thank you so much somesoni2 and Iguinn!
Monday I'll test customer's splunk..

Cheers!