Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

Silver

CUCM SQL Query - phone details

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


6 REPLIES
New Member

Re: CUCM SQL Query - phone details

Hi Art,

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

New Member

Re: CUCM SQL Query - phone details

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

Sent from Cisco Technical Support iPad App

Cisco Employee

Re: CUCM SQL Query - phone details

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

Silver

Re: CUCM SQL Query - phone details

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

Silver

Re: CUCM SQL Query - phone details

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

Cisco Employee

Re: CUCM SQL Query - phone details

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.

2774
Views
0
Helpful
6
Replies