Using chart to sort over multiple eval fields

[shubhs9]

Hi, I have this requirement which I am trying to test and retrieve the results for.

Premise:
I have a set of ticketing data which contain details of "Owner Name", "Ticket Resolution Status", "Ticket Raised By".
The values in them are as mentioned in an example:

Owner Name : owner_name1601
Ticket Resolution Status: Resolved/Unresolved
Ticket Raised By: User/Admin

I need to tdisplay the result in following format:

Assigned To Unresolved Resolved System Generated User Generated

owner_name2384 3 20 0 0
owner_name261 3 45 0 0
owner_name1387 2 23 0 0
owner_name1404 2 9 0 0

I have written a code which looks like this:

index=ticket_data
| eval res_desc=if(current_ticket_state="Resolved", "Resolved", "Unresolved")
| chart count by owner_name,res_desc
| appendcols[search index=ticket_data
| eval sys_user=if(system_user="BI Admin", "Admin", "User")
| chart count by owner_name,sys_user]

The issue is that the above code uses a subsearch and the limit to that is 50,000 which wouldn't help me in future.

The other code that i tried writing was this:

index=ticket_data
| eval res_desc=if(current_ticket_state="Resolved", "Resolved", "Unresolved")
| eval sys_user=if(system_user="BI Admin", "Admin", "User")
| chart count as user by owner_name,res_desc, sys_user

This clearly doesn't work.

Using stats doesn't help me get the results in the required format.

Could someone suggest me a workaround for this problem statement?

Thanks

[yuanliu · Accepted solution]

If each of the criteria is a limited set, I'd just evaluate each value then add them up.

index=ticket_data 
| eval Resolved=if(current_ticket_state=="Resolved",1,0)
| eval Unresolved=if(current_ticket_state=="Unresolved",1,0)
| eval System_gen=if(system_user=="BI Admin",1,0)
| eval User_gen=if(system_user=="User",1,0)
| stats sum(Unresolved) as Unresolved sum(Resolved) as Resolved sum(System_gen) as "System Generated"
  sum(User_gen) as "User Generated" by owner_name
| rename owner_name as "Assigned To"

[shubhs9]

Worked perfectly. Thanks for the help.