Splunk Search

Help recreating report from different souce

kmccowen
Path Finder

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
Tags (2)
0 Karma

DalJeanis
Legend

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.

0 Karma

somesoni2
Revered Legend

somesoni2
Revered Legend

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...