CUCM SQL

This provides devices and DNs where the External Phone Number Mask is like 7162010662. The percent sign allows you to allow for an unknown number of characters if you are searching for partial numbers. This sample sorts by device name.

run sql select d.name, d.description, n.dnorpattern, dmap.e164mask from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on dmap.fknumplan = n.pkid where dmap.e164mask like '%7162010662' order by d.name

This provides Translation Patterns where the Called Party Transform Mask is starts with 330.

run sql select dnorpattern, calledpartytransformationmask from numplan where tkpatternusage=3 and calledpartytransformationmask like '330%'

This provides CUCM phone Device Name, Description, Model and Phone Load Name configured on device if present:

run sql SELECT d.name AS device, d.description, m.name AS model, d.specialloadinformation AS device_load FROM device d INNER JOIN typemodel m ON d.tkmodel = m.enum WHERE m.tkclass = 1 ORDER BY m.name, d.name;

Download Cisco RTMT Real Time Monitoring Tool via direct URL

Perhaps your browser doesn't allow you to download Cisco RTMT from your CUCM | Application | Plugins link.  I suspect this might be a standard setting in more security conscious locations.

Try this: https://your_CUCM_url_or_IP_address:8443/plugins/CcmServRtmtPlugin.exe

Now when you get it installed and it is visually unusable, check this bug out:  https://quickview.cloudapps.cisco.com/quickview/bug/CSCva07316

1. Right click your RTMT executable
2. Click Compatibility tab
3. Click Change High DPI Settings button
4. Check Override high DPI scaling behavior.
5. Choose System from dropdown

Enjoy your Cisco RTMT.

Create Cisco prompt WAV files via TTS

Here's a little website I put together while experimenting with Google's Text-to-Speech Client Libraries and the Text-to-Speech API:

https://www.ciscoprompts.com

It will create WAV files in 8bit 8khz uLaw format, suitable for uploading to Cisco UCCX, Unity Connection, etc. via Google's text to speech service.

If you find it useful or have a suggestion on how to make it useful, I'd appreciate leaving me message.

Visual Studio Code and Remote-SSH to AWS EC2 Ubuntu

View | Command Palette...

Remote SSH

Add New SSH Host...

ssh ubuntu@18.18.18.18

select your SSH configuration file from dropdown

Open Config

Modify your config file accordingly.  The HostName value should be the EC2 host name / public DNS setting found in the AWS EC2 console.  The User will generally be ubuntu.  When using Visual Studio Code in Windows the IdentityFile path should be enclosed in double quotes, use single forward slashes and use the .pem extension on your key despite using the .ppk when connecting via  PuTTy, WinSCP or the like.

For example:

Host 18.18.18.18

HostName ec2-18-18-18-18.compute-1.amazonaws.com

User ubuntu

IdentityFile "C:/Users/rmaslanka/Documents/AWS/AWSmykey.pem"

Win10 SSH configuration for Visual Studio Code

If you have the ppk but not the base64 / pem private key required here, you can convert the ppk using PuTTygen, a key generating tool by the developers of PuTTy, found here: 

https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

Choose Load and browse for your existing key with the ppk extension.  Choose Conversions | Export OpenSSH key and save it, now with the a pem extension.  You'll then have both the ppk and base64 pem private key versions.

PuTTygen ppk to pem conversion

CUCM 12.5 CDR billing and DRS server failure after upgrade

 Tl;dr: CUCM 12 and beyond supports a shorter list of encryption algorithms for SFTP file transfers.

After completing a CUCM cluster upgrade from 11.5(1)SU6 to 12.5(1)SU3 I found errors in RTMT indicating CDR records were no longer being sent to the existing RSI billing server.

There were no networking changes and all CUCM services were running so we started with restarting the Cisco CDR Repository Manager and Cisco CDR Agent because who knows, why not?  When that failed I quickly added another billing server and pointed it to a freeFTPd server I was using to serve Cisco upgrade media.  I immediately started receiving billing records there. With that there then didn't seem to be any issue with the CUCM record collection and transfer themselves but something unique to the old billing server.

Collect logs from CUCM publisher

I grabbed all the logs related to CDR from the Publisher via RTMT, used grepWin to dig through them to find references to the billing server IP address configured in CUCM and found: 

2020-12-29 05:47:57,549 INFO  [Thread-15] cdrrep.CDRSender (CDRSender.java:175) - There was file delivery failure for destination 2 in last round, re-connect server now!

2020-12-29 05:47:59,619 ERROR [Thread-15] sftpapi.SFTPConnection (SFTPConnection.java:319) - error Making SFTP connectionAlgorithm negotiation fail

2020-12-29 05:47:59,619 ERROR [Thread-15] sftpapi.ftpClient (ftpClient.java:246) - connect(): Failed connect to 10.10.10.10

The interesting bit there is Algorithm negotiation fail.

Some Googling turns up a bug CSCuz80145 indicating I may need to eliminate some CTR ciphers:

https://bst.cloudapps.cisco.com/bugsearch/bug/CSCuz80145

Of course my version is well beyond the affected and fixed versions so how it applies is questionable.  It does maybe mean there is some sort of issue with using the CTR ciphers.

The Administration Guide for Cisco Unified Communications Manager, Release 12.5(1)SU1 (which I honestly have never looked at) does indeed indicate that ONLY the CTR ciphers are supported in version 12 and beyond, at least for DRS purposes.

aes128-ctr, aes192-ctr and aes256-ctr supported

The RSI support representative the client contacted was in no mood to answer questions about what SFTP server was installed or how it was configured and simply suggested it was a CUCM issue. Cisco TAC was not going to respond anytime soon to confirm whether CDR transport had the same limitations as DRS.

How does one confirm what the SFTP servers support without their vendor support?

I fell upon a post by Kenneth Perry at https://nocthoughts.wordpress.com/2019/07/25/cdr-export-to-billing-server-failing/ describing a very similar issue discovered in a different fashion.  Given the date on his post and his SFTP server scans it seems he was working on an older CUCM version that did not support CTR ciphers, while my 12.5 version ONLY supported CTR ciphers.  Regardless, thanks Ken and whoever your colleague Mark is!

Although not my normal go-to tool, I did happen to have nmap installed on my laptop to prep for another "Cisco DevNet, DevOPs, CI/CD, automate everything and move it to the cloud training bootcamp" I sat through recently.

More info on the specific nmap script used and nmap in general is here: https://nmap.org/nsedoc/scripts/ssh2-enum-algos.html

Scanning the failing RSI as well as my successful test SFTP server returned what I suspected.  The old RSI billing server was not advertising the new CTR cipher support that the 12.5 admin guide indicated is required. The questionable but free and easy freeFTPd server showed it supported the CTR ciphers required by 12.5 as well as the old CBC list used by 11.5.

cbc only list on failing server

What to do?

RSI support did ultimately attempt to update their SFTP server to support the new ciphers but it didn't make a difference.  

Ultimately we put our fingers in our ears, flipped it all to FTP and called it a day.

Pretty cool regardless.

Update 02242022

Was recently tasked with configuring OpenSSH on a Windows Server 2016 (before it was offered as an installable Windows app / feature).
It was to serve as the target of multiple version 11.5/6 UC servers.

Notable configuration options used:

# Ciphers and keying specifically for 11.X but not newer
Ciphers aes128-cbc
KexAlgorithms diffie-hellman-group-exchange-sha1,diffie-hellman-group1-sha1

# To restrict access to a particular Windows local group, here called SFTPUsers
AllowGroups SFTPUsers