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

CUCM SQL: Device Profile without Line [1] Number

Hi Guys,

we're cleaning up our Callmanager environment right now and I wanna get rid of User Device Profiles which never have been deleted. Is there any way (my thought was via SQL) to at least show up the Device Profiles, who don't have a Line [1] number associated with it (the DNs already have been deleted but there still a lot of old Profiles laying around)

Thanks for a short reply

cheers

Marc

Everyone's tags (3)
2 ACCEPTED SOLUTIONS

Accepted Solutions

Maybe someone can do it

Maybe someone can do it easily with SQL but my thought is you could export the UDPs, filter for profiles that dont have Directory NUmber 1 in the header and use this list to delete the profiles.

Please rate useful posts.
Super Bronze

Try this: run sql select

Try this: 

run sql select device.name from device where not exists (select fkdevice from devicenumplanmap where devicenumplanmap.fkdevice = device.pkid) and device.tkclass = 254

... though George's answer is equally practical for non-SQL types (+5) :-)

Aaron

 

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
6 REPLIES

Maybe someone can do it

Maybe someone can do it easily with SQL but my thought is you could export the UDPs, filter for profiles that dont have Directory NUmber 1 in the header and use this list to delete the profiles.

Please rate useful posts.
New Member

You're right, thats just what

You're right, thats just what I've done. Bulk exported all of the Device Profiles in csv and sorted them in Excel. Think thats the easiest solution for this one.

Super Bronze

My answer works too though :-

My answer works too though :-)

 

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
New Member

Aaron, thanks for this SQL

Aaron, thanks for this SQL statement! Its help a lot. Do you (or anyone else) know of a way to see a time-stamp of the last time a user device profile was used? We are running into an issue where UDP's are associated to an end user, but that end user has changed job functions and no longer uses the UDP for Extension Mobility. So we have UDP's associated to an end user so it would theoretically be used, but they aren't. The query to find UDP's that are no longer associated to a user is below.

run sql select d.name from device d where (0=(select count(edm.pkid) from enduserdevicemap edm where edm.fkdevice=d.pkid)) and d.tkclass=254 order by d.name

Super Bronze

Try this: run sql select

Try this: 

run sql select device.name from device where not exists (select fkdevice from devicenumplanmap where devicenumplanmap.fkdevice = device.pkid) and device.tkclass = 254

... though George's answer is equally practical for non-SQL types (+5) :-)

Aaron

 

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
New Member

And thats the proper SQL

And thats the proper SQL statement which I was looking for. :D

 

Thanks a lot

474
Views
15
Helpful
6
Replies
CreatePlease to create content