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

SQL Query Help, Find End User and Device Name

mloraditch
Level 7
Level 7

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
Level 3
Level 3

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!

View solution in original post

3 Replies 3

afmmanicke
Level 1
Level 1

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
Level 3
Level 3

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!

Thanks Amine,

That works great!

Getting Started

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: