Splunk Search

How do you group results by location and percentage the results?

davidoking
Explorer

I have a search result that is tabled, and now, I am trying to find a result by percentage of the location they visit most, A B or C per person.
Noting that each location A, B or C ,have sub locations designated by numbers, e.g. A1234, A2345, A4567 etc.

I'm trying to find the total % based on primary designation e.g. A*, B* or C*.

I can stats sum the events by sub location, but I can't seem to work out how to group the primary designations as a %.

Example

ID     Patron  Location 
123  Bob       A1234
234  Shirley  A1234
123  Bob      B4264
123  Bob      C7894
234 Shirley  B4356
123 Bob       A4561

hopeful result

                          Location
ID     Patron     A       B    C
123  Bob          X%   X%  X%
234  Shirley    Y%    Y%  Y%

Thoughts?

Thank you all in advance for your help.

0 Karma

davidoking
Explorer

@renjith.nair

Thank you for that. looks great unfortunately it doesn't seem work with the rest of my search. I'll have a look and see if i can modify it a bit. I'm getting a search conflict error.

Thanks again

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@davidoking ,

Can you try this and see if it matches your requirements?

| makeresults | eval ID="123,234,123,123,234,123"| makemv ID delim="," | mvexpand ID
| appendcols [| makeresults |  eval Patron="Bob,Shirley,Bob,Bob,Shirley,Bob"| makemv Patron delim=","|  mvexpand Patron]
| appendcols [| makeresults| eval Location="A1234,A1234,B4264,C7894,B4356,A4561"|  makemv Location delim=","| mvexpand Location]
| rename COMMENT as "---All above is for dummy data generation---" 
| eval PrimeLocation=substr(Location,0,1)| eventstats count by Patron,PrimeLocation
| eventstats sum(count) as total| eval perc=round((count/total)*100,2) 
| stats values(eval(if(PrimeLocation=="A",perc,null()))) as A ,values(eval(if(PrimeLocation=="B",perc,null()))) as B,values(eval(if(PrimeLocation=="C",perc,null()))) as C by ID,Patron
Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

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