topic Re: Help recreating report from different souce in Splunk Search

Help recreating report from different souce

kmccowen — Wed, 01 Feb 2017 16:23:43 GMT

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

Re: Help recreating report from different souce

somesoni2 — Wed, 01 Feb 2017 16:36:10 GMT

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

Re: Help recreating report from different souce

somesoni2 — Wed, 01 Feb 2017 16:55:15 GMT

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

Re: Help recreating report from different souce

DalJeanis — Wed, 01 Feb 2017 17:10:17 GMT

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.