Splunk Search

How to arrange column sequentially by month?

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

Contributor

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

0 Karma

Champion

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

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

SplunkTrust
SplunkTrust

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

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

Explorer

already included my current search!

0 Karma

SplunkTrust
SplunkTrust

@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

Explorer

this is my current search

index="acnpmocapacitychargeabilityidx"
| 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 Rosterlookup.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