reference table:
My query:
| eval time_period= "01-Nov-23"
| eval time_period_epoc=strptime(time_period,"%d-%b-%y")
|where epoc_time_submitted <= time_period_epoc
|join max=0 type=left current_ticket_state [|inputlookup monthly_status_state_mapping.csv|rename Status as current_ticket_state|table current_ticket_state "Ageing Lookup"]
|eval age= Final_TAT_days
|eval total_age=round(age,2)
|rangemap field=total_age "0-10days"=0-11 "11-20 Days"=11.01-20.00 "21-30 Days"=20.01-30 "31-40 Days"=30.01-40 "41-50 Days"=40.01-50 "51-60 Days"=50.01-60 "61-70 Days"=60.01-70 "71-80 Days"=70.01-80 "81-90 Days"=80.01-90 "91-100 Days"=90.01-100 ">100 Days"=100.01-1000
|chart count as count1 over work_queue by range |rename work_queue as "Owner Group"
|table "Owner Group" "11-20 Days" "21-30 Days" "31-40 Days" "41-50 Days" "51-60 Days" "61-70 Days" "71-80 Days" "81-90 Days" "91-100 Days" ">100 Days" |addtotals|addcoltotals |fillnull value="Grand Total"
my result:
Your issue is to do with the chart command, it only really supports two dimensions, in your case these appear to be work_queue and range, when you need three dimensions, work_queue, range and current_ticket_state.
This means you will have to use a stats command, then combine the work_queue and current_ticket_state into a single field, then chart using the combined field and range, then split the combined field back into two fields. Try something like this
| stats count by work_queue current_ticket_state range
| eval combined=work_queue."|".range
| chart max(count) by combined range
| eval work_queue=mvindex(split(combined,"|"),0)
| eval current_ticket_state=mvindex(split(combined,"|"),1)
| fields - combined
Iam trying to replicate reference table attached previously
My "work_queue" field contains (BROKERPORTAL_L2SUPPORT, CONSUMERPORTAL_L2SUPPORT, EMPLOYERPORTAL_L2SUPPORT, MARKETINGCLOUD_L2SUPPORT,
MEMBERPORTAL_L2SUPPORT, PROVIDERPORTAL_L2SUPPORT, SALESCLOUD_L2SUPPORT,
SERVICECLOUD_L2SUPPORT).
"Ageing Lookup" field contains (In Progress,Resolved,Awaiting Resolution Confirmation,Awaiting User Information,Dependent System,Awaiting Change,Cancelled,Reopen)
I want to divide work_queue field values further based on "Ageing lookup".
Your issue is to do with the chart command, it only really supports two dimensions, in your case these appear to be work_queue and range, when you need three dimensions, work_queue, range and current_ticket_state.
This means you will have to use a stats command, then combine the work_queue and current_ticket_state into a single field, then chart using the combined field and range, then split the combined field back into two fields. Try something like this
| stats count by work_queue current_ticket_state range
| eval combined=work_queue."|".range
| chart max(count) by combined range
| eval work_queue=mvindex(split(combined,"|"),0)
| eval current_ticket_state=mvindex(split(combined,"|"),1)
| fields - combined
how can I dedup "owner group" field without disturbing other fields in table.
my query:
| eval time_period= "01-Nov-23"
| eval time_period_epoc=strptime(time_period,"%d-%b-%y")
|where epoc_time_submitted <= time_period_epoc
|join max=0 type=left current_ticket_state [|inputlookup monthly_status_state_mapping.csv|rename Status as current_ticket_state "Ageing Lookup" as state|table current_ticket_state state]
|eval age= Final_TAT_days
|eval total_age=round(age,2)
|rangemap field=total_age "0-10days"=0-11 "11-20 Days"=11.01-20.00 "21-30 Days"=20.01-30 "31-40 Days"=30.01-40 "41-50 Days"=40.01-50 "51-60 Days"=50.01-60 "61-70 Days"=60.01-70 "71-80 Days"=70.01-80 "81-90 Days"=80.01-90 "91-100 Days"=90.01-100 ">100 Days"=100.01-1000
| stats count by work_queue state range
| eval combined=work_queue."|".state
| chart max(count) by combined range
| eval work_queue=mvindex(split(combined,"|"),0)
| eval state=mvindex(split(combined,"|"),1)
| fields - combined
|table work_queue state "11-20 Days" "21-30 Days" "31-40 Days" "41-50 Days" "51-60 Days" "61-70 Days" "71-80 Days" "81-90 Days" "91-100 Days" ">100 Days" |rename work_queue as "Owner Group" | fillnull value=0 |addtotals
What would be your expected output?
thanks this one helps
It is not clear what you want - please share some sample events and a description of what you are trying to do, and your expected results.
Iam trying to replicate reference table attached previously
My "work_queue" field contains (BROKERPORTAL_L2SUPPORT, CONSUMERPORTAL_L2SUPPORT, EMPLOYERPORTAL_L2SUPPORT, MARKETINGCLOUD_L2SUPPORT,
MEMBERPORTAL_L2SUPPORT, PROVIDERPORTAL_L2SUPPORT, SALESCLOUD_L2SUPPORT,
SERVICECLOUD_L2SUPPORT).
"Ageing Lookup" field contains (In Progress,Resolved,Awaiting Resolution Confirmation,Awaiting User Information,Dependent System,Awaiting Change,Cancelled,Reopen)
I want to divide every work_queue field value further based on "Ageing lookup".