Stats Percentage

[c799651]

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

[c799651]

Thank you. You are indeed a champion!

[bowesmana · Accepted solution]

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]

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