Splunk Search

Cannot sort dynamic column in date format

urapaveerapan
Explorer

Hi,
I tried to summary data in each assignment_group_name by month
here is my code:
index="snow" sourcetype="snow:incident" assignment_group_name="AM*"
|dedup number
|chart count over assignment_group_name by incoming_month
|addtotals
|sort by assignment_group_name, incoming_month

But I need the column to be arranged by date format by starting from the oldest month to the latest one.
I've tried to convert the incoming_month column by trying

|eval month = strftime(incoming_month, "%b-%y")
but it's not working. Please help.

alt text

0 Karma

woodcock
Esteemed Legend

Like this:

index="snow" sourcetype="snow:incident" assignment_group_name="AM*" incoming_month="*" 
| dedup number
| chart count over assignment_group_name by date_mdayincoming_month 
| rename Jan* AS "           Jan*"
         Feb* AS "          Feb*"
         Mar* AS "         Mar*"
         Apr* AS "        Apr*"
         May* AS "       May*"
         Jun* AS "      Jun*"
         Jul* AS "     Jul*"
         Aug* AS "    Aug*"
         Sep* AS "   Sep*"
         Oct* AS "  Oct*"
         Nov* AS " Nov*"
|addtotals
|sort 0 BY assignment_group_name incoming_month

Note that Oct has been renamed with 2 leading spaces and Nov with just one (and Dec not at all).
The whitespace is invisible in the chart but forces the alphabetical order that you desire. This would make a fine macro.

niketn
Legend

You can either create a lookup table with Month Abbreviations to month in digits like Jan - 01(Jan), Feb -02(Feb) etc or write a macro to perform series rename as shown below. Once you change Dec-16 to 12-Dec-16 it will show up sorted. You can also set usenull=f to hide null fields and add incoming_month="*" to your base search. (PS: All rename below should be shifted to a macro).

index="snow" sourcetype="snow:incident" assignment_group_name="AM*" incoming_month="*" 
|dedup number
|chart count over assignment_group_name by date_mdayincoming_month 
|rename "Jan*" as "01(Jan)*"
|rename "Feb*" as "02(Feb)*"
|rename "Mar*" as "03(Mar*"
|rename "Apr*" as "04(Apr)*"
|rename "May*" as "05(May)*"
|rename "Jun*" as "06(Jun)*"
|rename "Jul*" as "07(Jul)*"
|rename "Aug*" as "08(Aug)*"
|rename "Sep*" as "09(Sep)*"
|rename "Oct*" as "10(Oct)*"
|rename "Nov*" as "11(Nov)*"
|rename "Dec*" as "11(Dec)*"
|addtotals
|sort by assignment_group_name, incoming_month
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

rjthibod
Champion

Sorting of columns is always done in some way (alphabetic or ascii or something else) when left to its own devices. So the answer to your question is there is no way to do exactly what you want without adding some extra specificity, or changing the labels.

The latter option is simple, but you have to use the number version of month, e.g., |eval month = strftime(incoming_month, "%m-%y") or |eval month = strftime(incoming_month, "%Y-%m")

The more specific option is to use the column names with wildcards, like this; however, this will only work for the same calendar year because you put the year at the end of the column name

| fields assignment_group_name Jan* Feb* Mar* Ap* May* Jun* Jul* Aug* Sep* Oct* Nov* Dec* Total*

So, if you want to make Splunk do the work for you, you need to switch to |eval month = strftime(incoming_month, "%Y-%m")

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...