Splunk Search

Stats Percentage

c799651
Explorer

Hi,

I have a data source that lists phone calls.

Each call record will list a set of values, in defined fields

The key information I’m interesting in, is a field called Phone_Number

And a field called Result.

There are about 6 valid values for Result which I wish to remap as follows

A,B = Good_Result

C,D,E=Bad_Result

 

 

I want to list the Phone_Numbers based on a count (by percentage of Bad calls)

 

Phone_Number                % Bad_Result

800123455                           80

800444666                           77

800781711                           23

800372728                           4

800312711                           2

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Have a look at this 'run anywhere' example. You can paste this into the search window and run it. It takes your expected bad result % and phone numbers and creates some random data based on your description of ABCDE results.

The last 4 lines of this query are the ones that take the Phone_Number and result data and calculate the percentage. All lines before that are just creating the data set. If you run the query without the 4 lines you can see what the data looks like.

| makeresults
| eval _raw="Phone_Number                Threshold
800123455                           80
800444666                           77
800781711                           23
800372728                           4
800312711                           2"
| multikv forceheader=1
| eval n=mvrange(1,50 + (random() % 100))
| fields - _raw _time linecount
| mvexpand n
| eval Result=if(random() % 100 > Threshold, mvindex(split("A,B",","), random() % 1), mvindex(split("C,D,E",","), random() % 2))
| fields - n Threshold
| eval Result_Type=case(Result IN ("A","B"), 1, Result IN ("C","D", "E"), 0)
| stats sum(eval(if(Result_Type=1, 1, 0))) as "Good Results" sum(eval(if(Result_Type=0, 1, 0))) as "Bad Results" count as "Total Calls" by Phone_Number
| eval "Bad Result %" =  round('Bad Results' / 'Total Calls' * 100)
| table Phone_Number "Total Calls" "Bad Results" "Bad Result %"

Hope this helps

 

View solution in original post

c799651
Explorer

Thank you. You are indeed a champion!

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Have a look at this 'run anywhere' example. You can paste this into the search window and run it. It takes your expected bad result % and phone numbers and creates some random data based on your description of ABCDE results.

The last 4 lines of this query are the ones that take the Phone_Number and result data and calculate the percentage. All lines before that are just creating the data set. If you run the query without the 4 lines you can see what the data looks like.

| makeresults
| eval _raw="Phone_Number                Threshold
800123455                           80
800444666                           77
800781711                           23
800372728                           4
800312711                           2"
| multikv forceheader=1
| eval n=mvrange(1,50 + (random() % 100))
| fields - _raw _time linecount
| mvexpand n
| eval Result=if(random() % 100 > Threshold, mvindex(split("A,B",","), random() % 1), mvindex(split("C,D,E",","), random() % 2))
| fields - n Threshold
| eval Result_Type=case(Result IN ("A","B"), 1, Result IN ("C","D", "E"), 0)
| stats sum(eval(if(Result_Type=1, 1, 0))) as "Good Results" sum(eval(if(Result_Type=0, 1, 0))) as "Bad Results" count as "Total Calls" by Phone_Number
| eval "Bad Result %" =  round('Bad Results' / 'Total Calls' * 100)
| table Phone_Number "Total Calls" "Bad Results" "Bad Result %"

Hope this helps

 

bowesmana
SplunkTrust
SplunkTrust

and because in Splunk you can do the same thing many ways, you can replace the last 3 lines with these two, which gives you the same sort of results.

| top Result_Type by Phone_Number
| where Result_Type=0

 

Get Updates on the Splunk Community!

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 ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...