Can a script replace me? – Duncan Dexter

Are you tired of spending hours analyzing CDRs to troubleshoot call issues? Have you ever wished there was a way to automate the process and make your job easier? Well, in this blog post, I will explore whether a script can replace me, a Tier 1 SIP engineer with years of experience in CDR analysis.

As many of you may know, CDRs are a critical tool in resolving trouble tickets in the telecom industry. However, with recent layoffs and the slow replacement of techs, finding someone with the expertise to analyze CDRs has become increasingly difficult. And to make matters worse, our company is moving to a paid support model, leaving some customers without the resources to diagnose call issues.

So, with these challenges in mind, I began to wonder if I could write a script that could diagnose a CDR for customers left without a support team, while also learning Python in the process. Of course, there are regulations to work around, and my company isn’t going to allow me to start making API calls to the circuit out of the blue without a proof of concept.

Our objective is clear:
Build a script that can be fed a CDR and determine the root cause of the call failure. Let’s see if we can make this happen.

Step 1: Pulling a CDR in from Salesforce

So Salesforce is going to be our imitation “Circuit Switch” in the real world. This would be something like a Ribbon 7K or a Taqua box but for our proof of concept, Salesforce should work nicely. Now I have worked with Salesforce’s API before so I know we are going to need to make a few API calls to pull in our CDR.

A Bearer token that will allow us to make our other API calls
The case ID from the case number
Finally, the CDR from the latest case comment

Bearer Token

This is going to be the only time in the script where we will be sending a POST instead of a GET. We will also be hard-coding a username and password which isn’t the smartest idea but this script is only running on my local machine and I have an idea that I will implement later on to remove this hard coding. So for now, we are going to keep it hard coded but we will split them out separately from our URL into their own variables so that we can make changes to them later on with ease.

Now in all honesty I did not write this code from scratch, I went out and used postman instead because it just simplifies the process, is easier to test, and will spit out the necessary code for you once everything is working. I did then make some changes ie. pulling the username and password into their own variables so that I can manipulate them later.

All this to say we now have our Bearer Token assigned to the variables token and can start making GET calls to Salesforce.

    #Gets bearer token
    password = "****"
    username = "*****"
    tokenurl = "https://****prod.my.salesforce.com/services/oauth2/token?grant_type=password&client_id=***&client_secret=***&username=" + username + "&password=" + password

    payload={}
    headers = {
      'Cookie': 'BrowserId=_7xN5bepEe2iECO8hFqqHQ; CookieConsentPolicy=0:1; LSKey-c$CookieConsentPolicy=0:1'
    }

    gettoken = requests.request("POST", tokenurl, headers=headers, data=payload)
    tokenresponse = json.loads(gettoken.text)
    token = tokenresponse["access_token"]

Case ID

So for whatever reason salesforces API does not allow us to pull case comments using a case number you have to have the case ID. Now I could easily just skip this step and input the case ID directly into the script but I want other techs on my team to be able to use and test this script for me and the case number is just much simpler for them to find so we are going to keep this step in.

So now we head over to salesforce’s workbench tool to put together our SOQL Query where we tell it we want to SELECT the Id FROM the Case WHERE the CaseNumber = whatever our case number is. Fairly straightforward and it outputs for us the exact string we need to include on our API call. We then head back over to Postman to convert this query into a working API, not forgetting to include our variable ‘token’ in the header.

Now you may have noticed that I have split out the case into its own variable again, this is because I have a future GUI in mind, and building it will be easier with these variables already separated.

But we have done it, now using the token we pulled from the previous step and our case number our variable ‘caseid’ is now set and we can start to pull the CDR from the case we want.

#Gets Case ID from Case Number
    case = "'01020012'"
    idurl = "https://intelepeerprod.my.salesforce.com//services/data/v45.0/query/?q=SELECT Id FROM Case WHERE CaseNumber =" + case

    payload={}
    headers = {
      'Authorization': 'Bearer ' + token,
      'Cookie': 'BrowserId=_7xN5bepEe2iECO8hFqqHQ; CookieConsentPolicy=0:1; LSKey-c$CookieConsentPolicy=0:1'
    }

    getid = requests.request("GET", idurl, headers=headers, data=payload)
    idresponse = json.loads(getid.text)
    caseid = idresponse['records'][0]['Id']

Pulling the CDR

Alright so we have the Bearer Token and the Case ID so now it’s time to pull our CDR. This is just as straightforward as pulling the Case ID was, especially now that we have all the bits of information we need to actually make the API call.

We are going to head back over to our trusty workbench tool and create a new query. This time we are going to tell it that we want to SELECT the CommentBody FROM the CaseComment WHERE the ParentID = the ‘caseid’ variable we created in the step before. Again we feed all of this back into Postman to get our nice bit of code and make the necessary edits.

SUCCESS! We have now pulled in the CDR from Salesforce thus “imitating” an API call to the circuit and assigned to the ‘calldetails’ variable.

    #Gets CDR

    caseurl = "https://intelepeerprod.my.salesforce.com//services/data/v45.0/query/?q=SELECT CommentBody FROM CaseComment WHERE ParentId ='" + caseid + "'"

    payload={}
    headers = {
      'Authorization': 'Bearer ' + token,
      'Cookie': 'BrowserId=_7xN5bepEe2iECO8hFqqHQ; CookieConsentPolicy=0:1; LSKey-c$CookieConsentPolicy=0:1'
    }

    getcasecomment = requests.request("GET", caseurl, headers=headers, data=payload)
    caseresponse = json.loads(getcasecomment.text)
    calldetails = caseresponse['records'][0]['CommentBody']

Step 2: Creating our dictionaries

Now for the hard part…at least for me as we are now starting to do some actual python scripting which is a pool I haven’t jumped into before. We are now going to take that CDR and map it to a dictionary so that we can actually use it in our script. We are also going to map out our response codes so our script knows what to look for.

Mapping the ResponseCodes

So a responsecode is specific to a Taqua CDR so we will have to do another map for Sonus later but this will at least get our proof of concept off the ground. We have a .txt file that includes all of the Response Codes and the reason for these failures so we are going to map them to a dictionary.

Our file is laid out pretty nicely with a response code and reason on each line so the code is pretty straightforward. Open the .txt file and convert it to a list where each line is its own list split into individual words. Ends up looking like this: [‘1000’, ‘Normal’, ‘Call’, ‘Clearing’]

We then take those lists and convert them into a dictionary where the first item in the list is the key and using some json we join the rest of the list together as the value.

We now have our Response Code dictionary.

   
 #Mapping our response codes to a dictionary

    trc = {}
    with open("responsecodes.txt") as f:
        for line in f:
            lis = line.split()
            key = lis[0]
            val = ' '.join(lis[1:])
            trc[key] = val

Mapping the CDR

Now to mapping the CDR, it’s a little more complicated than the ResponseCodes file but it’s going to follow the same basic concept just with a few more rules.

We take the ‘calldetails’ variable and split it by \n which is each line break we then take each line and split it again turning each broken line into a list. So they look like this: [‘CalledCc’, ‘1’] looks familiar right? So why don’t we just use the same script as before that will work right?

Unfortunately No, unlike the ResponseCodes not every part of the CDR has a key and a value and when the script hits one of these lines everything becomes a mess. So we have to give it a little bit of extra logic which tells the script to look for more than one result in the list and if there is one that’s the key and value. If not it’s the key and the value is null.

Success! We now have our CDR in a dictionary.

    cdr = {}
    for line in calldetails.split('\n'):
      lis = line.split()
      try:
        cdr[lis[0]] = lis[1]
      except:
         cdr[lis[0]] = 'null'

Step 3: Teaching our script how to diagnose a CDR

We’re finally there! It’s time to give our script some logic and diagnose the CDR. So we are going to start off small and only really care if the issue is on the originating or terminating side if it’s an issue with the in-between we are just going to ignore it as a tech will need to be involved in order to resolve the issue anyways.

Diagnosing the issue

A key ingredient here is the DisconectParty in the CDR. It can have 1 of 3 values, 0: Call ended by the in-between, 1: Call ended by the originating side, or 2: Call ended by the terminating side.

All this really affects is how our response begins on the output. So we first check the DisconnectParty and then as long as it is either 1 or 2 we continue to pull the ReasonCode from the CDR.

We then take that ReasonCode and compare it to our ReasonCode dictionary to determine what that reason code indicates. And then finally we print the result, pointing a finger in either direction either the originating side or the terminating side, and giving a brief explanation as to what caused this failure.

    #Takes our CDR dictionary and diagnoses the issue

    if cdr['DisconnectParty'] == '1':
        print("This call failed on the originating side due to " + trc[cdr["ReasonCode"]])
    if cdr['DisconnectParty'] == '2':
        print("This call failed on the terminating side due to " + trc[cdr["ReasonCode"]])
    else:
        print("Oops")

Conclusion

And there we have it, a relatively small script that can read and diagnose a Taqua CDR. Now I mentioned Ribbon 7ks earlier and the script to handle those CDRs would still need to be added but would follow a similar path as the ReasonCode dictionary. We would just need to add a bit of logic to look at the trunk name and from that we could determine if it was a 7k CDR or a Taqua CDR.

If it was a 7k CDR we would be looking at the SIPResponse field instead of the ResonseCodes and we would have to add a SIPResponse dictionary for it to compare to. Not at all difficult, but we have spent quite a bit of time already and I think we have enough here for a proof of concept.

Final Thought

Well, we did it! and I feel like we have shown enough here as a proof of concept to show to the higher-ups and get permission to start making actual calls to the circuit instead of SalesForce. Now I do foresee a few issues when it comes to making those calls to the circuit, the main one being that you don’t normally get just one CDR back when you make the query so we would need to script out how we should look at multiple CDRs and which should carry the most weight. But we can leave that for another day. For now I am satisfied with what we have built.

Some other things I would like to do with this script:

Giving it the ability to pull CDRs from the boxes using a call example
Building it a Front End GUI maybe using something like Flask so it could be used through a browser. This would also aid us in eliminating the hard-coded username and password as they could be entered through text fields.
Look at doing some machine learning so it could be better at recognizing patterns and truly becoming a CDR AI

I hope you enjoyed following along on my first jump into the python pool and I am sure there will be more adventures into python to come.

Thanks,

Duncan

Edit

Hey everyone! I know I mentioned above that I was planning on adding a GUI into this script and I am definetly going to do that. I have been reading into Flask and how I can integrate this script into a web app and that will be coming soon. But in the mean time I made a couple of changes to the script to make it a little more secure and interactive.

So we made 3 changes to our script here, and because we already had split them out they weren’t too difficult at all. Our script now has input fields so it will now ask for the user to enter in their login details and also their case number.

So we make the change for Username and Case number the same way just a simple input(). But for the password we wanted to add in a little extra security so the password isnt read out in the terminal so we used getpass(). I hope you guys enjoyed this little edit and be on the lookout for my Flask Project!

from getpass import getpass

username = input("What is your salesforce Username?\n")
password = getpass("What is your password?\n")
case = input("What is your case number?\n")