Owner group need to further split-up based on "Ageing Lookup" field. check reference table & suggest changes to query.

[krishna1]

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:

[krishna1]

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".

[ITWhisperer · Accepted solution]

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

[krishna1]

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

[ITWhisperer]

What would be your expected output?

[krishna1]

thanks this one helps

[ITWhisperer]

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.

[krishna1]

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".