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
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
Thank you. You are indeed a champion!
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
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