UCM Informix Query for Device

[dgauthier]

All,

I am trying to align a device, e.g. IP Phone with the UCM Node in which the device registers to.  My version of UCM is 9.1.2.  I have used the following syntax to obtain various information such as EndUser, Class, Model, etc.  which works fine.

run sql \
SELECT g.name, g.a6 AS Street, g.a2 AS County, g.a3 AS City, g.pc AS ZipCode, g.flr AS Floor, \
eu.userid, eu.lastname, eu.firstname, eu.department, eu.manager, eu.telephonenumber, \
d.Name AS Device, \
tc.name AS Class, \
tm.name AS Model, \
np.dnorpattern AS DN \
FROM Device d \
LEFT JOIN typeclass AS tc ON d.tkclass = tc.enum \
LEFT JOIN typemodel AS tm ON d.tkmodel = tm.enum \
LEFT JOIN EndUser AS eu ON d.fkEndUser = eu.pkid \
LEFT JOIN DeviceNumPlanMap AS dnpm ON dnpm.fkdevice = d.pkid \
LEFT JOIN NumPlan AS np ON dnpm.fknumplan = np.pkid \
LEFT JOIN geolocation AS g ON g.pkid = d.fkgeolocation \
WHERE eu.department = 'Data Processing'

Next, I am simply trying to add the 'processnode' in which the device registers to by the following. 

run sql \
SELECT g.name, g.a6 AS Street, g.a2 AS County, g.a3 AS City, g.pc AS ZipCode, g.flr AS Floor, \
eu.userid, eu.lastname, eu.firstname, eu.department, eu.manager, eu.telephonenumber, \
d.Name AS Device, \
tc.name AS Class, \
tm.name AS Model, \
np.dnorpattern AS DN, \
pn.name AS ProcNodeName, pn.nodeid AS ProcNodeId \
FROM Device d \
LEFT JOIN typeclass AS tc ON d.tkclass = tc.enum \
LEFT JOIN typemodel AS tm ON d.tkmodel = tm.enum \
LEFT JOIN EndUser AS eu ON d.fkEndUser = eu.pkid \
LEFT JOIN DeviceNumPlanMap AS dnpm ON dnpm.fkdevice = d.pkid \
LEFT JOIN NumPlan AS np ON dnpm.fknumplan = np.pkid \
LEFT JOIN geolocation AS g ON g.pkid = d.fkgeolocation \
LEFT JOIN processnode AS pn ON pn.pkid = d.fkprocessnode \

What I am am finding is 'NULL' is the typical response for the field 'processnode' for a device that is registered to a give UCM Node.  The only match that I do get is MTP or CONF or ANN type of device...but no phones...

I am looking for any feedback since the LEFT JOIN processnode is a simple foreign key, 'fkprocessnode' from the device table from the UCM data dictionary...

[Aditya Gupta · Accepted solution]

Hi 

The reason why you are not getting information on UCM registration node for device  is because this info is never stored in the informix. 

Instead you need to query risdb on cucm to get the information. Use the following command and you can the registered node info for the device :

show risdb query phone

If you are running this query on a cluster with lots of phones, i will advice to run during minimal load since query is going to get you lots of information from CUCM.

Below is the sample output from my lab (2 CUCM-pub and sub and 2 phones):

Scenrio 1:

Both phones Registered to Pub

Publisher

admin:show risdb query phone
----------- Phone Information -----------

#TotalPhones, #TotalRegistered, #RegisteredSCCP, #RegisteredSIP, #UnRegistered, #Rejected, #PartiallyRegistered, StateId, #ExpUnreg
2, 2, 1, 1, 0, 0, 0, 7,0

DeviceName, Descr, Ipaddr, Ipv6addr, Ipv4Attr, Ipv6Attr, MACaddr, RegStatus, PhoneProtocol, DeviceModel, HTTPsupport, #regAttempts, prodId, username, seq#, RegStatusChg TimeStamp, IpAddrType, LoadId, ActiveLoadId, InactiveLoadId, ReqLoadId, DnldServer, DnldStatus, DnldFailReason, LastActTimeStamp, Perfmon Object
SEP00059A3C7A00, SEP00059A3C7A00, 10.65.49.186, , 3, 0, 00059A3C7A00, reg, SCCP, 30016, yes, 2, 30041, NoUserId, 1, 1427944494, 1, , CIPC-8-6-4-0, , , , 0, , 1427944494, 2
SEP001D4526E778, SEP001D4526E778, 10.88.85.231, , 0, 0, 001D4526E778, reg, SIP, 478, yes, 0, 365, NoUserId, 2, 1427472713, 1, CTS.1-10-10-13R-K9.P1, CTS_1.10.10(13)_P1, , , , 0, , 1427472713, 2

----------------
Total count 2
----------------

Subscriber

admin:show risdb query phone
----------- Phone Information -----------

#TotalPhones, #TotalRegistered, #RegisteredSCCP, #RegisteredSIP, #UnRegistered, #Rejected, #PartiallyRegistered, StateId, #ExpUnreg
0, 0, 0, 0, 0, 0, 0, 0,0

DeviceName, Descr, Ipaddr, Ipv6addr, Ipv4Attr, Ipv6Attr, MACaddr, RegStatus, PhoneProtocol, DeviceModel, HTTPsupport, #regAttempts, prodId, username, seq#, RegStatusChg TimeStamp, IpAddrType, LoadId, ActiveLoadId, InactiveLoadId, ReqLoadId, DnldServer, DnldStatus, DnldFailReason, LastActTimeStamp, Perfmon Object

----------------
Total count 0
----------------

Scenario 2

One phone moved from publisher to subscriber

Publisher

admin:show risdb query phone
----------- Phone Information -----------

#TotalPhones, #TotalRegistered, #RegisteredSCCP, #RegisteredSIP, #UnRegistered, #Rejected, #PartiallyRegistered, StateId, #ExpUnreg
2, 1, 0, 1, 1, 0, 0, 8,0

DeviceName, Descr, Ipaddr, Ipv6addr, Ipv4Attr, Ipv6Attr, MACaddr, RegStatus, PhoneProtocol, DeviceModel, HTTPsupport, #regAttempts, prodId, username, seq#, RegStatusChg TimeStamp, IpAddrType, LoadId, ActiveLoadId, InactiveLoadId, ReqLoadId, DnldServer, DnldStatus, DnldFailReason, LastActTimeStamp, Perfmon Object
SEP00059A3C7A00, SEP00059A3C7A00, 10.65.49.186, , 3, 0, 00059A3C7A00, unr, SCCP, 30016, yes, 2, 30041, NoUserId, 1, 1427945627, 1, , CIPC-8-6-4-0, , , , 0, , 1427945627, 2
SEP001D4526E778, SEP001D4526E778, 10.88.85.231, , 0, 0, 001D4526E778, reg, SIP, 478, yes, 0, 365, NoUserId, 2, 1427472713, 1, CTS.1-10-10-13R-K9.P1, CTS_1.10.10(13)_P1, , , , 0, , 1427472713, 2

----------------
Total count 2
----------------

Subscriber

admin:show risdb query phone
----------- Phone Information -----------

#TotalPhones, #TotalRegistered, #RegisteredSCCP, #RegisteredSIP, #UnRegistered, #Rejected, #PartiallyRegistered, StateId, #ExpUnreg
1, 1, 1, 0, 0, 0, 0, 3,0

DeviceName, Descr, Ipaddr, Ipv6addr, Ipv4Attr, Ipv6Attr, MACaddr, RegStatus, PhoneProtocol, DeviceModel, HTTPsupport, #regAttempts, prodId, username, seq#, RegStatusChg TimeStamp, IpAddrType, LoadId, ActiveLoadId, InactiveLoadId, ReqLoadId, DnldServer, DnldStatus, DnldFailReason, LastActTimeStamp, Perfmon Object
SEP00059A3C7A00, SEP00059A3C7A00, 10.65.49.186, , 3, 0, 00059A3C7A00, reg, SCCP, 30016, yes, 2, 30041, NoUserId, 1, 1427945662, 1, , CIPC-8-6-4-0, , , , 0, , 1427945662, 2

----------------
Total count 1
----------------

In scenario 2 , RegStatus of phone 1 shows as unr (unregistered) on pub.

You can use this as a filter to sort out the registered and un-registered phones on a given node

Hope this helps.

Regards

Aditya Gupta

[Gordon Ross]

As Aditya says, if you want to see what node a device is currently registered to, you have to use the RISDB. As an appendix to Aditya's answer, if you use the RIS API set, you can ask the publisher what server a specified device is registered to, rather than getting all the devices registered to a server.

However, if you want to find out what nodes a device is configured to register to, then, as you would in CCMAdmin, you need to go via the DevicePool to the CallManager Group.

GTG