Help recreating report from different souce

[kmccowen]

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

[somesoni2]

What is the relationship between columns of current output and expected output? Can't make any as the sample values also doesn't match.

[somesoni2]

Check my answer in your other post
https://answers.splunk.com/answers/494439/appendcols-missing-data-in-column.html#answer-494537

[DalJeanis]

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.