cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
3400
Views
0
Helpful
3
Replies

SQL Query Help, Find End User and Device Name

mloraditch
Rising star
Rising star

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!

1 Accepted Solution

Accepted Solutions

Amine Nouasri
Participant
Participant

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(enduse