Stats count & chart over combo?

[hollybross1219]

I'ma beginner with Splunk hoping someone can help me with my syntax around the following query.

I have queries with defined categories in a given field (error_msg_service):

index=wsi_tax_summary sourcetype=stash partnerId=* error_msg_service=* ein=* ein!="" tax_year=2019 capability=W2
| eval error_msg_service = case(match(error_msg_service, "OK"), "Success", match(error_msg_service, "W2 forms"), "Forms 
Unavailable", match(error_msg_service, "Invalid Credentials"), "Invalid Credentials", 1==1, "Other") 
| stats dc(intuit_tid) as Total by partnerId ein error_msg_service

This will give me 4 columns: partnerId, ein, error_ms_service, and total count.

My goal combines providing granularity of stats but then creating multiple columns as what is done with chart for the unique values I've defined in my case arguments, so that I get the following columns

| partnerId | ein | error_msg_service when equal to "Success" | error_msg_service when equal to "Forms Unavailable" | error_msg_service when equal to "Invalid Credentials" |error_msg_service when equal to "Other" |Total count of events

Has someone done something like this that could help?

[somesoni2 · Accepted solution]

Try like this

index=wsi_tax_summary sourcetype=stash partnerId=* error_msg_service=* ein=* ein!="" tax_year=2019 capability=W2
 | eval error_msg_service = case(match(error_msg_service, "OK"), "Success", match(error_msg_service, "W2 forms"), "Forms 
 Unavailable", match(error_msg_service, "Invalid Credentials"), "Invalid Credentials", 1==1, "Other") 
| eval partnerId_ein=partnerId."###".ein
 | chart dc(intuit_tid) as Total by partnerId_ein error_msg_service
| rex field=partnerId_ein "(?<partnerId>.+)###(?<ein>.+)" | fields - partnerId_ein
| table partnerId ein *

OR

  index=wsi_tax_summary sourcetype=stash partnerId=* error_msg_service=* ein=* ein!="" tax_year=2019 capability=W2
 | eval error_msg_service = case(match(error_msg_service, "OK"), "Success", match(error_msg_service, "W2 forms"), "Forms Unavailable", match(error_msg_service, "Invalid Credentials"), "Invalid Credentials", 1==1, "Other") 
 | stats dc(eval(if(error_msg_service="Success",intuit_tid,null()))) as "Success" 
dc(eval(if(error_msg_service="Forms 
 Unavailable",intuit_tid,null()))) as "Forms Unavailable" 
dc(eval(if(error_msg_service="Invalid Credentials",intuit_tid,null()))) as "Invalid Credentials" 
dc(eval(if(error_msg_service="Other",intuit_tid,null()))) as "Other" by partnerId ein 

[hollybross1219]

this is perfect, thank you!