I did a post not too long ago on the vlookup function and how it can be used to extract data by matching on a row and returning data x number of columns away. The same exists using the hlookup command to match on a column and return data x number of rows away.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The use case for this is simple: you’re doing a parallel upgrade of Call Manager using the import/export method and you want to copy over all the tables that exist in the old database, but if they don’t exist, you still need the table headers. To do this you’ll need the old phone.csv and a blank export of the new phone.csv side by side.

You’ll paste this on the new phone.csv in A2 and drag to the right and down to populate the database.

=hlookup(a$1,(old-phone!$a$1:$dyt$5000),(row(a2)),false)

When you’re done you should copy all values to a new sheet to clean up any #N/A values it may create and thats your new phone.csv

A lot of apartment buildings have callboxes that will dial a tenant and allow the tenant to open the door by pressing a key sequence. I have had to use this to call myself before when I left my keys upstairs, or that time I lost my keys for a week (but thats another story). So what happens if you don’t have your phone or your keys? Or what about when you have guests that arrive early and you’re still in the shower, getting ready or can’t get to your phone in time?

In this example, the callbox dials Tropo instead of your phone, uses the built-in IVR to ask for a password. If the password is correct, it dials the dtmf digits to open the door and sends a text message to your phone alerting you of company.

var
result = ask(“The password is?”, {
choices: “sausages”
});
if(result.value == “sausages”){
say(“that is correct”);
say(“https://evolution.voxeo.com/library/audio/prompts/dtmf/Dtmf-9.wav”);
message(“A Guest has Arrived”, {
to:”+19192223323″,
network:”SMS”
});
}
else{
say(“there is a nice bench outside for you to sleep on”);
}

I recently posted about the Jabber Problem Report Tool and the csf-unified.log that turned out to be one of my most popular posts. I had some feedback asking for more info on specific troubleshooting after home cluster is found, so here it is.

Service Discovery Failure Codes (csf-unified.log)

[table id=2 /]

Service Discovery Cache File (service-location.xml)

Located in %appdata%\Cisco\Unified Communications\Jabber\CSF\Config

<?xml version=”1.0″?>
<UCServices>
<DomainName>pnslabs.com</DomainName>
<UCService>
<type>CUCM</type>
<connectionInformation>
<name>_cisco-uds</name>
<scope>UNKNOWN</scope>
<address>cucm.pnslabs.com</address>
<protocol>_tcp</protocol>
<port>8443</port>
</connectionInformation>
</UCService>
</UCServices>

IMS Result Codes

Client Profile Agent Logs pulled from IM&P Server EPASSoapXXXXX.log
[table id=3 /]

Anyone who has deployed Jabber knows of the cisco-uds SRV record that Jabber uses (or Expressway-C in the case of MRA) to discover it’s services. It’s also used for directory searches, and home cluster lookup with ILS.

UDS or User Data Services is a simple REST based API for CUCM. While the UDS API is not as extensive as what you can do with AXL through SOAP and WSDL, it makes a great use case for a front end to allow end users to manage their own devices: Change their single number reach destination, password, speed dials, and conference pins.

For security reasons, most browsers will block an XMLHttpRequest served on one page and originating on another (in the case of a front end making UDS calls to CUCM). To get around this, you would use CORS or Cross Origin Resource Sharing, defining the front end URL inside CUCM.

UDS supports REST queries POST/PUT/GET/DELETE in XML format. Some calls do not require authentication and some do. Ones that do will use Basic authentication built into the browser which encodes the username:password in Base64 format.

UDS resources that do not require authentication
clusterUser, installedLocales, models, options, phoneService(s), servers, timezones, users, version

GET https://{host}:8443/cucm-uds/users?last=Smit
GET https://{host}:8443/cucm-uds/clusterUser?username={userId}

UDS resources that require authentication
credentials, device(s), extension(s), remoteDestination(s), speedDial(s), subscribedService(s), user, userPolicy

POST https:­//{host}:8443/cucm-uds/user/{userId}/speedDials
<!–add speedDials example request body–>
<speedDials>
<speedDial>
<index>1</index>
<number>1234567890</number>
<label>Manager</label>
</speedDial>
<speedDial>
<index>2</index>
<number>1234567899</number>
<label>Assistant</label>
</speedDial>
</speedDials>

CUCM UDS API Reference
CUCM UDS Developer Guide
CUCM UDS Authentication Guide
Base64 Encoder/Decoder

You’ve probably heard a lot of buzzwords lately: API, JSON, and REST among them, however truth be told, REST or Representational State Transfer has always been around as the “language of the internet” (a REST HTTP GET brought you this page!), so it goes without saying that in the age of Internet of Things, REST would become infinitely more important.

REST is made up of 4 things

URI: https://api.ciscospark.com/v1/rooms
Request Type: POST, GET, PUT, etc
Header: Authorization (API key), Content-Type (html, application/xml, application/json)
Body: { “function” : “sendMessage”, “message” : “this is a message”}

JSON is the preferred format over XML because its more efficient. With PHP methods json_encode() and json_decode() for instance, it’s very easy to parse arrays into JSON format and vis a visa.

One of the great things about REST is that when a call is made, it sends a response, which can then in turn be a variable in a second response and so on. It’s not client-server, it’s a conversation.

rest-api

Check out my demo’s for Spark API and Tropo API for a full writeup of how to write some simple REST calls using an html form and some PHP.

Cisco has a good writeup on CUC SQL queries, which are very useful to determine which voicemail accounts are active and which are just taking up a license, and perhaps could be skipped over during migrations. To get everything you need, use the query below:

lists all message counts, durations, size, and dates per alias:

run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as TotalMessages, sum(case when deleted=’0′ then 1 else 0 end) as Inbox, sum(case when deleted=’1′ then 1 else 0 end) as Deleted,  min (arrivaltime) as OldestMessageTime, vw_mailbox.bytesize, sum(duration/1000) as TotalDuration_In_sec from vw_message,vw_mailbox, unitydirdb:vw_mailbox, unitydirdb:vw_user where vw_message.mailboxobjectid=vw_mailbox.mailboxobjectid and vw_mailbox.mailboxobjectid in (select mailboxid from vw_mailbox where unitydirdb:vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) group by alias, vw_mailbox.bytesize order by TotalMessages desc

MRA (Mobile and Remote Access) is a technology that allows phone registrations without raw sip signaling sent over the internet. I compare it to RPC over HTTPS proxy, aka “Outlook Anywhere.” Email came first, now I’m not sure if anyone remembers having to VPN in for Outlook to work.

After having several MRA projects back to back, I’ve found that most of the lessons I’ve learned were dealing with project management than the technical pieces. No other piece of hardware or software requires so many different groups of people: the datacenter guy, the firewall guy, the certificate guy, the dns guy, the voice guy. Throw in a business analyst and a project manager and it gets messy. I literally had projects ongoing for months and months when I was able to get MRA working in my lab in just a couple hours. Anytime I finish a project I look at how it could’ve been done better, so here it is:

First: It’s better to think of MRA as 5 separate, independent tasks because almost nothing depends on anything else.

  1. VM Deployment
  2. Networking
  3. Certificates
  4. DNS
  5. Firewall

Second: Clearly define roles and responsibilities per task at the onset of the project. The certificate guy doesn’t care about ip addresses or storage so just gather contact information and give role takers hyper-specific information to keep their attention. Limit emails.

Third: Make the configuration as cookie-cutter as possible. I wouldn’t even mention single NIC with some nat reflexion workaround, you use the advanced networking key with an outside and an inside interface. Public Certs on everything. It’s always a cluster, even if its a cluster of one; you never know when they will scale up. I assume inside out, everything is allowed through the firewall; this is usually the case and customers get confused with the lengthy cisco documentation. I usually give something like below:

Purpose

Protocol

Internet Endpoint (source)

Expressway-E (listening)

XMPP

TCP

>=1024

5222

XMPP federation

TCP

>=1024

5269

HTTP proxy (UDS)

TCP

>=1024

8443

SIP media

UDP

>=1024

36002 to 59999

SIP signaling

TLS

>=1024

5061

HTTPS (Unity)

TCP

>=1024

443

Also, I posted before about Flexible Jabber ID, which allows for multiple presence domains. That can cause holdups if you aren’t allowed to sign for these domains for your cup-xmpp certificate. It becomes an all-or-nothing service model. The best thing to do is get ahead of this and remove services from domains that you cannot sign for! You can run a whois lookup to ensure the technical contact has some relationship with the customer.

For a list of all jabber users and their last login time, you can run the following query:


run sql
select e.userid, cd.timelastaccessed from enduser as e, credentialdynamic as cd, credential as cr where e.pkid=cr.fkenduser and e.tkuserprofile=1 and e.primarynodeid is not null and cr.tkcredential=3 and cr.pkid=cd.fkcredential order by cd.timelastaccessed

This date is in something called “epoch” format. To convert to something that makes sense, you’ll need to use the following formula:

=(((B2/60)/60)/24)+DATE(1970;1;1)+(-5/24)

This is for Eastern Standard Time where -5 = UTC -5, you can change this accordingly.

expresswayc-e

There are a number of deployment options for Expressway depending on your customers environment, which could lead you to having to come up with creative solutions. This is called engineering.

We know external services need to be discovered using “outside” dns, however what happens when a customer uses a single dns server for outside and inside networks? Well, The traversal zone will need to be a FQDN for TLS validation, so it needs to use dns to resolve the inside addresses, but clients on the public internet need to use dns to resolve the outside address of the Expressway E.

expc-mra_-_Edit_zone

Tandberg VCS aka Cisco Expressway runs on linux and uses a lightweight network services package called dnsmasq

Being a linux guy, the first thing I tried was editing the /etc/hosts file, but to prevent hacking, everything is on a read-only filesystem except for the /tandberg mount, which also had an etc but changes were not persistent. After poking around I found the dnsmasq.conf man pages here and an option to specify a “conf-dir” to include configuration files that are loaded on start.

For static A records, create a file called hosts.conf

/tandberg/etc/dnsmasq.conf.d/hosts.conf

address=/expe-mra.car.pnslabs.com/10.20.30.40

For static SRV records, create a file called srv.conf

/tandberg/etc/dnsmasq.conf.d/srv.conf

srv-host=_cisco-uds._tcp.car.pnslabs.com.,cucm.car.pnslabs.com.,8443,100,0

After adding the file, you’ll need to restart dnsmasq:

/etc/init.d/dnsmasq restart

 

Unlike other “report this problem” buttons, Jabber’s problem report doesn’t just fly into the cloud somewhere never to be seen again.

It actually contains a lot of useful information about things like: how services are discovered and assigned, what policies are defined, what devices are configured, etc. The problem report can be generated, but is located below:

\Users\AppData\Local\Cisco\Unified Communications\Jabber\CSF\Logs\csf-unified.log

One of the biggest questions I get is where configurations exist, and what takes priority ? Before looking at the file, I would have guessed 3 places, but as you can see below its actually 12!

1: TftpOverrideConfigStore [10] jabber-config.xml
2: LocalFileConfigStore [20] AppData\Roaming\Cisco\Unified Communications\Jabber\CSF\Config\jabberLocalConfig.xml
3: Ucm90ConfigStore [25] http://cucm.fqdn.com:6970/SPDefault.cnf.xml
4: PresenceAuthenticatorStore [28]
5: ServiceDiscoveryConfigStore [29]
6: PresenceConfigStore [30] Presence Server TFTP+CCMCIP Settings
7: RegistryConfigStore [31] HKCU\Software\Cisco Systems, Inc.\JabberWerxCpp
8: PresenceProductModeStore [35]
9: UrlProvisioningConfigStore [499]
10: BootstrapConfigStore [500] ProgramData\Cisco Systems\Cisco Jabber\jabber-bootstrap.properties
11: InMemoryConfigStore [600]
12: jabber-config-defaults.xml [2147483647] Program Files (x86)\Cisco Systems\Cisco Jabber\jabber-config-defaults.xml

Each of these stores contain configuration elements in the form of <tags>, retrieved from either the local machine, or through HTTP GET requests

When Jabber is launched, Jabber first tries to read jabberLocalConfig.xml for cached settings, if any exists. It then attempts to reach the domain controller of the machine you are running on for SSO. If both of those fail, only then will it give you a login prompt. If no cached username is found, email is prompted for and DISCOVERY begins.

retrieve email address and parse domain:

Getting LastLoggedInUserName from ConfigFeatureSet
Getting servicesDomain from ConfigFeatureSet
Getting PresenceDomain from ConfigFeatureSet
ServicesDomain key not found, parsing email address.
Retrieved Email Address jhalpert@car.pnslabs.com
Retrieved DNS Domain ‘car.pnslabs.com’ from ‘Email Address from UserInput’
Domain is set to: car.pnslabs.com

check for webex im:

makeCasLookupForDomain result is ‘Code: NOT_WEBEX_CUSTOMER; CasUrl: http://loginp.webexconnect.com/cas/FederatedSSO?org=car.pnslabs.com;

check SRV records for on-prem and mra:

*—–* DNS query _cisco-uds._tcp.car.pnslabs.com. has succeeded.
*—–* DNS query _collab-edge._tls.car.pnslabs.com. has failed: DNS name does not exist.

determine home cluster:

*—–* Making HTTP request to: https://cucm.car.pnslabs.com:8443/cucm-uds/clusterUser?email=jhalpert@car.pnslabs.com

fetch user data:

https://cucm.car.pnslabs.com:8443/cucm-uds/user/jhalpert
https://cucm.car.pnslabs.com:8443/cucm-uds/user/jhalpert/devices
https://cucm.car.pnslabs.com:8443/cucm-uds/user/jhalpert/credentials
https://cucm.car.pnslabs.com:8443/cucm-uds/user/jhalpert/extensions/
https://cucm.car.pnslabs.com:8443/cucm-uds/user/jhalpert/subscribedServices
https://cucm.car.pnslabs.com:8443/cucm-uds/user/jhalpert/speedDials
https://cucm.car.pnslabs.com:6970/SPDefault.cnf.xml
https://cucm.car.pnslabs.com:6970/global-settings.xml

set location:

setCurrentLocationOption start: description: Jeffs House ,id: fff8ffb0-1332-4da7-ba78-647b35aa25b2
IsGatewayAddressSubnetExists: gateway address: 00::50::56::E3::E5::50, subnet: 172.16.13.0, locations: 1