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.

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

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