cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements

2546
Views
0
Helpful
3
Replies
Highlighted
Contributor

SQL Query Help, Find End User and Device Name

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!

Everyone's tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Participant

SQL Query Help, Find End User and Device Name

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
Highlighted
Beginner

SQL Query Help, Find End User and Device Name

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

Highlighted
Participant

SQL Query Help, Find End User and Device Name

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

Highlighted
Contributor

SQL Query Help, Find End User and Device Name

Thanks Amine,

That works great!

CreatePlease to create content
Content for Community-Ad
Future of Work Virtual Summit Day 5

Cisco COVID-19 Survey