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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...