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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...