Friday, August 9, 2013

SQL Statements on CM DB

To determine what users had their phones forwarded. Command line examples of how to do this:

admin: run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where (cfd.cfadestination != '')


If you have an interest in seeing stations that are call forwarded to voicemail AND other destinations, then the following query can be used:

admin: run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where ((cfd.cfadestination != '') or (cfd.cfaVoicemailEnabled='t'))


If you are looking for a particular number like 17035551212 as the call forward destination, then the following could be used:

admin: run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where (cfd.cfadestination = '917035551212')

NOTE: The above assumes that the off net access code of 9

Ref: https://supportforums.cisco.com/thread/2010685

Monday, February 11, 2013

Enable Calendar Presence

When Cisco originally released the Cisco Unified Presence Communicator (CUPC) client several years ago, they provided end-users with the ability to share their calendar presence.
Jabber for Windows, which replaced CUPC, does not provide the same simple way for the end-user to share his/her calendar presence status (e.g. “In a Meeting” or “Available”).
In order for the end-user to “opt-in” to this feature, he/she must login to the Cisco Unified Presence User Options web page (https://[imp server]/cupuser).  Obviously, this is not the most ideal situation.
In order to enable this feature for the end-user, perform the following steps:
  1. SSH into the Cisco Unified Presence Server
  2. Login with the OS administrator ID and the defined password.
  3. From the command prompt enter the following command, substituting “username” for the end-user account being modified:
run sql execute procedure spSetUserCalendaring((select pkid from enduser where userid like ‘username’),’t',’t')
To adjust this setting for multiple users, I leveraged Excel and the concatenate formula as follows:
  • Column A: This column should contain the usernames
  • Column B: =CONCATENATE(“     run sql execute procedure spSetUserCalendaring((select pkid from enduser where userid like ‘”,A2,”‘),’t',’t') “
Take the results from Column B, copy the values and paste into TextWrangler (Mac) or Notepad++ (Windows).  From here, you can copy/paste the commands into the SSH terminal.
If you notice, I added 5 spaces in front of the SQL command. During my testing, I found that some of the initial characters can be cut off when pasting multiple lines into the CLI.

Monday, January 14, 2013

Translator_X


http://translatorx.cisco.com/


What is TranslatorX?

TranslatorX is a troubleshooting tool that allows you to quickly parse through Cisco Unified Communications Manager or Cisco Unified Border Element trace files and search for Q.931, H.225, SCCP (Skinny), MGCP, or SIP messages. TranslatorX supports searching through large numbers of trace files and provides advanced filtering capabilities to help you find the calls you are interested in. You can generate sophisticated message filters. These filters can be created automatically for you based on call detail record information as well.
TranslatorX is available for Mac OS X, Microsoft Windows, and Linux.

Features

The following is a list of the features of TranslatorX
  • Reads CCM/SDI or SDL Trace files from Cisco Unified Communications Manager
  • Reads SIP debugs from Cisco Unified Border Element (debug ccsip messages)
  • Finds and decodes SCCP (Skinny), H.323 (H.225 and H.245), ISDN Q.931, MGCP, and SIP messages
  • Generates a call list based on Call Detail Record information contained in the trace
  • Decodes Call Detail Record Fields
  • Advanced Filtering Capabilities
  • Automatic Filter generation based on calls from the Call List
  • Message Sequence Diagram Generation
  • Ability to traverse a hierarchy of folders automatically
  • Automatic sorting of messages based on timestamp (in the case of opening multiple files)
  • Automatic exclusion of repetitive messages (SCCP Keepalives, SIP OPTIONS, and MGCP Keepalives)
  • Automatic correlation of H.245 and H.225 messages
  • Can read plain .txt files as well as compressed .gz files automatically

Tuesday, January 1, 2013

MGCP TSHOOTING

Q931 SETUP

Q931 SETUP message arrives. The destination is 2025552002. The bearer channel is channel #1 . The D channel which is used for signaling is the last channel (Serial 0/0/0:23). The SETUP message will be packetized by the gateway and sent to the “mgcp call agent” (the first choice Unified Communications Manager). This is process of taking the ISDN messages arriving on the PRI and packetizing them and sending them to the UCM is known as Q931 backhaul. The UCM listens on TCP port 2428- the gateway uses an ephemeral port that is dynamically selected.
May 30 13:04:07.404: ISDN Se0/0/0:23 Q931: RX <- SETUP pd = 8  callref = 0x0084
 Bearer Capability i = 0x8090A2
 Standard = CCITT
 Transfer Capability = Speech 
 Transfer Mode = Circuit
 Transfer Rate = 64 kbit/s
 Channel ID i = 0xA98381
 Exclusive, Channel 1
 Progress Ind i = 0x8583 - Origination address is non-ISDN 
 Calling Party Number i = 0x0080, N/A
 Plan:Unknown, Type:Unknown
 Called Party Number i = 0xA1, '2025552002'
 Plan:ISDN, Type:National

CRCX

The MGCP messaging sequence now begins. As a reminder the UCM will be listening on UDP port 2427. The Q931 SETUP would have been received by the UCM. The UCM is then able to route the call to the destination (the incoming CSS and incoming significant digits settings on the gateway configuration within UCM is used to route the call to the next hop which is the phone). The UCM will use SIP or SCCP for call setup on the line/phone side but in this instance we are interested in the MGCP call setup. So the first message is  Create Connection (CRCX). The UCM is telling the gateway to provide the ip address and port number that will be used for this call. The UCM is the master and the gateway is the slave and so the UCM also provides a lot of other information to the gateway namely:
(1) C (CallId): globally unique conference id
(2) X (RequestIdentifier): random hexadecimal string generated- all responses to this create connection will contain X=1 within the message.
(3) L (LocalConnectionOptions): information about the call that will be setup- codec = 711u, sampling rate 20ms, s= off (vad), TOS = b8 = DSCP 46
(4) M (ConnectionMode): recvonly.  UCM is not providing gateway information about the other party involved in the call just yet. This is because the phone has not answered yet so the UCM does not have that information. So the gateway will not be sending any packets at this stage.
(5) R (RequestedEvents): The D means invoke the “DTMF package”  if the gateway receives 0-9, ACBD*#.

May 30 13:04:07.404: MGCP Packet received from 10.10.210.11:2427--->
CRCX 610 S0/SU0/DS1-0/1@SiteA-RTR MGCP 0.1 <<< 610 is the transaction number. Each message
                                           <<<  needs a response with this transaction id.
C: D000000002cd8630000000F580000084   
X: 1
L: p:20, a:PCMU, s:off, t:b8, fxr/fx:t38
M: recvonly
R: D/[0-9ABCD*#]
Q: process,loop
<---
The gateway sends a 200 OK confirmation to the UCM providing the requested information that was in the CRCX.  The gateway also provides the UCM a local call identifier (I: ConnectionID). In this instance  the local call ID of I= 57 should be used when referencing this call. The “57″ is randomly generated and only has significance locally for the duration of this call. The information about codec’s and RTP port numbers cannot be carried in the 200 OK and therefore we have Session Description Protocol (SDP) information within the 200 OK. One of the most important lines within SDP is the “m=” line which is used to reveal the information about media. The RTP media types being used by the gateway are “0″ which is G711ulaw and 100 which is Named Signaling Events (NSE) used for fax and modem communications.

May 30 13:04:07.404: MGCP Packet sent to 10.10.210.11:2427--->
200 610 OK <<< 200 OK response. This the response to the request which had transaction id 610
I: 57 <<< Local call ID

v=0
o=- 87 0 IN IP4 10.10.110.1
s=Cisco SDP 0 <<< Beginning of SDP
c=IN IP4 10.10.110.1 <<< mgcp bind media source lo0
t=0 0
m=audio 16910 RTP/AVP 0 100 <<< RTP port number 16910, 0 is g711u, 100 is NSE
a=rtpmap:100 X-NSE/8000
a=fmtp:100 200-202 <<< fmtp= format specific params for rtp payload 100.
                   <<< 200, 201, 202 are Cisco NSE events related to T38 fax
a=X-sqn:0
a=X-cap: 1 audio RTP/AVP 100
a=X-cpar: a=rtpmap:100 X-NSE/8000
a=X-cpar: a=fmtp:100 200-202
a=X-cap: 2 image udptl t38
<---

Alerting, Ringback, RQNT

Below we have the Q931 Call Proceeding and Alerting being sent out to the PSTN- this is  being received from the UCM on the Q931 backhaul link and converted to circuit switched signaling by the gateway DSPs and sent out to the PSTN. The Alerting means that the destination phone is ringing and hence ringback should be given to the caller.  Who provides the ringback? The ringback is known as in-band information is played by our gateway to the PSTN. We inform the PSTN that we are playing ringback since the Progress Indicator is “8″ and this means “in-band” information is present.

May 30 13:04:07.408: ISDN Se0/0/0:23 Q931: TX -> CALL_PROC pd = 8  callref = 0x8084
 Channel ID i = 0xA98381
 Exclusive, Channel 1
May 30 13:04:07.408: ISDN Se0/0/0:23 Q931: TX -> ALERTING pd = 8  callref = 0x8084
 Progress Ind i = 0x8088 - In-band info or appropriate now available
The PSTN now knows that we are going to play a tone/annoucement. Now we need to play it! The UCM tells the gw to play a ringback tone to the PSTN using the Notification Request message.
May 30 13:04:07.408: MGCP Packet received from 10.10.210.11:2427--->
RQNT 611 S0/SU0/DS1-0/1@SiteA-RTR MGCP 0.1
X: 1
R: D/[0-9ABCD*#]
S: G/rt  <<<<<<  S: signal request, G- generic media package, rt= ringback tone
Q: process,loop
<---
We confirm this to the UCM
May 30 13:04:07.416: MGCP Packet sent to 10.10.210.11:2427--->
200 611 OK
<---

CONNECT, MDCX

The next message is the ISDN CONNECT message which means that the Called Party (IP Phone) has answered.

May 30 13:04:13.408: ISDN Se0/0/0:23 Q931: TX -> CONNECT pd = 8  callref = 0x8084
 Display i = 'SiteA phone 2'
May 30 13:04:13.420: ISDN Se0/0/0:23 Q931: RX <- CONNECT_ACK pd = 8  callref = 0x0084
At this stage the gateway needs to know about where to send RTP packets to. UCM tells the MGCP gw information about who to send RTP packets to using the Modify Connection (MDCX) message. We have to include SDP information in order to do this.
May 30 13:04:13.676: MGCP Packet received from 10.10.210.11:2427--->
MDCX 613 S0/SU0/DS1-0/1@SiteA-RTR MGCP 0.1
C: D000000002cd8630000000F580000084 <<< same CallID as CRCX
I: 57 <<< same local CallId as earlier
X: 1 <<< same request identifier as earlier
L: p:20, a:PCMU, s:off, t:b8, fxr/fx:t38
M: sendrecv <<< previously was recvonly- now two way audio.
R: D/[0-9ABCD*#], FXR/t38
S:
Q: process,loop

v=0
o=- 87 0 IN EPN S0/SU0/DS1-0/1@SiteA-RTR
s=Cisco SDP 0 <<< SDP header
t=0 0
m=audio 22876 RTP/AVP 0 <<< IP Phone is listening on port 22876 using rtp payload "0" which is g711u
c=IN IP4 10.10.200.128 <<< IP Phone IP address
a=X-sqn:0
a=X-cap:1 image udptl t38
<---

The MDCX is ack’d by the UCM below.

May 30 13:04:13.692: MGCP Packet sent to 10.10.210.11:2427--->
200 613 OK
<---
At this stage we have an active call. Let’s look at the process of call teardown.

Call Teardown- MDCX

We  see the MDCX being used to revert to “recvonly” meaning stop sending packets to the IP phone.
May 30 13:04:18.616: MGCP Packet received from 10.10.210.11:2427--->
MDCX 614 S0/SU0/DS1-0/1@SiteA-RTR MGCP 0.1
C: D000000002cd8630000000F580000084 <<< same CallID
I: 57 <<< same local CallID
X: 1 <<< same requestID
M: recvonly <<< stop sending packets to IP Phone
R: D/[0-9ABCD*#] <<< keep telling UCM about DTMF received from PSTN
Q: process,loop
<---
We send confirmation to the UCM.
May 30 13:04:18.620: MGCP Packet sent to 10.10.210.11:2427--->
200 614 OK
<---

Q931 DISCONNECT

In this instance the IP Phone hangs up the call and we will see the DISCONNECT  sent out to the PSTN. If the PSTN hung up the call we would see the DISCONNECT arrive from the PSTN.

May 30 13:04:18.624: ISDN Se0/0/0:23 Q931: TX -> DISCONNECT pd = 8  callref = 0x0002
 Cause i = 0x8290 - Normal call clearing
May 30 13:04:18.636: ISDN Se0/0/0:23 Q931: RX <- RELEASE pd = 8  callref = 0x0084
May 30 13:04:18.636:ISDN Se0/0/0:23 Q931: TX -> RELEASE_COMP pd = 8  callref = 0x0002

DLCX

The UCM instructs the gateway to stop listening on the RTP port number setup in the CRCX phase earlier. This is done with the Delete Connection (DLCX) message.
May 30 13:04:18.640: MGCP Packet received from 10.10.210.11:2427--->
DLCX 615 S0/SU0/DS1-0/1@SiteA-RTR MGCP 0.1
C: D000000002cd8630000000F580000084 
<<< same CallID
I: 57 <<< same local CallID
X: 1 <<< same requestID
S:
<---
The gateway confirms the DLCX and includes Connection Parameters (“P”) or QoS stats regarding the call. PS=packets sent, OS=octets sent, PR=packets received, OR=octets received, PL=packets lost, JI= jitter, LA=latency

May 30 13:04:18.664: MGCP Packet sent to 10.10.210.11:2427--->
250 615 OK
P: PS=247, OS=39520, PR=245, OR=39200, PL=1, JI=0, LA=0 <<<<
<---