CUCM SQL Query - phone details

[asandborgh]

Hi all,

We have just upgraded from CUCM 4.1 to 6.1.  With 4.1 we ran the following query to extract a phone report.  I am not an SQL guru so I was wondering if anyone could look at the syntax and tell us how it might be changed to run on 6.1?  If not, is there an SQL query for 6.1 that supplies the major details of all phones?

SELECT dbo_TypeModel.Name, dbo_Device.Name, dbo_Device.Description, dbo_DeviceNumPlanMap.Display, dbo_NumPlan.DNOrPattern, dbo_DeviceNumPlanMap.NumPlanIndex, dbo_CallingSearchSpace.Name

FROM ((dbo_NumPlan INNER JOIN ((dbo_TypeModel INNER JOIN dbo_Device ON dbo_TypeModel.Enum = dbo_Device.tkModel) INNER JOIN dbo_DeviceNumPlanMap ON dbo_Device.pkid = dbo_DeviceNumPlanMap.fkDevice) ON dbo_NumPlan.pkid = dbo_DeviceNumPlanMap.fkNumPlan) INNER JOIN dbo_CallingSearchSpace ON dbo_NumPlan.fkCallingSearchSpace_SharedLineAppear = dbo_CallingSearchSpace.pkid) INNER JOIN dbo_RoutePartition ON dbo_NumPlan.fkRoutePartition = dbo_RoutePartition.pkid

WHERE (((dbo_TypeModel.Name) Like 'Cisco IP Communicator'))

ORDER BY dbo_NumPlan.DNOrPattern;

Many thanks in advance!

Art

[Tooth Fairy]

Hi Art,

SQL isn't my specialty either.  Perhaps this would be an easier way to get the information? (Sorry, not free ) :

[joergwesely]

Could you post some sample output? Data from an old report to see what should come out?

Sent from Cisco Technical Support iPad App

[Jesse Drohen]

Hello Art,

There are a lot of changes between those two versions. The Cisco recommended way is to get this information through an AXL SOAP query now.

You can run this to get a dump from the device table
run sql select * from device

If you only wanted information with SEP in the name, usually limited to phones you could run this
run sql select * from device where name like 'SEP%'

From there you can look at the column names and only return what information you want and replace the * with them. Something like this
run sql select name,description,tkmodel from device where name like 'SEP%'

Your query was doing some joins across tables, a little beyond my expertise but this should get you started.

Hope this helps,
Jesse

[asandborgh]

Thanks Jesse - good information.  We will try this and see where it leads.

[asandborgh]

I will try and get a sample outpu and attach it to this string - stay tuned

[daveaton]

You might try this:

Search for "Cisco 7942" phone type

run sql SELECT TypeModel.Name, Device.Name, Device.Description, DeviceNumPlanMap.Display, NumPlan.DNOrPattern, DeviceNumPlanMap.NumPlanIndex,  CallingSearchSpace.Name  FROM NumPlan INNER JOIN TypeModel INNER JOIN Device ON TypeModel.Enum = Device.tkModel INNER JOIN DeviceNumPlanMap ON Device.pkid = DeviceNumPlanMap.fkDevice ON NumPlan.pkid = DeviceNumPlanMap.fkNumPlan INNER JOIN CallingSearchSpace ON NumPlan.fkCallingSearchSpace_SharedLineAppear = CallingSearchSpace.pkid INNER JOIN RoutePartition ON NumPlan.fkRoutePartition = RoutePartition.pkid WHERE TypeModel.Name Like 'Cisco 7942' ORDER BY NumPlan.DNOrPattern

Search not limited to phone type

run sql SELECT TypeModel.Name, Device.Name, Device.Description,  DeviceNumPlanMap.Display, NumPlan.DNOrPattern,  DeviceNumPlanMap.NumPlanIndex,  CallingSearchSpace.Name  FROM NumPlan  INNER JOIN TypeModel INNER JOIN Device ON TypeModel.Enum =  Device.tkModel INNER JOIN DeviceNumPlanMap ON Device.pkid =  DeviceNumPlanMap.fkDevice ON NumPlan.pkid = DeviceNumPlanMap.fkNumPlan  INNER JOIN CallingSearchSpace ON  NumPlan.fkCallingSearchSpace_SharedLineAppear = CallingSearchSpace.pkid  INNER JOIN RoutePartition ON NumPlan.fkRoutePartition =  RoutePartition.pkid ORDER BY NumPlan.DNOrPattern

Hope this helps.

[alexver1978]

Hi, could you please help me to write query to fined ip address of ip phones in CUCM?

[Anthony Holloway]

That's not possible actually, since the IP Address is not part of the configuration. Rather, you can find it in the command output of "show risdb query phone" however, you have to run it on each CUCM server, and each CUCM server could *potentially* show conflicting data, in which case you'll need to look at the date/time column in the output to see which server has the latest info for that phone.

This is true even if you use the Serviceability API and script a solution.

Alternatively, you could write a Python script to scrape the CUCM web page and make the IP list from what is seen on the page.