query:
index=cat sourcetype=ctap host=sc58lcatp* source="*.out" "INFO: ZIP_SEARCH" OR "INFO: COMPARE" OR "INFO: COMPETITIVE_INFO" cat_userid="*" | stats count(cat_click) by cat_userid cat_role cat_click| sort cat_userid
Output
cat_userid↕ cat_role↕ cat_click↕ count(cat_click)↕
LPHILSON STORESALES ZIP_SEARCH 1
LPRIMER CARESALES ZIP_SEARCH 13
LRBROWN STORESALES ZIP_SEARCH 3
LRIVES STORESALES ZIP_SEARCH 3
LROSENE SUPERUSER COMPARE 1
LROSENE SUPERUSER COMPETITIVE_INFO 1
LROSENE SUPERUSER ZIP_SEARCH 1
LSCALES SUPERUSER ZIP_SEARCH 15
LSTEWART CARESALES COMPARE
I'm trying to get the data above into the format below.
UserID SalesChannel Uses Compare GetGlossary
ltapia Careretention 69 2 2
mmslagle Careretention 68 2 23
mpsutter Careretention 64 5 0
tdewey Careretention 46 23 0
akcarpenter Careretention 42 10 0
sklompstra Careretention 42 6 0
khaselhuhn Careretention 37 2 10
aneuens Careretention 36 5 0
jchaput Careretention 32 0 0
mmarshall3 Careretention 31 20 2
Something like this -
index=cat sourcetype=ctap host=sc58lcatp* source="*.out" cat_userid="*"
(cat_click="ZIP_SEARCH" OR cat_click="COMPARE" OR cat_click="COMPETITIVE_INFO")
| stats count as clickCount by cat_userid cat_role cat_click
| eval SalesChannel=case(cat_role=="CARESALES","Careretention",
cat_role=="STORESALES","StoreChannel",
cat_role=="SUPERUSER","SuperChannel",
true(),"DefaultChannel")
| eval Uses=if(cat_click=="ZIP_SEARCH",clickCount,0)
| eval Compare=if(cat_click=="COMPARE",clickCount,0)
| eval GetGlossary=if(cat_click=="COMPETITIVE_INFO",clickCount,0)
| stats Sum(Uses) as Uses, sum(Compare) as Compare, sum(GetGlossary) as GetGlossary by cat_userid, SalesChannel
I've assumed, since your code was already getting cat_click somewhere, that it was already defined to splunk, so it could be used in the search.
You'll need to modify the SalesChannel case statement to your actual channel names, assuming that's how you get the channel.
Check my answer in your other post
https://answers.splunk.com/answers/494439/appendcols-missing-data-in-column.html#answer-494537
What is the relationship between columns of current output and expected output? Can't make any as the sample values also doesn't match.