CCM SQL Query - CallPickup group info for Director Numbers

Unanswered Question
Jan 30th, 2008

Hi,

The SQL query below (CCM 3.2) lists most of the information for UDP's except CallPickup Group info that is displayed in the format 'A3-A6B3-CA61DC5DBDF7' instead of the real group names. Is there any way to fix this using a join command or similar?


many thanks,

Stargazer



select

--UDP Profile:

dev.tkDeviceProfile as DP_or_Phone,

right(dev.name, 20) as UDP_Name,

right(dev.Description, 30) as Description,

right(dev.LoginUserID, 20) as Login_User_ID,

right(dev.AddOnModules, 20) as Expansion_Module,

--Associated Line:

right(num.DNOrPattern, 20) as Line_DN,

right(par.name, 20) as Line_Partition,

right(css.name, 20) as Line_CSS,

right(num.CFAdestination, 20) as Line_CFA_Dest,

right(num.CFBdestination, 20) as Line_CFB_Dest,

right(num.CFNAdestination, 20) as Line_CFNA_Dest,

right(num.ikNumPlan_CallPickupGroup, 20) as Call_Pickup_Grp,

right(dvn.Label, 30) as Line_Text_Label,

right(dvn.Display, 30) as Line_Display_CLID


from dbo.device as dev


inner join dbo.devicenumplanmap as dvn

on dev.pkid = dvn.fkDevice


inner join dbo.numplan as num

on dvn.fknumplan = num.pkid


inner join dbo.RoutePartition as par

on par.pkid = num.fkRoutePartition


inner join dbo.callingsearchspace as css

on css.pkid = num.fkCallingSearchSpace_SharedLineAppear


where dev.tkDeviceProfile = "2"

or

dev.tkDeviceProfile = "1"

-- "1" User UDP and is "2" is AutoGenerated UDP

and dev.AddOnModules = "0"

--and dev.Description != "%"


order by Line_DN


  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
therealstargazer Thu, 03/06/2008 - 10:20

This sql query extracts all Phone & UDP info from a CCM 3.2 - Very useful for CCM migration projects as there is no BAT export function in CCM 3.2.


select

-- Device

right(dev.tkDeviceProfile, 40) as 'Phone (0), UDP (1) or ADP (2)',

right(dev.name, 40) as 'MAC Address / UDP Name',

right(typ.name, 40) as 'Associated Phone Model',

right(ptl.name, 40) as 'Phone Template',

right(dev.Description, 40) as 'Device Description',

right(loc.name, 40) as 'Device Location',

right(dpl.name, 40) as 'Device Pool',

right(css_dev.name, 40) as 'Device CSS',

right(med.name, 40) as 'Media Resource List',

-- Line

right(dvn.numplanindex, 10) as 'Line_Index',

right(num.dnORPattern, 40) as 'Directory Number',

right(par.name, 40) as 'Line Partition',

right(css.name, 40) as 'Line CSS',

right(num.CFAdestination, 40) as 'Line CFA Dest',

right(num.CFBdestination, 40) as 'Line CFB Dest',

right(num.CFNAdestination, 40) as 'Line CFNA Dest',

right(dvn.Display, 40) as 'Line Display CLID',

right(dvn.Label, 40) as 'Line Text Label',

right(num2.dnORPattern, 40) as 'Line Pickup Group',

right(vmp.name, 40) as 'Voice Mail Profile',

right(dev.LoginUserID, 40) as 'Device Login UserID',

right(dev.AddOnModules, 20) as 'Expansion Module'


from dbo.numplan as num


left join dbo.devicenumplanmap as dvn

on num.pkid = dvn.fknumplan


left join dbo.device as dev

on dvn.fkdevice = dev.pkid


left join dbo.typemodel as typ

on dev.tkmodel = typ.enum


left join dbo.numplan as num2

on num2.pkid = num.iknumplan_callpickupgroup


left join dbo.RoutePartition as par

on par.pkid = num.fkRoutePartition


left join dbo.callingsearchspace as css

on css.pkid = num.fkCallingSearchSpace_SharedLineAppear


left join dbo.callingsearchspace as css_dev

on css_dev.pkid = dev.fkCallingSearchSpace


left join dbo.Location as loc

on loc.pkid = dev.fkLocation


left join dbo.devicepool as dpl

on dev.fkDevicePool = dpl.pkid


left join dbo.MediaResourceList as med

on dev.fkMediaResourceList = med.pkid


left join dbo.PhoneTemplate as ptl

on dev.fkPhoneTemplate = ptl.pkid


left join dbo.VoiceMessagingProfile as vmp

on num.fkVoiceMessagingProfile = vmp.pkid


WHERE

num.tkPatternUsage = 2

-- 2 are Directory Numbers


-- AND dev.tkDeviceProfile = 1

-- "0" are Physical phones and CTI Route Points

-- "1" are User UDP's

-- "2" are AutoGenerated UDP's


-- AND typ.name = "IP Phone 7940" OR

-- typ.name = "IP Phone 7960"

-- AND dvn.numplanindex = 1

-- Primary Extension


-- AND dvn.numplanindex = 2 OR dvn.numplanindex = 3 OR

-- dvn.numplanindex = 4 OR dvn.numplanindex = 5 OR dvn.numplanindex = 6

-- Line Indexes higher than 1 (to be imported using 'Add Lines' with BAT)


order by 'Line DN'


Actions

This Discussion