I have the below SQL query to retrieve the list of userid and their telephonenumber using Cisco CallManager AXL SQL Toolkit:
sql query="select d.name as deviceName, e.userid, e.telephonenumber from device d
left outer join endUser e on d.fkenduser=e.pkid where e.telephonenumber is not null"
How can I modify the above query to also retrieve the “Directory Number” of the returned results? I'm not sure what table and column to join!
The following query will return the line information:
SELECT d.name AS deviceName, e.userid, e.telephonenumber, n.dnorpattern AS directoryNumber
FROM device d
INNER JOIN enduser e on d.fkenduser=e.pkid
INNER JOIN devicenumplanmap dmap on dmap.fkdevice=d.pkid
INNER JOIN numplan n on dmap.fknumplan=n.pkid
I'm not sure if you know what you're asking for here... As this returns results based on the association of Owner UserId attribute on the phone (in d.fkenduser). It doesn't show the results of phones associated to end users. This is mapped in the enduserdevicemap table. It's not part of your original question, but mentioned for posterity.
Can't say for sure, but try to look here:
RUNNING SQL QUERIES ON CUCM 6X/7X USING AXL SOAP TOOLKIT – PART 1
In Query 4 we have two examples of how tables are linked by foreign keys. The deviceNumPlanMap table is one example of a table that actually joins values from multiple tables. We can “marry up” the relationship between a phone and a directory number using multiple joins:
inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid
inner join numplan as n on dmap.fknumplan = n.pkid
The numplan table is responsible for storing all digit patterns in the dial plan. For example, it stores all directory numbers programmed in the system. You may recall that directory numbers have call forwarding configurations.