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