SQL CallManager 4.1 report

webstd.design · ‎04-14-2008

Who can help me with this scipt?

select

distinct

NumPlan.DNOrPattern as DirectoryNumber,

Device.Name as DeviceName,

Device.Description as UserDeviceProfileName,

Device.LoginUserid as UserId,

DeviceNumPlanMap.Display as DisplayIntCallerID,

DeviceNumPlanMap.NumPlanIndex as NumPlanIndex,

DeviceNumPlanMap.Label as LineTextLabel,

numplan.alertingname as AlertingName,

DeviceNumPlanMap.MaxNumCalls, DeviceNumPlanMap.BusyTrigger,

DeviceNumPlanMap.E164Mask as ExtPhoneMask,

NumPlan.CFADestination as

CallFWDALL, NumPlan.CFNADestination as CallFWDNoAnswer,

NumPlan.CFBDestination as CallFWDBusy,

devicepool.Name as DevPoolName from numplan, devicenumplanmap, devicepool, device ,TypeModel

where DeviceNumPlanMap.fknumplan = numplan.pkid

and DeviceNumPlanMap.fkdevice = device.pkid and device.fkdevicepool = devicepool.pkid and numplan.tkPatternUsage = 2

order by devicepool.Name ,numplan.DnOrPattern

I want to ass also CallingSearchSpace, but I couldn't find the description of CallingSearchSpaces tables. Here is only pkids. Who can help me this question?

Report Inappropriate Content · ‎04-18-2008

1. On the CCM server, go to Start---> Programs---> Microsoft SQL Server 7.0--->Enterprise Manager

2. Once in Enterprise Manager, expand SQL Server Group---> name of your CCM--->Databases---> the latest CCM030X database---> Tables

3. Once in the Tables database, right click on "CallingSearchSpace", go to Open Table, then Return all rows. This should open the Calling Search Space database and should list all the search spaces configured on CallManager

4. Back on the Tables database, right click on "Device" go to Open Table, then Return all rows. This should open the Device database and should list all the devices configured on CallManager. Scroll to the right until you see the "CallingSearchSpace" column. Look in this column for the device that still has the "pkid" of the calling search space

agiaccone · ‎04-18-2008

This one should work :

SELECT DISTINCT

TOP 100 PERCENT dbo.NumPlan.DNOrPattern AS DirectoryNumber, dbo.Device.Name AS DeviceName,

dbo.Device.Description AS UserDeviceProfileName, dbo.Device.LoginUserid AS UserId, dbo.DeviceNumPlanMap.Display AS DisplayIntCallerID,

dbo.DeviceNumPlanMap.NumPlanIndex AS NumPlanIndex, dbo.DeviceNumPlanMap.Label AS LineTextLabel,

dbo.NumPlan.AlertingName AS AlertingName, dbo.DeviceNumPlanMap.MaxNumCalls, dbo.DeviceNumPlanMap.BusyTrigger,

dbo.DeviceNumPlanMap.E164Mask AS ExtPhoneMask, dbo.NumPlan.CFADestination AS CallFWDALL,

dbo.NumPlan.CFNADestination AS CallFWDNoAnswer, dbo.NumPlan.CFBDestination AS CallFWDBusy, dbo.DevicePool.Name AS DevPoolName,

dbo.CallingSearchSpace.Name AS CSS

FROM dbo.NumPlan INNER JOIN

dbo.DeviceNumPlanMap ON dbo.NumPlan.pkid = dbo.DeviceNumPlanMap.fkNumPlan INNER JOIN

dbo.Device ON dbo.DeviceNumPlanMap.fkDevice = dbo.Device.pkid INNER JOIN

dbo.DevicePool ON dbo.Device.fkDevicePool = dbo.DevicePool.pkid LEFT OUTER JOIN

dbo.CallingSearchSpace ON dbo.Device.fkCallingSearchSpace = dbo.CallingSearchSpace.pkid CROSS JOIN

dbo.TypeModel

WHERE (dbo.NumPlan.tkPatternUsage = 2)

ORDER BY dbo.DevicePool.Name, dbo.NumPlan.DNOrPattern

The last column is the CSS.

HTH

Alberto