Splunk Search

Sorting Field name that is dynamically named in a particular order

Parameshwara
Path Finder

My search compares between the past two month (i.e. now we are in March, my search compares between January & February), and provides a column chart with the Field name corresponding to the two months.

My question is how do we sort the Field name? I would like to sort the Month ascending/descending (December, November, ...). At the moment the Field is sorted alphabetically. i.e for January and February, February appears first followed by January. If comparing between April and May, April will appear first followed by May. I want to arrange the field name either by ascending/descending month so that there will be a consistent arrangement of data every month.

Tags (2)
1 Solution

jonuwz
Influencer

It would be much better to store your month field as a proper time rather than the name of the month.

i.e.

date=11345456454   field1=somethgin field2=something_else

then you can sort by date (which will order the data in the way you've described) then fieldformat the column to display the actual month names. i.e.

... | fieldformat date=strftime(date,"%B")

This also has the benefit of sorting the dates correctly in the December -> January scenario.

If you absolutely cannot do that for whatever reason, then you can use a similar approach by converting the Month names to psuedo-real dates first, then applying the same technique.

 ... | eval date=strftime(strptime("01 ".date,"%d %B"),"%m") 
     | sort date
     | fieldformat date=strftime(strptime("01 ".date,"%d %m"),"%B")

Now your date field contains 01,02,03 etc, which can be sorted, the fieldformat simlpy changes the way it looks in the output

update

This should be far simpler, if i'm reading your query correctly - if not, post your output, its hard to visualise why its wrong

index=abc earliest=-2mon@mon latest=@mon 
| chart sum(Money) over Place by date_month
| table Place jan* feb* mar* apr* may* jun* jul* aug* sep* oct* nov* dec*

View solution in original post

rahul_jasrotia
Path Finder

Thanks @jonuwz, Was looking for the same problem and you're solution worked like a charm.

0 Karma

jonuwz
Influencer

updated answer

0 Karma

Parameshwara
Path Finder

Hi jonuwz, thanks for the speedy reply. Your answer seems to be more on the field value format change, and that is not what I was looking for. Let me elaborate further my problem.
This is my search:

index=abc earliest=-1mon@mon latest=@mon | stats sum(Money) as Mth by Place
| join Place [search index=abc earliest=-2mon@mon latest=-1mon@mon | stats sum(Money) as PrvMth by Place]
| table Place PrvMth Mth 
| eval month2=relative_time(now(), "-2mon@mon") | eval month1=relative_time(now(), "-1mon@mon") 
| eval month2=strftime(month2, "%B") | eval month1=strftime(month1, "%B") 
| eval {month2}=PrvMth | eval {month1}=Mth 
| fields - PrvMth Mth month1 month2

When this search is run now, it gives a table in the Fields order of Place, February, March. This is fine and the month are in ascending order. When this search was run last month, it gave a table in the Fields order of Place, February, January. How can I arrange the month to be sorted ascending/descending. In other words, I want the Fields order to be Place, January, February.

0 Karma

jonuwz
Influencer

It would be much better to store your month field as a proper time rather than the name of the month.

i.e.

date=11345456454   field1=somethgin field2=something_else

then you can sort by date (which will order the data in the way you've described) then fieldformat the column to display the actual month names. i.e.

... | fieldformat date=strftime(date,"%B")

This also has the benefit of sorting the dates correctly in the December -> January scenario.

If you absolutely cannot do that for whatever reason, then you can use a similar approach by converting the Month names to psuedo-real dates first, then applying the same technique.

 ... | eval date=strftime(strptime("01 ".date,"%d %B"),"%m") 
     | sort date
     | fieldformat date=strftime(strptime("01 ".date,"%d %m"),"%B")

Now your date field contains 01,02,03 etc, which can be sorted, the fieldformat simlpy changes the way it looks in the output

update

This should be far simpler, if i'm reading your query correctly - if not, post your output, its hard to visualise why its wrong

index=abc earliest=-2mon@mon latest=@mon 
| chart sum(Money) over Place by date_month
| table Place jan* feb* mar* apr* may* jun* jul* aug* sep* oct* nov* dec*

Parameshwara
Path Finder

That solved it. What i had tried was:

table Place January February March.....

and what that gave me is, if I am comparing between February and March, January would still show up as an empty column. By changing to:

table Place Jan* Feb* Mar*.....

only February and March shows. And the months are sorted in the way I want to as well.

Only one small issue left, when the comparison is between December of this year and January of next year, the sorting will be January, December.

0 Karma

rahul_jasrotia
Path Finder

Thanks @jonuwz, Was looking for the same problem and you're solution worked like a charm.

0 Karma

Parameshwara
Path Finder

Hi jonuwz, fyi due to character limitation on comments, have replied to your answer as a comment to my question.

Cheers.

0 Karma
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

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

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...