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'
