Splunk Search

Stats count & chart over combo?

hollybross1219
Path Finder

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?

Tags (5)
0 Karma
1 Solution

somesoni2
Revered Legend

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 

View solution in original post

0 Karma

somesoni2
Revered Legend

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 

View solution in original post

0 Karma

hollybross1219
Path Finder

this is perfect, thank you!

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!