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

CCM SQL Query - CallPickup group info for Director Numbers

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

2 REPLIES
New Member

Re: CCM SQL Query - CallPickup group info for Director Numbers

Try Bug - CSCdx69386

New Member

Re: CCM SQL Query - CallPickup group info for Director Numbers

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'

191
Views
0
Helpful
2
Replies
CreatePlease to create content