SQL Query Help, Find End User and Device Name

[mloraditch]

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!

[afmmanicke]

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

[Amine Nouasri · Accepted solution]

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!

[mloraditch]

Thanks Amine,

That works great!