Category Archives: Avaya

Goodbye to my Avaya Environment :(

23 Replies

I’m very sad to say farewell to my Avaya environment.

In 1999, I left EarthLink Network and made the leap into telecommunications consulting. It was very scary – my wife and I wanted to buy a house and start a family. It was a risky move but I’m glad I did. I had a rewarding career and learned a lot.

In 2010, I began consulting for a financial company in Century City, CA. They had several small offices with an Avaya system in each. I was able to get the gig because I had worked with Avaya systems in the past. Over the next seven years, I worked with them to build an amazing Avaya network.

In 2016, they began to explore Cisco to replace their aging videoconferencing network. It was a compelling return on investment calculation. Due to the complexity of the video equipment, this client was using a video bridging service for 100% of their video calls, which resulted in huge operating costs. Naturally, Cisco was able to convince them to install simple-to-use Cisco video gear and they could eliminate the bridging usage fees.

As their voice consultant, I was not involved in the sales process. But from what I saw, I was amazed. As you read this in 2020 or later, you may not appreciate the genius of the Cisco’s sales process in 2017. They simply gave my client a VMWare server and a bunch of videoconferencing equipment. “Just assign some IP addresses and plug this server into your network”, they said. And amazingly, they were right.

The VMWare server had all the virtual machines necessary to have a working Cisco Unified Communications Manager (CUCM) network. A small tweak to DHCP options, and all the loaner video endpoints worked fine!

Say what you will about Cisco, and I was no fan at the time, but that was something Avaya could not have done. Of course it doesn’t hurt that the networking equipment and edge routers were already Cisco. It was a pretty easy sale – the demonstration basically sold itself. My client agreed to replace the videoconferencing network and hired a CUCM engineer to oversee the project.

I could see the writing on the wall. It wouldn’t be long before Cisco convinced them to replace the phones as well. Since the video infrastructure would also handle voice, it would be a no-brainer to add Cisco telephones and shut down all of the Avaya systems. And there was a lot of Avaya gear – and the maintenance and support costs to go with it. And, to be honest, I was the only guy who knew anything about it. Nobody said anything, but they were a bit exposed if anything happened to me, their only voice guy. I knew my days were numbered. I knew that, five minutes after the Avaya was shut down, I’d be politely asked to reduce my billable hours to zero.

Around this time, I got a call from a buddy I met while consulting. He had moved to a large healthcare organization and said there was an opportunity for a voice guy to handle the migration from PRIs to SIP. It sounded like a lot of fun, but it was a full-time job. Many times during my consulting career, I had been asked if I was interested in full-time work. But I always politely and humbly declined. I loved consulting. But at this point I was pushing 50 years old and going into “sales mode” and hitting the Los Angeles streets wasn’t as exciting as it used to be. I loved this client. I loved the people and the network. I like to think that they loved me. But I didn’t know Cisco very well. And they were filling up their network team with Cisco engineers. Alas, my future there as a consultant was doomed.

So, for the first time in 17 years of consulting, the idea of full-time work sounded appealing. Ironically, the healthcare organization was all Cisco. It was a long shot, but I went ahead and applied. It turns out that my friend’s recommendation carried a lot of weight. Also, my potential boss and I hit it off immediately. He was an old-school telephone tech and used to manage the outside cable plant for one of the Bell companies. My hero! And I knew SIP and AT&T IPFlex quite well. So even though I didn’t know the Cisco SBCs specifically, I got the job!

The new job was work-from-home. My client was 60-90 minutes away in Los Angeles traffic. I suppose I could have pursued a full-time job at my client, but saving 2.5 hours of commute time per day was a huge factor.

Unfortunately, my client wasn’t happy with the news, and they seemed genuinely surprised that my crystal ball showed no future there after the Avaya was disconnected. But I had developed pretty good prediction skills and was confident in my decision. I helped with the transition to a new Avaya contractor (several times, as it turned out), and gracefully exited the consulting business.

However, I knew everyone at that place and had all the credentials necessary to remotely support their Avaya network. I was always friendly, as available as possible, and never hoarded information about their systems. For the last three years, I’ve been trying to write up some of my favorite things I did in that network – things like

  • Automatically building labels on 9630 phones
  • Config extraction to a database
  • Automatic station configuration change and history reports (like, what was the extension we assigned that intern last summer?)
  • CDR processing
  • Tons of Perl scripting
  • Asterisk integration
  • Help Desk routing based upon an Active Directory attribute (this was done in Asterisk and then passed back to the CM)
  • Audio recording/playback for conference room testing (again, through Asterisk)

And through it all, I’ve always been able to connect to the Avaya network and look at the existing configuration. I’ve been able to capture screenshots to answer your questions here on this blog. I’ve been able to assist my client with the transition to Cisco by making small routing changes here and there to keep my skills sharp.

Alas, three days ago, on June 24, 2020, my SIP phone sitting here on my desk in my home office went dead. Actually, it just said “Acquiring Service”, which means my client has finally shut off the Session Border Controllers and presumably, the Communication Manager. While I did not expect the system to last this long into 2020, it is still an emotional blow to me.

My first PBX was a ROLM 8000. I cared deeply for that switch from 1990 through 1995. When it was replaced with a Siemens HiCom 300 in 1997, I brought it home to my condo (and wonderfully understanding wife) where I continued to love it as one might love a classic car in their garage. It moved into our garage when we bought a house in 2000. I finally disposed of it in 2010 when we converted our garage to an apartment for my father. That was difficult.

I’m surprised to find myself mourning the loss of this Avaya network in much the same way. I tended that network lovingly like a gardener might take care of a large estate. And now it has been deleted. Decommissioned. It happens all the time. But some of my soul went into that network and it makes me very sad.

I am also sad that I have no more Avaya network for you, either. I cannot simply “try it” when you have a question. And my Avaya skills have atrophied – I find myself looking up even the simplest commands that used to spring from my fingertips from muscle memory.

So begins a new chapter for “Roger the Phone Guy”. No longer Avaya, I will likely lose many of you. I have entered the world of Cisco. But perhaps many of you are also making this same transition. To be honest, it was difficult wrapping by head around Cisco. But I am teaching my new Cisco network some “stupid phone tricks” that I look forward to sharing with you.

Anyway, thank you for all of your attention and kind words for the last several years. I hope to share some amazing things about Cisco and perhaps even help to introduce some of you Avaya-heads to the drastically different topology and vocabulary of Cisco.

With affection,

Roger

Python scripting tool to manage Avaya Communication Manager

13 Replies

This could be it!

So many of us hunger for a decent tool to automate getting useful information out of the Avaya Communication Manager! I have written many scripts using PHP and Perl, but they all have various issues.

It looks like an engineer and python programmer has created a script that uses OSSI, which is a low-level protocol to talk to the CMs. He probably had to do a lot of brilliant hacking to get this to work. I wish I had an Avaya to play with! Alas, my world is all Cisco now (which also has surprisingly difficult challenges with command-line automation).

Anyway, I wanted to pass along the link to this person’s project. I saw it on LinkedIn here: https://www.linkedin.com/feed/update/urn:li:activity:6648167619008483328

And here is the project itself: https://pypi.org/project/ossi-tool/

As I post this, we are four weeks into the COVID-19 “shelter in place” order. Many of us telephone engineers have been involved in setting up remote workers and probably dealing with capacity and congestion. This Python tool should help with that. I can imagine setting up a script that:

  1. Watches for a new “mobility” flag in Active Directory
  2. If set, pulls the extension and mobile number from Active Directory
  3. Launches a script to build the EC500 record in CM with the right mobile number
  4. Lists all EC500 and watches for changes between AD and CM
  5. Removes the EC500 mapping when the flag is cleared

Also, we could all use more visibility into trunk utilization. This Python script should allow us to monitor trunk usage as well.

Happy Scripting All! Please let me know how it works for you. And send feedback and thanks to https://www.linkedin.com/in/janos-tarjanyi-a4201226/ for this awesome tool!

Roger

 

Sneak Preview – how to automatically label Avaya IP phones

6 Replies

I’ve been looking forward to writing up this tutorial on automatically labeling Avaya IP phones.

This is a huge part of my success as a telephone consultant. I spent many years struggling with Avaya labels and I managed to write this automatic labeling script. It’s a combination of Perl, PHP, and Apache directives. I used it thousands of times over the last four years or so.

This is a sneak preview of what’s to come. I’m really excited to share it with you.

Please let me know what you think! Roger

 

 

SigMa Cookbook (for Avaya SBCE / Sipera SBCs)

25 Replies

Hello Everyone! I’m excited to say I got my hands on an unofficial “SigMa Cookbook” from a buddy of mine at Avaya. This was put together by some engineers at Sipera. This was passed around within Avaya and maybe some customers, but I don’t think it was officially released.

These recipes were put together in 2010 and 2011 for “generic release 4.0.4 Q102 or later”. I do not think there’s official support for this. It was an internal document and was always sent with various warnings about “your mileage will vary”. So you have been warned! If you find any errors and clean up some of these, please let me know and I’ll fix them here.

I have formatted it as HTML for easy searching. I do not know if the engineers want me to include their names. If any of you are from Avaya or Sipera and want attribution for these recipes, please let me know and I’m happy to do it!

And also let me know if you have any good examples we should add. There’s not much of a user community for these SBCs so let’s help each other!

Example 1: P-Asserted-Identity (PAI) Header Manipulation

Use Case:

The P-Asserted-Identity header field can be used to present the identity of the originator of a request within a trusted network. Since the “From” header field is populated by the originating User-Agent it may not contain the actual identity. It usually is established by means of authentication between the originating User-Agent and its outgoing proxy. The outgoing proxy then adds a P-Asserted-Identity header field to assert the identity of the originator to other proxies.

  1. If there is no P-Asserted-Identity header field present, a proxy MAY add one containing at most one SIP or SIPS URI, and at most one tel URL.
  2. If the proxy received the message from an element that it does NOT trust and if there is a P-Asserted-Identity header present, the proxy MUST replace the SIP URI or remove it.

Script:

within session "ALL"  {
    /* 
        Looks into all the messages 
        Message should be a request (act on request) and the messages coming towards the Uc-Sec should be considered,
        i.e. the destination of the message should be Uc-Sec ({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND").
        The actions are invoked as soon as the message comes from the wire ({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK") 
    */

    act on request where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" {
        /*
            Checks if the first P-Asserted-Identity header is present/exists in the message. 
            Each header is represented as {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["<Header-name>"][<Header position>].
            For headers such as From and Contact, the Header Position is always 1.
            For headers like Via and P-Asserted-Identity, the positions can range from 1 to n
        */

        if(exists({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["p-asserted-identity"][1])) then {
            remove({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["p-asserted-identity"][1]); //Remove the header
        } else {
            /*
                If the P-Asserted-Identity header is not found in the message
                Add a SIP and a telephone URI.
            */
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["p-asserted-identity"][1] = "12345<sip:12345@192.168.150.150>";
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["p-asserted-identity"][2] = "tel:+14085554000";
        }
    }
}

Additional Description:

The script looks into each message that comes in (since the script acts on all sessions) and checks if

  1. It is a request message
  2. The message is inbound towards the Uc-Sec

If both the above conditions are successful, as soon as the message comes from the wire, (and after the basic sanity checks and DoS checks are performed on the message), the action is performed.  The script checks to see if a “P-Asserted-Identity” header exists.  If it does, it removes it, else it adds the header.

Limitations: 

If you would like to remove all the P-Asserted-Identity headers, it is assumed that you know the maximum number of headers that would be present in the messages.  You do not need to know the exact number of headers that come in because if you perform an operation on a header that does not exist, it is simply ignored.

Note:

If {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS[“<Header-Name>”][<Header Position>] is already present, then the operation

{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["<Header-Name>"][<Header Position>] = <Val>

will modify the header. If it is not present in the message,

{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["<Header-Name>"][<Header Position>] = <Val>

will add the header to the message.

Example 2: Addition of a media attribute in SDP

Use Case:

It might be required to add/modify SDP attributes or connection parameters for interoperability.

Script:

within session "INVITE" {
    /*
        Looks into messages in the INVITE session only (It includes all messages in the INVITE dialog)
    */
    
    act on request where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" {
        /*
            The "m=" field in SDP contains information about the type of media session. 
            It includes the format-list parameter for specifying the codecs.
            Assuming that the message comes in with 2 codecs, we add a third codec as 101
        */

        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}SDP[1]["s"]["m"][1].FORMATS[3]="101";    

        /*
            The "a=" field contains attributes to provide more information on the codecs.
            Assuming that the message does not have any fmtp attribute, we add the first one as 101 0-16
        */

        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}SDP[1]["s"]["m"][1].ATTRIBUTES["fmtp"][1]="101 0-16";    
    }
}

Additional Description:

The script looks into all the messages of the “INVITE” session.  A session is defined as a SIP dialog and has the same lifetime as that of a dialog.  A new format-type and an attribute is added corresponding to fmtp.

Limitations:

We would need to know the number of codecs (number of formats in format-list parameter  and attributes), or we would end up replacing an existing format-type.

Example 3: Changing Privacy Header Value

Use Case:

Restricting Calling Party Presentation.

Script:

within session "ALL" {
    act on message where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" {
        /*
            Checks if the privacy header value matches with the regular expression given (e.g. "none").
            If it matches, then the privacy header value is changed to "id"
        */

        if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Privacy"][1] = "none") then {
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Privacy"][1] = "id";
        }
    }
}

Example 4 – Replace “From” header for a set of users

Use Case:

In an organization, there could be several phones used by the employees and each of them might have a unique “From URI” associated with them.  It may be required that all calls going out from the organization have the same “From URI”.  For this purpose, the following script can be used.

Script:

within session "INVITE" {     
    /* 
        For users whose Uri begins with the prefix 10, when the message comes towards the Uc-Sec, 
        the Uri is changed to “9000”<sip:9000@domain>. So, when the receiver answers the call, 
        the From is 9000.
    */

    act on request where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" {
        /*
            A Uri can be represented as "<diplay_name>"<scheme>:<user>@<host>:<port>, 
            eg: "roger"<sip:roger@rogerthephoneguy.com:5060>. 
            URI.USER extracts the user portion of the URI.  
            regex_match tries to match the string against the regular expression. 
            It is of the form <string>.regex_match("<regular expression>"). In this example,
            it is checked if the USER portion in the "From" Header starts with the prefix 10 
        */

        if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].URI.USER.regex_match("^10")) then {
            /*
                The uri and display name of the actual user is stored in temporary variables
            */

            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}OriginalFromUri = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].URI.USER;
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}OriginalFromName = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].DISPLAY_NAME;        
    
            /* 
                The display name and uri is changed to the new values.
            */

            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].DISPLAY_NAME = "9000";
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].URI.USER = "9000";          
        }
    }

    /*
        When the response comes back, we need to change the URI USER and DISPLAY NAME to the actual user. 
        So,before the message is sent out to the wire from the Uc-Sec, it is checked if the URI.USER is 9000.
        If yes, then change it back to the original user's details. 

        Message should be a response (act on response) and the messages going out 
        from the Uc-Sec should be considered ({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND"). The actions are 
        invoked before the message goes out ({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="BEFORE_NETWORK") 
    */

    act on response where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="OUTBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="BEFORE_NETWORK" {
        /*
            Check if the user portion of the From URI is 9000
        */

        if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].URI.USER = "9000") then {
            /*
                Change the URI.USER and display name to the original user’s details, 
                which are saved in the temporary variables
            */

            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].URI.USER = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}OriginalFromUri;
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].DISPLAY_NAME = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}OriginalFromName;
        }
    }
}

Additional Description:

The above example shows how to modify a message (request) on its way out and also modify a message (response) when it comes in.

Limitations:

The example illustrates the use of regex_match.  The regular expression provided within the parentheses, i.e. regex_match(<regular expression>), can be any valid Perl regular expression.  However, the “$” symbol (which typically anchors the match to the end of the pattern) cannot be used in the regular expression.

Example 5: Editing the “Allow” Header

Use Case:

The Allow header is used to indicate the methods supported by the user agent, e.g. “Allow: INVITE, ACK, BYE, INFO, OPTIONS, CANCEL”.  The OPTIONS method is used to query a user agent or server about its capabilities and discover its current availability. The response to the request lists the capabilities of the user agent or server.  This may not be desired (probably due to security reasons).  In this case, the Uc-Sec can strip the OPTIONS method from the Allow header before sending out the message.

Script:

within session "INVITE" {

    /*
        Look for INVITE messages only. This is specified with the extra condition 
        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}METHOD="INVITE" in the where clause
    */

    act on request where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}METHOD="INVITE" { 

        /*
            There could be (1) multiple methods in Allow or (2) OPTIONS could be the only method in Allow. 
            If there are multiple methods in Allow, OPTIONS could be (1) in 
            the beginning (2) in the middle, or (3) the end 
        */

        /*
            If OPTIONS is in the middle/end in Allow, it would be of the form 
            Allow:<Methods>,OPTIONS,<More methods> or Allow:<Methods>,OPTIONS. 
            So, we try to match Allow against the regex ",OPTIONS"
        */

        /*
            Note from RogerThePhoneGuy - this example had ,OPTIONS in the comment
            but comma [space] OPTIONS in the regex below. You might want to check
            traces to see if there's a space between the comma and the word OPTIONS
            and make sure you match it in this script!
        */

        if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Allow"][1].regex_match(",OPTIONS")) then {
            
            /*
                <string1>regex_replace("<regex1>","<string2>") looks for 
                regex1(regular expression) in string1 and replaces it with 
                string2(plain string). Here we replace ",OPTIONS" with
                an empty string, indirectly removing ",OPTIONS"
            */

            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Allow"][1].regex_replace(",OPTIONS",""); 
            /* 
                Note from Roger: The original example had a space between the comma and the word OPTIONS
                but I think that is incorrect so I removed it for this example.
                I do not have an SBC at the moment though - please check your traces!
            /*
        } else {
            /*
                Nested if-else
            */

            /*
                If OPTIONS is in the beginning in Allow, it would be of the form 
                Allow: OPTIONS,<More methods>. So, we try to match Allow 
                against the regex "OPTIONS,"
            */

            if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Allow"][1].regex_match(" OPTIONS,")) then {
                /*
                    We replace OPTIONS, with an empty string, indirectly removing " OPTIONS,"
                */

                {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Allow"][1].regex_replace(" OPTIONS,", "");
            } else {
                /*
                    If OPTIONS is the only method in Allow, it would be of the form 
                    Allow: OPTIONS. So, we try to match Allow against the regex " OPTIONS"
                */

                if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Allow"][1].regex_match(" OPTIONS")) then {

                    /*
                        Since OPTIONS is the only method in Allow, we remove the entire header
                    */

                    /*
                        remove({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["<Header-name>"][<Posn>] removes the header specified in 
                        <Header-name> in Position <Posn>. Here we remove the Allow header
                    */

                    remove({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Allow"][1]);
                }
            }
        }
    } 
}

Additional Description:

The above script can come in handy while operating on headers such as Allow, Supported, Content-Type etc, whose values can’t be extracted individually as compared to headers like “From”, “To”, or “Contact”.

Limitations:

The regular expression in regex_replace can’t have the $ symbol.

Example 6: Prefix Stripping

Use Case:

Phone numbers may come in with a prefix. Sometimes this needs to be stripped off before the call is routed. This is useful in scenarios where a call transfer is made and the number to which the call has to be transferred is entered with a prefix.

Script:

within session "INVITE" {
    /*
        Look for REFER messages only. This is specified with the extra 
        condition {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}METHOD="REFER" in the where clause
    */

    act on request where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}METHOD="REFER" {

        /*
            The User portion of the URI in the Refer-To header is checked to 
            see if it starts with the prefix 011. If it does, then it is 
            replaced with an empty string. If URI.USER does not 
            match the regex, then the action is ignored and the message is left intact.
        */

        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Refer-To"][1].URI.USER.regex_replace("^011","");
    }
}

Additional Description:

The messages which have the “Refer-To” method are checked if the URI contains a prefix.  If so, it is stripped before sending it out.

Limitations:

The regular expression in regex_replace can’t have the $ symbol.

Example 7: Diversion Header Addition and phone context removal

Use Case:

The user may want the option to add a diversion header or manipulate the extension field or change the URI parameters in the header. This example also illustrates combining two functions such as addition of a diversion header only to the INVITE and the removal of phone-context from all outbound messages.

Script:

within session "ALL" //Looks into all the messages {
	act on message where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="OUTBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="POST_ROUTING" {

		/* 
			Any message that is outbound from the UC-Sec is checked 
		*/

		/*
			Remove phone-context from Request Line
		*/

		remove({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Request_Line"][1].PARAMS["phone-context"]);

		/*
			Remove phone-context from the From header
		*/

		remove({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].PARAMS["phone-context"]);

		/*
			Remove phone-context from the To header
		*/

		remove({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["To"][1].PARAMS["phone-context"]); 

		if ({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}INITIAL_REQUEST = "true") then {

			/*
				Add a diversion header
			*/

			{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Diversion"][1] = "sip:3145552375@rogerthephoneguy.com";

		}
	}
}

Example 8: Redirection

Use Case:

The user may require to populate the  From header in the outbound INVITE message with the contents of the Diversion header in the incoming INVITE.

Script:

within session "ALL" {
	act on message where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="OUTBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="POST_ROUTING" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}METHOD="INVITE" {

		/*
			Store the contents of diversion header URI
		*/

		{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}Div = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["Diversion"][1].URI;

		/*
			Replace the contents of From header with stored value
		*/

		{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].URI  = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}Div;
	}
}

Example 9: Invite XML Body Manipulation

Use Case:

The user may require manipulation of the contents of the xml body present in the INVITE message.

Script:

within session "INVITE" {     
    act on request where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="OUTBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="POST_ROUTING" {

    	/*
    		Check the body for the regex value 
    	*/

        if({0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}BODY[1].regex_match("sip:2911@192.168.3.150")) then {
        	/* 
        		Replace the matching string with sip:anonymous@anonymous.com 
        	*/   
            {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}BODY[1].regex_replace("sip:2911@192.168.3.150","sip:anonymous@anonymous.com");   
        }
    }
}

Example 10: Switching From and To tags

Use Case:

The user may require to switch tags on the From and To in a particular message. The following example illustrates tag switching in the 200 OK message.

Script:

within session "REGISTER" {
    act on response where {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}DIRECTION="INBOUND" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}ENTRY_POINT="AFTER_NETWORK" and {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}RESP_CODE="200" {
        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}from = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].PARAMS["tag"];
        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["From"][1].PARAMS["tag"] = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["To"][1].PARAMS["tag"];
        {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}HEADERS["To"][1].PARAMS["tag"] = {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}from;
    }
}

Note from Roger: Does anyone know why you’d want to swap the from and to? This was the 10th example in the doc but I cannot think of why this would be useful.

How to translate four digits to an eleven digit PSTN call in Avaya Communication Manager

4 Replies

Hello all!

I was recently contacted by an old client who is moving offices. They have four-digit dialing between offices and use a lot of h.323 tie lines. However, they’re migrating to Cisco and during the transition, they just want to convert four digits to a PSTN call. Most of our telephone systems probably do this, but we rarely need to change it or add new ones, so I figured I’d summarize the process here.

Typically, four-digit translation is done based upon the first two digits (i.e. 100 numbers at-a-time). There are really only a few steps: Create the entry in the “dialplan analysis”, then the “uniform-dialplan”, then the “aar digit-conversion”. It is possible to perform some digit-conversion in the uniform dialplan, but I prefer to use the aar digit-conversion table due to consistency and flexibility. In this example, we add translation for the 83xx range to dial 914-555-83xx.

Step 1 – Verify the Dialplan is available for the first two digits

There should no entry for ‘83’ in the “dialplan analysis” table.

display dialplan analysis                                       Page   2 of  12
                             DIAL PLAN ANALYSIS TABLE
                                   Location: all            Percent Full: 11

    Dialed   Total  Call     Dialed   Total  Call     Dialed   Total  Call
    String   Length Type     String   Length Type     String   Length Type
   8           4   ext
   80          3   dac
   86          4   ext
   87          4   ext

Step 2 – change the dialplan analysis

Next, change the dialplan analysis adding an ‘83’ with a total length of 4 and a call-type of ext. We’re really going to use AAR to route this call, but that will be done through the uniform dialplan next. Confirm the entry:

display dialplan analysis                                       Page   2 of  12
                             DIAL PLAN ANALYSIS TABLE
                                   Location: all            Percent Full: 11

    Dialed   Total  Call     Dialed   Total  Call     Dialed   Total  Call
    String   Length Type     String   Length Type     String   Length Type
   8           4   ext
   80          3   dac
   83          4   ext
   86          4   ext
   87          4   ext

Step 3 – Check uniform dialplan

Next, check the uniform dialplan. It is possible to have entries in the uniform-dialplan that are not in the dialplan analysis table (as far as I can tell, this is a mistake because you’ll get wave-off). In my case, I don’t have 83s:

list uniform-dialplan                                                  Page   8

                      UNIFORM DIAL PLAN TABLE

Matching Pattern   Len   Del   Insert Digits   Net    Conv   Node Num

7977                4     4      7577          ext     n
7978                4     4      7578          ext     n
7983                4     4      7583          ext     n
7986                4     4      7586          ext     n
7988                4     4      6111          ext     n
8651                4     4      1111          ext     n
8662                4     4      1111          ext     n

Step 4 – OPTION 1 – Uniform dialplan only

Like everything else in a telephone system, there are multiple ways to do this. Let’s call this OPTION 1 where we only use the Uniform Dialplan (and not aar analysis)

Create an entry in the uniform dialplan for 83 with a length of 4 and no deleted digits. However, we are going to insert the digits here to route through the PSTN. In this case, insert 1914304. We set the Network to ‘ars’ so that this call goes through ARS routing. Note that we do not include a ‘9’ in the inserted digits. This is because we’re explicitly telling the PBX to use ARS routing (which is also what the ‘9’ does). Also note that you can only insert 10 digits here, which is not enough for some international translations. In that case, I use ars with digit translation. But this is fine for USA numbers. However, to be consistent between a mix of USA and international, I will typically use ARS digit-conversion as described in the next step OPTION 2.

list uniform-dialplan                                                  Page   8

                      UNIFORM DIAL PLAN TABLE

Matching Pattern   Len   Del   Insert Digits   Net    Conv   Node Num

7977                4     4      7577          ext     n
7978                4     4      7578          ext     n
7983                4     4      7583          ext     n
7986                4     4      7586          ext     n
7988                4     4      6111          ext     n
83                  4     0      1917304       ars     n
8651                4     4      1111          ext     n

[Note, the option above works for the North American numbering plan, but not for longer international strings, so I always use the next option]

Step 4 – OPTION 2 – AAR digit conversion

This is the most consistent (and my preferred) option, because if you have any translations to international numbers you’ll probably need to use this method. Create an entry in the uniform dialplan for 83 with a length of 4 and no deleted digits. However, we also want to perform digit conversion on this pattern so be sure to stroke the “Conv” column to ‘y’ so additional conversion can take place.

list uniform-dialplan                                                  Page   8

                      UNIFORM DIAL PLAN TABLE

Matching Pattern   Len   Del   Insert Digits   Net    Conv   Node Num

7977                4     4      7577          ext     n
7978                4     4      7578          ext     n
7983                4     4      7583          ext     n
7986                4     4      7586          ext     n
7988                4     4      6111          ext     n
83                  4     0                    ars     y
8651                4     4      1111          ext     n

Next, create an entry in the aar digit-conversion table for 83. Set the min/max to 4 and insert the new digits in the replacement string. Then set the Net to “ars” to tell the PBX to route the call out to the PSTN. There will be no more digit conversion, so leave Conv to ‘n’.

list aar digit-conversion

                      AAR DIGIT CONVERSION REPORT

                                Location: all

Matching Pattern     Min   Max   Del   Replacement String   Net  Conv ANI Req

0                    1     28    0                          ars   y
1                    4     28    0                          ars   y
5982                 4     4     4     12155550542          ars   n
7317                 4     4     0     555929               aar   y
7501                 4     4     4     13105555425          ars   n
83                   4     4     0     1917555              ars   n
x11                  3     3     0                          ars   y

And that’s it! You should be able to dial 83xx and it will use ARS routing to the translated value of 191755583xx. If you have any trouble at all, you can refer to my flowchart of Avaya routing to help troubleshoot this.

Happy routing, everyone!

Roger

 

How to Parse Avaya Communication Manager data to a database for analysis – Part 3

13 Replies

Hello all!

This is a continuation of our discussion about pushing Avaya Communication Manager data into a database so you can analyze the results.

Here is part 1 where we connect to the Communication Manager through a perl script

Here is part 2 where we capture a bunch of data and parse it into useful tables

In this part, we push the data into a MySql database. The first step is to create the database. I typically use my own Linux workstation. You can ask your VM team for one. They take almost no resources and they’re open-source, so there are no licensing concerns. Therefore I typically have complete control of the repositories, database, and Apache instance. If you do not have the root password to your Linux workstation, then you might need help from your team with some of these steps.

We need to start with some assumptions here. So let’s assume you have followed parts 1 and 2 of this series and you have MySQL installed, okay?

Step 1 – create the database

Log into MySQL. These commands will create the database and user for this project. This user is called ‘scraper’ with a password of ‘crafty1876’

mysql> create database avaya;
Query OK, 1 row affected (0.01 sec)

mysql> use avaya;
Database changed

mysql>create user scraper@localhost identified by 'crafty1876';
Query OK, 0 rows affected (0.04 sec)

mysql>grant all on avaya.* to scraper@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>exit

We exit the database and log back in with our new user. This ensures that the user works and has full access to the avaya database

[roger ~]# mysql -hlocalhost -uscraper -pcrafty1876 avaya

Now let’s create the tables we need. I always have a “drop table if exists” statement so I can tweak the table as much as I want and re-create it. This creates a keyvalues table and a display name table.

mysql> drop table if exists avaya_keyvalues;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql]> create table avaya_keyvalues (
 id integer auto_increment primary key,
 active integer,
 key_name varchar(256),
 key_value varchar(256),
 record_status integer,
 created_on datetime,
 updated_on datetime,
 deactivated_on datetime
);
Query OK, 0 rows affected (0.11 sec)

mysql>drop table if exists avaya_display_name;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql>create table avaya_display_name (
 station varchar(12) NOT NULL,
 name varchar(32),
 PRIMARY KEY (station)
);
Query OK, 0 rows affected (0.09 sec)

mysql>

Now we have a database and the empty tables we need. Since you have read part 1 and part 2 of this process, you should have a perl script generating your ‘keyvalue.txt’ file.

Step 2 – Push the keys and values into the database

We have a new perl script that takes the file of keys and values and puts it into the database. You can click here for a download the script. Just rename it to .pl. Here it is so you can see it. It is called keys2db.pl:

#!/usr/bin/perl

#provided as-is by in 2017 by RogerThePhoneGuy.com 
#This script reads a file of "key/value" pairs, and logs them to a database. If the value was changed or deleted, it logs that too
#meant to be used with keyval.php, which shows this data
#assumes there's a local mysql connection

use strict;
use DBI;

my $db;

sub db_open { 
	my $dsn = "DBI:mysql:avaya:localhost";
	my $user = "scraper";
	my $pass = "crafty1876";
	while (!($db)) {
		$db = DBI->connect($dsn, $user, $pass);
		sleep 1;
	}
}

sub db_close { 
	if($db) {
		$db->disconnect();
		sleep 1;
	}
	undef($db);
}

sub generic_replace { 
	my ($table, $fields, $values) = (@_);
	my $sql = "replace into $table ($fields) values ($values)";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to replace with $sql\n";
	}
	$sth->finish();
}

sub generic_create { 
	my ($table, $fields, $values) = (@_);
	my $sql = "insert into $table ($fields) values ($values)";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to insert with $sql\n";
	}
	$sth->finish();
}

sub generic_read {
	my ($select, $table, $where) = (@_);
	my @datarow;
	my $sql = "select $select from $table where $where limit 1";
	my $sth = $db->prepare($sql);
	if($sth->execute()) {
		@datarow = $sth->fetchrow_array();
	} else {
		print "unable to execute $sql\n";
	}
	$sth->finish();
	return $datarow[0];
}

sub generic_update {
	my ($table, $set, $where) = (@_);
	my $sql = "update $table set $set where $where";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to update with $sql";
	}
	$sth->finish();
}

sub generic_delete {
	my ($table, $where) = (@_);
	my $sql = "delete from $table where $where";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to update with $sql";
	}
	$sth->finish();
}

db_open();

my $counter = 0;
generic_update("avaya_keyvalues","record_status=9","active=1");
open SCRAPED, $ARGV[0];
while (my $line = ) {
	chomp($line);

	my ($key,$value) = split(/\t/,$line);
	my $id = generic_read("id", "avaya_keyvalues","active=1 and key_name='$key'") + 0;
	my $currvalue = generic_read("key_value", "avaya_keyvalues","active=1 and key_name='$key'");
	if($id > 0) {
		if($value ne $currvalue) {
			$value =~ s/'/\\'/g;
			generic_update("avaya_keyvalues","active=0,record_status=2,updated_on=now()","id=$id");
			my $fields = "id,active,key_name,key_value,record_status,created_on,updated_on,deactivated_on";
			my $values = "null,1,'$key','$value',1,now(),now(),null";
			generic_create("avaya_keyvalues", $fields, $values);	
		} else {
			generic_update("avaya_keyvalues","active=1,record_status=1","id=$id");
		}
	} else {
		$value =~ s/'/\\'/g;
		my $fields = "id,active,key_name,key_value,record_status,created_on,updated_on,deactivated_on";
		my $values = "null,1,'$key','$value',1,now(),now(),null";
		generic_create("avaya_keyvalues", $fields, $values);
	}
	if($key =~ /\.station\.(\d+)\.status/) {
		my $station = $1;
		my $name = '';
		if($value =~ /^(.+?) \(ip=/) {
			$name = $1;
		} elsif($value =~ /^(.+) \(unregistered/) {
			$name = $1;
		}
		$name =~ s/'/\\'/g;
		generic_replace("avaya_display_name","station,name","'$station','$name'");
	}
}
close SCRAPED;
generic_update("avaya_keyvalues","active=0,record_status=3,deactivated_on=now()","record_status=9");

my $create = "
drop table if exists avaya_keyvalues;
create table avaya_keyvalues (
	id integer auto_increment primary key,
	active integer,
	key_name varchar(256),
	key_value varchar(256),
	record_status integer,
	created_on datetime,
	updated_on datetime,
	deactivated_on datetime
);
";

#my @count = generic_read("count(*)","callrecord","1=1");
#print "count is " . $count[0] . "\n";
db_close();

This script is really basic. It just does these things:

  1. Line 88, It marks all records in the database as ‘old’
  2. Starting in line 90, It cycles through the keyvalues file and checks for records where the value has changed (line 97). It marks these as ‘changed’ and then inserts the new record. It marks the unchanged records as ‘unchanged’ (line 104).
  3. If the current value is not in the database, it inserts it (line 110)
  4. It also replaces the value of the display name in case we want to see all extensions and names later (line 121)
  5. Lastly, any record that was not touched through this loop is NOT in the text file. Does that make sense? So any record that is still marked ‘old’ in step 1 must have been deleted from the Avaya system. So we mark these records as ‘deleted’ (line 125).

When this script is done, we have a list of all active records in the database. We also have a list of old values and deleted values. This gives you a rolling history of all extensions in your database. I cannot tell you how handy it is to see a history of an extension. You can see when the IP address changes, when registration changes, when firmware changes, network region, etc. And of course when a station is deleted you still have a record of it. This is very handy.

For those of you interested in Perl, there are also a bunch of helpful database functions in here. I keep these in my back pocket and often copy/paste them to the next project.

To tie this new perl script into our ‘sanity.sh’ script from last time, we should now have this:

file=/home/roger/avaya/sanity/data/sanitycheck_`date +"{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}Y-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}m-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}d-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}H-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}M"`.txt
perl /home/roger/avaya/sanity/av.pl /home/ARES/randerson/avaya/sanity/la.pbx /home/roger/avaya/sanity/commands.txt >$file
perl /home/roger/avaya/sanity/sanity.pl $file > /home/roger/avaya/sanity/keyvalues.txt
perl /home/roger/avaya/sanity/keys2db.pl /home/roger/avaya/sanity/keyvalues.txt

At the end of this shell script, you should have a fresh copy of all stations in your PBX in the database! Ready for reading with a simple PHP script. That will be next time, and hopefully MUCH sooner than this post. Sorry for the delay.

Before I finish, I want to thank you for reading this. I recently “moved” from an Avaya environment to a Cisco environment, so I don’t have easy access to the Avaya environment anymore. I am using some copies of the scripts so if you find any syntax errors or something doesn’t work, please let me know and we’ll get it working.

Once I finish this series, I’ll show you a way to AUTOMATICALLY generate labels for the 96xx phones. That’s the most exciting thing I’ve been wanting to share with you!

Roger

 

How to parse Avaya Communication Manager output into useful csv or database tables

12 Replies

Hello all!

This is a follow-up to my post about extracting data from Avaya Communication Manager. In that post, I showed you how to extract any “list”, “display”, or “status” from Avaya Communication Manager to a text file. First, I would like to show you my bash shell script that extracts this data. And then I’ll show you my perl script that converts the output to a text file that you can open with Excel or import into a database.

What information should you pull from Communication Manager? Well, in my case, I have a text file called ‘commands.txt’ that contains these lines:

display time
list ips
list surv
list med
status cdr
status trunk 1
status trunk 2
status trunk 8
status trunk 11
status trunk 16
status trunk 21
status trunk 79
list station
list reg
status station 2291
status station 2292
status station 2293
status station 2294
list call-forwarding
list off s
display time

So those are the various pieces of information that I care about. Looking at that info, those are my active trunk groups, and I want to know the status of four particular extensions (those happen to be the digital ports of my fax server). Also, I have a port network, so I want the status of IPSIs. Anyway, think about all the stuff you care about when you log into your PBX in the morning. And stick them in that “commands.txt” file. I put a “display time” at the beginning and end so I can look at the file later and tell how long it took to run the file.

So we want to run these commands automatically. So let’s create a file called “sanity.sh” and put this in the file:

file=/home/roger/avaya/sanity/data/sanitycheck_`date +"{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}Y-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}m-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}d-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}H-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}M"`.txt
perl /home/roger/avaya/sanity/av.pl /home/ARES/randerson/avaya/sanity/la.pbx /home/roger/avaya/sanity/commands.txt >$file
perl /home/roger/avaya/sanity/sanity.pl $file

Once you save the file, make it executable with

chmod +x sanity.sh

Let’s go over this script line-by-line, okay?

  1. The first line creates a variable for the file that will contain the data. There’s a little linux trick to embed a timestamp in the filename.
  2. The second line runs the file av.pl that I shared with you in a previous post about this script. It’s magical. I cannot take all the credit, but I have modified it for our purposes.
  3. The third line runs a file that parses the output of all the commands. This is a very useful script and I can take full credit for this one. It generates a list of “key/value” pairs to a text file.

My thought process with this script is to create a unique key for every piece of useful data. For example, I want the name, IP address, firmware version, gatekeeper, and network region for every station. I also want the EC500 mapping. And I would like the port number for analog stations. Oh! I also want a sense of low or high priority (Is it bad if the data changes?) and I need to include a site identifier in case I have the same extension in multiple sites. That’s a lot of information to display. And, if I have 1000 stations, it’s too much to display all this information on separate lines. That’s like 7000 lines of data for my stations. Fine for machines to read, but it seemed like too much for humans. I want to put them into one (and in the case of ec500, two) lines of data. I do this by assigning a “Key name” for my stations like this:

Key Name Key Value
lo.la.station.8348.offpbx EC500=>2135552978, OPS=>8348
lo.la.station.8348.status Anderson, Roger (ip=10.10.60.138;reg=3;ver=3.260A;gk=10.2.86.180)

See how much data I squeezed into that? And note that very few stations will have off-pbx information. Most stations will just have that station.status key. The script called sanity.pl takes the raw output from the Communication Manager and converts it to these key/value pairs above. Here is a link to sanity.pl. You’ll need to rename it to a ‘.pl’ file. This should compile just fine. When you copy it to your linux server, try

perl -c sanity.pl

That -c just means to check the syntax and don’t run it. It hopefully says “syntax okay”. Actually, I’m sorry but you’ll need to edit the file and change line 45 to the path where you want your ‘keyvalues.txt’ file, which will contain all your data. This should use a command line param, but it doesn’t. Sorry.

You might consider looking in more detail at this file. It’s where the magic happens and it’s a good framework for parsing other data. If you’re interested, here is a summary of how it works:

  • Starting in line 56, we check the raw text file line-by-line looking for the header for each ‘list’ or ‘display’ or ‘status’ section. For example, when we see the text ‘REGISTERED IP STATIONS’, then we know we are expecting to see the result of a ‘list reg’ (by setting our variable of type to 7).
  • Then on line 183 we actually process the registration status of stations. If type=7, then we are in the ‘list reg’ mode and if a line contains registration information for a station, then we capture the data.
  • Each section has some customization, but you’ll notice that (almost) each section performs a ‘set_data’ of the keys and values appropriate for that section.
  • At the end, the trunk group and station summary is generated and the keys are reported alphabetically.

Here are some examples of what the script does:

  • The script parses ‘list station’ and assumes all stations are unregistered.
  • The script parses ‘list reg’ and fills in the registration status for all stations (anything not parsed in this section has a status of ‘unregistered’)
  • The script parses ‘status trunk xxx’ and simply counts the trunks that are in service (ignoring the “idle/in use” values. We just count trunk members in service
  • The script parses ‘status cdr’ and simply stores the percent full of the buffers
  • The script parses ‘list off s’ and stores the off pbx mappings for all stations
  • The script parses various critical fields for IPSIs, media gateways, and survivable servers
  • The script parses ‘list call-forwarding’ and generates a list of all stations that are call forwarded.

At this point, if you ran the bash script, you would have an awesome list of keys and values in a text file called keyvalues.txt. You probably don’t need to know anything about Perl to get this working for you. But if you do know a bit of Perl, you’ll be able to do amazing things with it.

There’s more!

This data is useless unless you’re looking at it. In a future post, I will show you how to schedule this script with cron, push the data into a database, read it with a simple ‘web site in a single file’ php script, filter the data (including historical values!), and get alerts when the values change.

And then I have something REALLY amazing to show you!

Thanks for reading, everyone! Feel free to contact me at roger@rogerthephoneguy.com or post a comment here. If you need help getting this code working, or you want to tweak it a bit, let me know!

Roger

How to automatically extract data from an Avaya Communication Manager

42 Replies

You might call this post “the secret of my success” as a PBX admin for Avaya Communication Manager. Avaya provides some utilities for their techs and business partners, but customers don’t really have a lot of tools to easily extract data. In this post, I’ll show you how to extract ANYTHING from Avaya from a command line so it’s suitable for scripting. In my case, I parse the data further and generate a list of all stations and report of changes to my PBX every 15 minutes. Have you ever wondered who used to have extension 4438? How about that phone for the intern over the summer named “Bodkin Van Horn” that you deleted but now he has been hired and you want to give him the same extension? How about the question “how long has this extension been unplugged”?

Back in mid 00s, I wrote some PHP integrate with CM using telnet. Since I didn’t know much about PHP’s ANSI support, I ended up writing my own ANSI parser of the raw data. Anyway, it was great, but many customers don’t support telnet and want all communication to CM to be through ssh, which is reasonable, right?

Then I found this post from Benjamin Roy where he provided a perl module to do it through ssh. Also, he meticulously reverse-engineered Avaya’s OSSI protocol, which I think is impressive and sounds like fun. But I didn’t use OSSI; I just wanted a way to get data using ssh. So I took Ben’s code and tweaked it a little bit. It’s straight perl code – it’s not a module or anything like that. It does have dependencies on a couple other modules though. You do not need to be a perl developer to use this. It’s really easy. Here is a link to the script. Just save this as “av.pl”. Try to run it with “perl -c av.pl” (the -c means just check to make sure it compiles but do not run it). You might get some errors about missing modules. There are only three, and this is how you install them if you need to:

perl -MCPAN -e "install Expect"
perl -MCPAN -e "install Data::Dumper"
perl -MCPAN -e "install Term::VT102"

I should probably mention here – if you don’t have a Linux server in your environment, you should really get one. Your Virtual Machine team can make one for you – just about any distribution of Lunux will do and they take almost no resources. If you need to justify it, just say something like “utility server for PBX monitoring” or something like that.

Anyway, back to the script. Naturally, it needs to connect to your PBX, so create a little text file like “mypbx.txt” with the contents

10.10.40.89,5022,cmusername,Passw0rd,ssh

Obviously, replace the IP address, username, and password of your PBX.

And then create another file called “commands.txt” with the contents

list trunk
display station 3100

This can contain whatever commands you’d like. list reg, list station, list trunk, list locations, list measurements, stat cdr, etc. It should be any command that can be “paged” through, you know?

And then simply run this line

perl av.pl mypbx.txt commands.txt

The script will connect to the PBX defined in mypbx.txt, run the commands in commands.txt and output the results to STDOUT!

This is the core script to a bunch of stuff I automate in my PBXs. In a later post, I’ll show you how I parse the results with Perl and make use of the data.

I call this “the secret of my success” because I use this data to build a simple html page of all extensions in all of my PBXs every 15 minutes. I keep this page up all the time in a browser tab, which enables me to simply “control-f” for any name in the PBX and I have the extension number and the IP address of the station (or ‘unregistered’ if it’s unplugged). I can see unregistered ‘guest’ numbers, see the station’s history, etc. There’s a lot more I do with this information and I look forward to showing you.

Please let me know what you think! And if there’s anything I can do to help you get it working.

Lastly, if any of you need to do any telephone system testing (capacity, QA, DID number ports, call-flow, queuing, etc), please check out my post about CallsByCloud. If you sign up and use the promotion code ‘roger2015’, you’ll get $10 in credit and a 20{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0} discount on the per-minute rate.

Also, if you hate unsolicited outbound telemarketing, consider subscribing to the Jolly Roger Telephone Company. Your telemarketers will entertain and delight you. Sample recordings here.

Thanks all!

Roger

 

Roger speaks at TEDx Naperville about disrupting the telemarketing industry!

4 Replies

Way back in July of 2014, I wrote about how I stopped telemarketing at my house. Little did I know that my efforts to combat and disrupt unsolicited telemarketers would eventually lead to me giving a TEDx talk about it!

Some of you may know that, in an effort to stop telemarketing, I created a robot to talk to them. It’s an interesting story and I posted some audio at the beginning of 2016. My story was picked up by Gizmodo on February 1, 2016 and things got really crazy from there.

Anyway, Fast forward to November 4, 2016 and I find myself on stage at TEDxNaperville in Illinois speaking in front of about 500 people at a beautiful venue. I’ve never spoken in front of a crowd like that, and it was amazing.

Don’t get me wrong – as a phone guy, I love call centers. But as a phone guy I also love the telephone network. The Public Switched Telephone Network has been around for about 140 years and it is crushed under the burden of unsolicited telemarketing. Blocking doesn’t work. Their predictive dialers simply call the next sucker. I think the only solution (short of abandoning the entire network) is the Jolly Roger Telephone Company. Here is me story about how I’m disrupting the scammers, spammers, and crooks, which make up most of the unsolicited telemarketing industry.

By the way, this goes for cold callers too. If your business is struggling to handle cold callers (ask your receptionists, Investor Relations team, or any admins for your CxOs) then I also have a solution for you. It’s called a Biz-Bot and it will handle all your pesky cold callers for all your employees.

But anyway, here is the talk!

How to parse Avaya CDR into MySQL database with Perl

10 Replies

This is a follow-up to my earlier post about setting up CDR processing with Kiwi Syslog Server. In that post, I discuss how to capture the data to a text file. But of course, if we are capturing CDR, we probably want to analyze it, right?

So this post is dedicated to capturing the data to a MySQL database. It is not for the faint-of-heart though. It requires a little knowledge of MySQL and willingness to read a tiny bit of perl. I am happy to help you set this up, though. Either via this blog as time allows, or I would love to work for you as an hourly contractor – and I love all things telecom!

If you don’t have a Linux server in your network that you can use for your own purposes, I highly recommend one. Any Linux distribution will do, and you probably have a VMWare infrastructure to support it. If you need to justify it, I can help – perhaps even this blog entry will help. Tell your boss you need to perform text analysis of your PBX data. This blog entry assumes you have Linux, but I suppose it will work on Windows with a LAMP stack (but good luck with local firewall rules in Windows).

So first. you must create a MySQL database to store the call records. Let me know if you need help doing this. Since I have to start somewhere with this post, I will assume you have access to a MySQL database. You just need to create a table to store the data. Here is the statement to do that:

drop table if exists cdr_syslog;
create table cdr_syslog (
 id integer auto_increment primary key,
 method varchar(8),
 reported_on datetime,
 deduced_starttime datetime,
 source_ip varchar(32),
 from_number varchar(24),
 to_number varchar(24),
 call_type varchar(12),
 call_time varchar(16),
 direction varchar(3),
 duration_ss integer,
 call_hash varchar(64),
 pbx_time_of_day_hh varchar(2),
 pbx_time_of_day_mm varchar(2),
 pbx_duration_h varchar(1),
 pbx_duration_mm varchar(2),
 pbx_duration_6s varchar(1),
 pbx_condition_code varchar(1),
 pbx_access_code_dialed varchar(4),
 pbx_access_code_used varchar(4),
 pbx_dialed_number varchar(23),
 pbx_calling_number varchar(14),
 pbx_account_code varchar(15),
 pbx_authorization_code varchar(7),
 pbx_frl varchar(1),
 pbx_incoming_ckt varchar(3),
 pbx_outgoing_ckt varchar(3),
 pbx_feature_flag varchar(1),
 pbx_attendant_console varchar(4),
 pbx_incoming_tac varchar(4),
 pbx_node_number varchar(2),
 pbx_ins varchar(5),
 pbx_ixc varchar(3),
 pbx_bcc varchar(1),
 pbx_ma_uui varchar(1),
 pbx_resource_flag varchar(1),
 pbx_packet_count varchar(5),
 source_line varchar(128),
 created_on datetime
);

alter table cdr_syslog add index i_call_hash(call_hash);
alter table cdr_syslog add index i_deduced_starttime(deduced_starttime);
alter table cdr_syslog add index i_from_number(from_number);
alter table cdr_syslog add index i_to_number(to_number);

Those statements create the table and a few indexes to speed things up. The indexes for call_hash and deduced_starttime are used when inserting records. The indexes for from_number and to_number are added since these are probably the most common fields to query. Typically, I will add an index for any field that appears in my ‘where’ clause, but I’m no DBA so there are probably better ways to tune it. However, this works fine for me and should for you as well.

I am using a Kiwi Syslog server to capture the CDR. But the cool thing about Kiwi is that you can “forward” the message along to another server. So I configured mine to send it to my Linux server. This is the screenshot for that forward command within Kiwi:

kiwi-send-logs-out-via-syslog

This simply sends the Syslog message to my server on UDP port 514. This is the typical Syslog port. Then I have a Perl script running on my server. That Perl script has some dependencies

use strict;
use DBI;
use Net::Syslogd;
use Digest::MD5 qw(md5_hex);
use POSIX qw(setsid);

You don’t need to be familiar with Perl to run this script, but you will need to make sure these packages are installed. For example, if I have a script that runs this line:

use Roger::Wilco;

When I try to run the script, I will get this error:

Can't locate Roger/Wilco.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at syslog.pl line 2.
BEGIN failed--compilation aborted at syslog.pl line 2.

So if you try to run my CDR script and you get any kind of “Can’t Locate” error, then you need to install that package. To to this, type this at a command line:

Perl -MCPAN -e shell

And that will get you to the CPAN command line where you can install packages.

# perl -MCPAN -e shell
Terminal does not support AddHistory.

cpan shell -- CPAN exploration and modules installation (v1.9402)
Enter 'h' for help.

cpan[1]>

And from here, you can type

install Net::Syslogd

Or whatever other packages you need to install. Note that case matters, and that’s a double colon between the words. When it asks you a yes/no question, say yes. If all goes well, then it should install fine. CPAN installs all dependencies. If you get errors, the most common is you don’t have a c++ compiler on your machine. I never did figure out how to fix that in Windows. In Linux, there are lots of tutorials. With that in mind, here is the Perl script that will catch the CDR forwarded from Kiwi. Be sure to read my comments at the end.

#!/usr/bin/perl

### Read unformatted CDR data from an Avaya server
### This script uses Net::Syslogd to listen to standard syslog messages
### The Avaya phone systems send to TCP ports. Unfortunately, this script is to listen from 
### A 'proxy' instance of CDR. for example, a Kiwi Syslog Server that forwards syslog messages
### out to another server.
### see my blog http://rogerthephoneguy.com/?p=404 for details


### Usage!
### set the $dir folder to where you store text files from your syslog server
### and run this script with 'perl cdrd.pl file' to tell the script to process files
### or run as 'perl cdrd.pl' to just watch the processing fly by
### or run as 'perl cdrd.pl -d' as a daemon process
### to stop the daemon, just create a file matching $killfile
### Or do a 'ps -ef |grep cdrd.pl to find the PID
### and kill -9 that pid
### not the best way to manage daemons, but we are telephone engineers here, not linux admins, right?

use strict;
use DBI;
use Net::Syslogd;
use Digest::MD5 qw(md5_hex);
use POSIX qw(setsid);

my $db;
my $dir = "/home/roger/avaya/cdr/data/";
my $killfile = "/home/roger/avaya/cdr/cdrd.end";


sub db_open {
        my $dsn = "DBI:mysql:dbname_ie_avaya:localhost";
        my $user = "dbuser";
        my $pass = "dbpassword";
        while (!($db)) {
                $db = DBI->connect($dsn, $user, $pass);
        }
}

sub db_close {
        if($db) {
                $db->disconnect();
        }
        undef($db);
}

sub sql { #check
        my ($sql) = (@_);
        my $sth = $db->prepare($sql);
        if(!($sth->execute())) {
                print "WARNING - unable to execute $sql\n";
        }
        $sth->finish();
}

sub generic_create {
        my ($table, $fields, $values) = (@_);
        my $sql = "insert into $table ($fields) values ($values)";
        my $sth = $db->prepare($sql);
        if(!($sth->execute())) {
                print "WARNING - unable to insert with $sql\n";
        }
        $sth->finish();
}

sub generic_read {
        my ($select, $table, $where) = (@_);
        my @datarow;
        my $sql = "select $select from $table where $where limit 1";
        my $sth = $db->prepare($sql);
        if($sth->execute()) {
                @datarow = $sth->fetchrow_array();
        } else {
                print "unable to execute $sql\n";
        }
        $sth->finish();
        return $datarow[0];
}

sub one {
        my ($sql) = (@_);
        my @datarow;
        my $sth = $db->prepare($sql);
        if($sth->execute()) {
                @datarow = $sth->fetchrow_array();
        } else {
                print "unable to execute $sql\n";
        }
        $sth->finish();
        return $datarow[0];
}

sub process_cdr_line {
    my $cdr = shift;
    chomp($cdr);
    my ($logtime,$facsev,$remoteaddr,$message) = split(/\t/,$cdr);
    my $md5message = md5_hex($message);
    #print "012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789\n$message";
    #here are the start and length of each data point within the message of an "unformatted" cdr line
    #Nobody knows what some of these are. It's a mystery today
    #your condition codes may be different, so you should watch your data closely for several days
    my @parses = (
        0,2,     #time of day hours
        2,2,     #time of day minutes
        4,1,     #duration hours
        5,2,     #duration minutes
        7,1,     #duration tenths
        8,1,     #condition code
        9,4,     #access code dialed
        13,4,    #access code used 
        17,15,     #dialed number
        32,10,     #calling number
        42,15,     #account code
        57,7,     #auth code
        66,1,     #frl
        67,3,     #incoming ckt
        70,3,     #outgoung ckt
        73,1,     #feature flag
        70,4,     #attendant console
        76,4,     #incoming tac
        82,2,     #node number
        84,4,     #INS
        88,3,     #IXC
        92,1,     #BCC
        93,1,     #MAUUI
        94,1,    #Resource flag
        95,4    #packet count
    );
    my $values = '';
    my $insert;
    my $i;
    my $calltime;
    my $duration;
    my $direction = 'unk';
    my $dialed;
    my $calling;
    my $node = '01';
    my $condition;
    for($i=0; $i<25; $i++) { my $start = $parses[$i*2]; my $length = $parses[$i*2+1]; my $part = substr($message,$start,$length); #print "this $i from $start with length $length is '$part'\n"; $part =~ s/^ *//g; $part =~ s/ *$//g; if($i == 0) { #time of day hh $calltime = "$part:"; } elsif($i == 1) { #time of day mm $calltime .= $part; } elsif($i == 2) { #duration h $duration = $part * 60 * 60; } elsif($i == 3) { #duration mm $duration += ($part * 60); } elsif($i == 4) { #duration 1/10 min (6 sec) $duration += ($part * 6); } elsif($i == 5) { #condition code $condition = $part; if($part == 9) { $direction = "in"; } elsif($part == 7) { $direction = "out"; } } elsif($i == 8) { #dialed number $dialed = $part; $dialed =~ s/E$//; if(($dialed ne '6226') && ($dialed =~ /^6[234]\d\d$/)) { $node = '44'; } } elsif($i == 9) { #calling number $calling = $part; $calling =~ s/E$//; if(($dialed ne '6226') && ($dialed =~ /^6[234]\d\d$/)) { $node = '44'; } } elsif($i == 17) { #incoming tac if($part =~ /^#0\d\d/) { $node = '44'; } } elsif($i == 18) { #node number $part = $node; } $values .= "'$part',"; } if($direction eq "in") { my $temp = $dialed; $dialed=$calling; $calling=$temp; } if(($dialed =~ /^\d*$/) && ($calling =~ /^\d*$/)) { #dialed_name and calling_name are filled from a 'display_name' table that is not part of this demo. #for more information, contact me through RogerThePhoneGuy.com and we can chat about how this table is filled my $dialed_name=''; #generic_read("name", "avaya_display_name","station='$dialed'"); my $calling_name=''; #generic_read("name", "avaya_display_name","station='$calling'"); $dialed_name =~ s/'/\\'/g; $calling_name =~ s/'/\\'/g; #print "logtime:$logtime\ncondition $condition\ndirection $direction\ndialed $dialed ($dialed_name)\ncalling $calling ($calling_name)\nduration $duration\n"; $insert = "insert into cdr_syslog values (null,'syslog','$logtime',null,'$remoteaddr','$calling','$calling_name','$dialed','$dialed_name','type','$calltime','$direction',$duration,'$md5message',"; $insert .= "$values '$message',now())"; my $check = one("select count(*) from cdr_syslog where call_hash = '$md5message'"); #print "hash check = $check\n"; if($check == 0) { #We insert the md5 of this line, to prevent double-logging. This allows us to re-process the cdr as much as necessary w/o dups in the database #print "$insert\n"; sql($insert); sql("update cdr_syslog set deduced_starttime = DATE_SUB(reported_on,INTERVAL duration_ss SECOND) where call_hash='$md5message'"); } else { #print "duplicate\n"; } } else { $insert = "insert into pbx_syslog values (null,'$message')"; #sql($insert); } } sub daemonize { #check open STDIN, '/dev/null' or die "Can't read /dev/null: $!"; open STDOUT, '>>/dev/null' or die "Can't write to log: $!";
    open STDERR, '>>/dev/null' or die "Can't write to log: $!";
    defined(my $pid = fork)   or die "Can't fork: $!";
    exit if $pid;
    setsid                    or die "Can't start a new session: $!";
    umask 0;
}

if($ARGV[0] eq "-d") {
    daemonize;
}

db_open();

if($ARGV[0] eq "file") {
    opendir(DH, $dir);
    my @files = readdir(DH);
    closedir(DH);
    foreach my $file (@files) {
        open CDR, "$dir$file";
        my $i = 0;
        while (my $line = ) {
            $i++;
            my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = localtime(time);$mon++;$year+=1900;
            $line =~ s/[^!-~\s]//g; #strips unprintable
            process_cdr_line($line);
            #die if($i>2);
        }
        print "$dir$file - $i lines\n";
        close CDR;
        unlink "$dir$file";
    }

} else {
    my $syslogd = Net::Syslogd->new()
      or die "Error creating Syslogd listener: ", Net::Syslogd->error;

    while (!(-e $killfile)) {
        my $message = $syslogd->get_message();
        if (!defined($message)) {
            printf "$0: {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}s\n", Net::Syslogd->error;
            exit 1;
        } elsif ($message == 0) {
            next
        }

        if (!defined($message->process_message())) {
            printf "$0: {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}s\n", Net::Syslogd->error
        } else {
            my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = localtime(time);$mon++;$year+=1900;
            my $logtime = sprintf("{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}04d-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}02d-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}02d {0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}02d:{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}02d:{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}02d",$year,$mon,$mday,$hour,$min,$sec);
            my $remoteaddr = $message->remoteaddr;
            my $remoteport = $message->remoteport;
            my $facility = $message->facility;
            my $severity = $message->severity;
            my $time = $message->time;
            my $hostname = $message->hostname;
            my $msg = $message->message;
            my $line = "$logtime\t$facility.$severity\t$remoteaddr\t$msg\n";
            print $line;
            #now $line looks like any line from the CDR files
            #so this next part works via syslog or files
            $line =~ s/[^!-~\s]//g; #strips unprintable
            process_cdr_line($line);
        }
    }
    unlink $killfile;
}

db_close();

There’s a lot in that script. Things you must know:

  1. You’ll need to replace the database name, username, and password in the “db_open” subroutine.
  2. The comments at the top explain how to run it to process files, or to capture syslog live, or even how to run as a daemon.
  3. Each CDR line is hashed and included in the record, so don’t worry about processing the CDR files multiple times. It will NOT insert duplicate records.
  4. When processing files, the script will delete the file when done. So always copy the CDR files into a separate folder before running this script.
  5. I kludged this together from a working Perl script. I removed some identifying information, moved some variables around, and added a bunch of comments. If you get any errors, let me know – it’ll probably be really easy to fix.
  6. For any hobbyist Perl developers out there, this script includes some handy database functions that insert records, return rows, and return one record.
  7. This script is for information purposes only. Naturally, I don’t provide any warranties, guarantees, or assertions that it will work with your systems. Run it at your own risk.
  8. If you’d like me to help set this up, I’d love to work for you. I love telecom and I’m always available for hire as a consultant. I would LOVE to be your telephone guy!

If you’re planning on trying this, then I’m really flattered. Let me know how it goes. Mine has been running for about two years and I cannot tell you how handy it is to have that much CDR data in a MySQL table. I have a PHP script that I use to analyze the data. Do you want to see that?

Thanks all,

Roger