topic How to edit my search to reformat columns to rows and rows to columns for my resulting table? in Splunk Search

How to edit my search to reformat columns to rows and rows to columns for my resulting table?

splunker9999 — Thu, 01 Sep 2016 17:03:17 GMT

Hi,

I have this search below, which produces results, but need to format these in a report.

index=imdc_w sourcetype="imdp:ITSM:changes"                        
|rename assignment_group as Assignmentgroup, u_chg_plan_impl_startdate as Time u_chg_reject_count as Reject_count ,u_chg_exped as Expedited, u_chg_unauthorized as Unauthorized, u_chg_plan_impl_stopdate as stopdate, closed_at as closeddate 
| where Assignmentgroup != "assignment_group"  
| join type=left Assignmentgroup [  |inputlookup sys_user_group.csv]  
| eval Platform=if(isnull(Platform), "Unknown Platform", Platform) 
| eval Tier=if(isnull(Tier), "L2", Tier) 
|search Tier=* 
| search Platform="*" 
| search Director="*" 
| search Assignmentgroup="*" 
| search VP="*"  
| eval type = if (type="Service Restore Emergency", number, null())   
| eval Reject_count = if(Reject_count>0 AND state="Closed", number, null())  
|eval Expedited=if(Expedited="true",number,null())  
|eval Unsuccessful=if(u_chg_closure_code ="Unsuccessful",number,null()) 
|dedup change_request 
|stats dc(type) as Emergency dc(Reject_count) as Rejected dc(Expedited) as Expedited dc(Unauthorized) as Unauthorized dc(Unsuccessful) as Unsuccessful dc(change_request) as Total  by date_month
|xyseries date_month

Currently result output:

date_month    Emergency    Rejected    Unauthorized    Unsuccessful
Aug           0            0           2               3
Jul           1            2           2               1

I want to see results in below format:

                 Aug     Jul     Jun
Emergency        0       0       0
Rejected         2       1       0
Unauthorized     0       0       3
Unsuccessful

Can you please help?

THanks

Re: How to edit my search to reformat columns to rows and rows to columns for my resulting table?

sundareshr — Thu, 01 Sep 2016 17:09:05 GMT

Try this

index=imdc_w sourcetype="imdp:ITSM:changes"  
| rename assignment_group as Assignmentgroup, u_chg_plan_impl_startdate as Time u_chg_reject_count as Reject_count ,u_chg_exped as Expedited, u_chg_unauthorized as Unauthorized, u_chg_plan_impl_stopdate as stopdate, closed_at as closeddate  
| where Assignmentgroup != "assignment_group" 
| join type=left Assignmentgroup [  |inputlookup sys_user_group.csv] 
| eval Platform=if(isnull(Platform), "Unknown Platform", Platform)  
| eval Tier=if(isnull(Tier), "L2", Tier) 
| search Tier=* 
| search Platform="*" 
| search Director="*" 
| search Assignmentgroup="*" 
| search VP="*"  
| eval type = if (type="Service Restore Emergency", number, null())  
| eval Reject_count = if(Reject_count>0 AND state="Closed", number, null())   
| eval Expedited=if(Expedited="true",number,null())   
| eval Unsuccessful=if(u_chg_closure_code ="Unsuccessful",number,null()) 
| dedup change_request   
| stats dc(type) as Emergency dc(Reject_count) as Rejected dc(Expedited) as Expedited dc(Unauthorized) as Unauthorized dc(Unsuccessful) as Unsuccessful dc(change_request) as Total  by date_month
| untable date_month groups count 
| xyseries groups date_month count

Re: How to edit my search to reformat columns to rows and rows to columns for my resulting table?

splunker9999 — Thu, 01 Sep 2016 17:37:45 GMT

Hi,

How can I order results by month instead by alphabetical order.

index=imdc_w sourcetype="imdp:ITSM:changes"                        |rename assignment_group as Assignmentgroup, u_chg_plan_impl_startdate as Time u_chg_reject_count as Reject_count ,u_chg_exped as Expedited, u_chg_unauthorized as Unauthorized, u_chg_plan_impl_stopdate as stopdate, closed_at as closeddate                           | where Assignmentgroup != "assignment_group"                     | join type=left Assignmentgroup [  |inputlookup sys_user_group.csv]                | eval Platform=if(isnull(Platform), "Unknown Platform", Platform)                     | eval Tier=if(isnull(Tier), "L2", Tier) |search Tier=*                      | search Platform="*" | search Director="*" | search Assignmentgroup="*" | search VP="Kevin L Murray"                  | eval type = if (type="Service Restore Emergency", number, null())              | eval Reject_count = if(Reject_count>0 AND state="Closed", number, null())                  |eval Expedited=if(Expedited="true",number,null())      |eval Unsuccessful=if(u_chg_closure_code ="Unsuccessful",number,null())       |dedup change_request                   |stats dc(type) as Emergency dc(Reject_count) as Rejected dc(Expedited) as Expedited dc(Unauthorized) as Unauthorized dc(Unsuccessful) as Failed dc(change_request) as "Total   Requested" by date_month|rename date_month as Month|replace august with Aug july with Jul june with Jun may with May april with Apr march with Mar febrauary with Feb january with Jan september with Sep october with October november with Nov december with Dec
| untable Month groups count | xyseries groups Month count|addtotals fieldname=YTD|rename groups as " "

Re: How to edit my search to reformat columns to rows and rows to columns for my resulting table?

sundareshr — Thu, 01 Sep 2016 17:44:13 GMT

See if this works

 index=imdc_w sourcetype="imdp:ITSM:changes"                        
| rename assignment_group as Assignmentgroup, u_chg_plan_impl_startdate as Time u_chg_reject_count as Reject_count ,u_chg_exped as Expedited, u_chg_unauthorized as Unauthorized, u_chg_plan_impl_stopdate as stopdate, closed_at as closeddate                           
| where Assignmentgroup != "assignment_group"                     
| join type=left Assignmentgroup
    [  
    | inputlookup sys_user_group.csv]                
| eval Platform=if(isnull(Platform), "Unknown Platform", Platform)                     
| eval Tier=if(isnull(Tier), "L2", Tier) 
| search Tier=*                      
| search Platform="*" 
| search Director="*" 
| search Assignmentgroup="*" 
| search VP="Kevin L Murray"                  
| eval type = if (type="Service Restore Emergency", number, null())              
| eval Reject_count = if(Reject_count>0 AND state="Closed", number, null())                  
| eval Expedited=if(Expedited="true",number,null())      
| eval Unsuccessful=if(u_chg_closure_code ="Unsuccessful",number,null())       
| dedup change_request                   
| eval Month=strftime(_time, "%m-%b%)
| stats dc(type) as Emergency dc(Reject_count) as Rejected dc(Expedited) as Expedited dc(Unauthorized) as Unauthorized dc(Unsuccessful) as Failed dc(change_request) as "Total   Requested" by Month
| untable Month groups count 
| xyseries groups Month count
| addtotals fieldname=YTD
| rename groups as " "

Re: How to edit my search to reformat columns to rows and rows to columns for my resulting table?

s2_splunk — Thu, 01 Sep 2016 17:54:43 GMT

You can probably also remove |search Tier=* | search Platform="*" because those fields will always have a value, given the prior evals for them.