08-20-2013 08:59 AM - edited 03-16-2019 06:57 PM
In prepaparation for upgrading clients to 9.x I want to run some queries that will allow me to develop commands to mass update Owner User ID info on devices.
I have two queries that I need to run. One i've got down, the other not so much, I want to find the devicename that has a line 1 equal to a user's telephone number field.
I've found a query that is half-way there courtesy of Bill Bell:
select d.name
from device as d
inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid
inner join numplan as n on dmap.fknumplan=n.pkid
When I try to add enduser.userid as a select and then say:
where enduser.telephonenumber = dmap.dnorpattern
AND dmap.numplanindex = 1
CUCM barfs and gives me an error about an ON clause has an invalid table reference. I'm also not sure about the numplanindex. I've read conflicting info about whether index 1 is always line 1. I'm familiar with SQL in general but have never done joins before so I'm lost.
I would appreciate any assistance that could be provided.
Thanks!
Solved! Go to Solution.
08-22-2013 09:19 AM
Try the query below:
run sql select userid,firstname,lastname,telephonenumber,department,enduser.allowcticontrolflag cti,device.name,device.tkmodel, numplan.dnorpattern Primary from enduser left join enduserdevicemap on(enduser.pkid=enduserdevicemap.fkenduser) left join device on(enduserdevicemap.fkdevice=device.pkid) left join endusernumplanmap on(enduser.pkid=endusernumplanmap.fkenduser) left join numplan on(endusernumplanmap.fknumplan=numplan.pkid) where telephonenumber LIKE '1%' ORDER BY telephonenumber
In this example, telephonenumber LIKE '1%' will list users/devices with an extension starting with 1.
Please rate helpful answers!
08-22-2013 08:56 AM
I don't wanna be all "RTFM," but it could help you and ensure that there's not a table in the middle to break up a many-to-many relationship.
http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/8_0_1/datadictionary_801.pdf
I've work through quite a few queries in the past and it was also helpful to do a basic select * from ... to see the actual data and help me with it all.
TONY
08-22-2013 09:19 AM
Try the query below:
run sql select userid,firstname,lastname,telephonenumber,department,enduser.allowcticontrolflag cti,device.name,device.tkmodel, numplan.dnorpattern Primary from enduser left join enduserdevicemap on(enduser.pkid=enduserdevicemap.fkenduser) left join device on(enduserdevicemap.fkdevice=device.pkid) left join endusernumplanmap on(enduser.pkid=endusernumplanmap.fkenduser) left join numplan on(endusernumplanmap.fknumplan=numplan.pkid) where telephonenumber LIKE '1%' ORDER BY telephonenumber
In this example, telephonenumber LIKE '1%' will list users/devices with an extension starting with 1.
Please rate helpful answers!
08-22-2013 12:30 PM
Thanks Amine,
That works great!
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: