Splunk Search

How to chart data by month with sorting order?

jeffbat
Path Finder

I am running into an issue when I am trying to get a chart to populate with the data as I am expecting.

I am running a search where the data is from IIS logs where it parsing out the referrer_stem  and then counting the total of each referrer_stem per month.  I am also splitting out the month field by both the shortname and numerical value (for testing each on the sort).

this is the end portion of my search:

 

 

| eval date_month=strftime(_time, "%b")
| eval number_month=strftime(_time, "%m")
| chart count BY referrer_stem, date_month
| sort 10 - count

 

 


The issue I am having is if I do this with date_month field then it shows columns or bars out of order (i.e. it shows as Feb Jan) where as if I do it by number_month it is correct (i.e. 01 02). 

I want it to show in the correct order but using the month's shortname.

I did try to use a case statement when using number_month but that doesn't work because after the chart command the field name seems to not exist (or I just don't know how to access the right name).

Any help  or insight on this would be greatly appreciated.

 

Labels (2)
Tags (1)
0 Karma
1 Solution

VatsalJagani
Champion

Try something like:

| eval number_month=strftime(_time, "%m")
| chart count BY referrer_stem, number_month
| sort 10 - count
| rename "01" as "Jan", "02" as "Feb", and so on...

VatsalJagani_0-1644991039591.png

FYI, your sort - count will not work because as you can see in the screenshot when you will use the chart command, you don't have a column called "count".

View solution in original post

0 Karma

jeffbat
Path Finder

Thank you everyone for all of the help.  This is the solution I ended up utilizes as it was pointed out to me that we would be showing some months from previous year and would want to have them ordered correctly so needed to add in the year.

| bin _time span=1mon 
| eval c_time=strftime(_time,"%Y-%m")
| chart count BY referrer_stem, c_time
| addtotals 
| rename "*-01" AS "*-Jan" "*-02" AS "*-Feb" "*-03" AS "*-Mar" "*-04" AS "*-Apr" "*-05" AS "*-May" "*-06" AS "*-Jun" "*-07" AS "*-Jul" "*-08" AS "*-Aug" "*-09" AS "*-Sep" "*-10" AS "*-Oct" "*-11" AS "*-Nov" "*-12" AS "*-Dec"
| sort 10 - Total
| fields - Total

I also solved the issue of the sorting by totaling the rows and using that column to do the sort (this ALSO solved the fact of making sure I had to top used referrer_stems) and then just removing the field after the sort

 

0 Karma

VatsalJagani
Champion

Try something like:

| eval number_month=strftime(_time, "%m")
| chart count BY referrer_stem, number_month
| sort 10 - count
| rename "01" as "Jan", "02" as "Feb", and so on...

VatsalJagani_0-1644991039591.png

FYI, your sort - count will not work because as you can see in the screenshot when you will use the chart command, you don't have a column called "count".

0 Karma

VatsalJagani
Champion

Other way is:

| eval date_month=strftime(_time, "%b")
| chart count BY referrer_stem, date_month
| table referrer_stem, Jan, Feb, Mar, .....
0 Karma

PickleRick
Ultra Champion

Working with time strings is tricky. How should splunk know that "feb" shoud be after "jan"? It's before in lexicographical order.

If you want to sort by time, leave it as is - as a unix timestamp. Just do a fieldformat so it's displayed the way you want. If you want only some part of the date (like the month in your case), do binning.

0 Karma

PickleRick
Ultra Champion

Ahh, I missed that it's about _column_order.

That's more tricky. If your column set is constant you simply add

| table columnA columnD columnC ...

at the end of your search and you have your order.

If you however want columns in a particular order but don't know the exact names, transposing, sorting and transposing back is the way to go.

0 Karma

VatsalJagani
Champion

Yup, I missed a simpler solution. The table should also solve.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...