Splunk Search

How to arrange column sequentially by month?

patricianaguit
Explorer

I'm having a trouble arranging my columns per month.
I want it to the be arranged like this:
|Sept-15-2017| |Sept-30-2017| |Sept-2017| |Oct-15-2017| |Oct-30-2017||Oct-2017|

Is there a way where i can arrange the columns like the example above?

Tags (2)
0 Karma

nikita_p
Contributor

Hi @patricianaguit,
Can you use sort command before your table command
...| sort output| table output,Target,Actuals,......

0 Karma

HiroshiSatoh
Champion

It seems that we have to define all the tables like this.

(your search)|table Sept-*-*,Sept-*,Oct-*-*,Oct-*
0 Karma

niketn
Legend

@patricianaguit, Can you add your current search which generates above table?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

patricianaguit
Explorer

already included my current search!

0 Karma

niketn
Legend

@patricianaguit please use the code button (101010) for adding data and code so that special characters do not escape

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

patricianaguit
Explorer

this is my current search

index="acn_pmo_capacity_chargeability_idx"
| table *
| fields "Enterprise ID" "Employee Level" Date "TR Period" "Charge Code" "Charge Code Description" Hours
| fields - _raw _time
| eval Date=strptime(Date, "%m/%d/%Y"), Month=strftime(Date, "%b-%Y")
| sort Date
| eval Date=strftime(Date, "%m/%d/%Y")
| rename "Enterprise ID" AS EID, "TR Period" as TRPeriod
| eval date1 = strptime(TRPeriod, "%m/%d/%Y"), TRPeriod = strftime(date1, "%b-%d-%Y")
|eval output = Month + ";" + TRPeriod |
makemv delim=";" output
| mvexpand output
| sort "EID"
| lookup wbs_lookup "Charge Code" outputnew WBS
| where WBS = "Non-Chargeable" OR WBS = "Chargeable" OR WBS = "Training"
| stats sum(eval(case(WBS == "Chargeable", Hours, 1=1,null()))) as TotalChargeableHours, sum(Hours) as TotalHours by EID, output
| fillnull value=0.00
| eval Chargeability = round((TotalChargeableHours / TotalHours) * 100, 2)
| sort EID
| fillnull value=0.00
| fields EID, output, Chargeability
| xyseries output EID Chargeability
| fields - _timediff
| table output, *
| transpose 0 header_field=output
| appendpipe [stats avg(*) as * | foreach * [eval "<>"=round('<>', 2)] | eval column="Actuals"]
| untable column output Chargeability
| rename column AS EID
| xyseries output EID Chargeability
| eval Target = "70%"
| table output, Target, Actuals, *
| eval date = strptime(output, "%b-%d-%Y")
| sort date
| fields - date, _timediff
| table output, Target, Actuals, *
| transpose 0 header_field=output
| fillnull value=0.00
| rename column as EID
| lookup Roster_lookup.csv EID outputnew "Career Level"
| table EID "Career Level" *
| transpose 0 header_field=EID
| eval getmonth=strptime(column,"%b-%e-%Y")
| fillnull value=0.00
| sort getmonth
| fields - getmonth
| transpose 0 header_field=column
| rename column AS EID
| eval "Career Level"=if(EID="Target" OR EID="Actuals", "", 'Career Level')

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 ...