Which request should I use in AXL 8.0 to obtain a list of all ip phone devices and the associated primary phone number?
something like
device-name1, 4-digit-ext1
device-name2, 4-digit-ext2

David, thanks for detailed response. executeSQLQuery approach seems much better. I tried and I got SoapException (using C#):
Unhandled Exception: System.Web.Services.Protocols.SoapException: Column (fknumplan) not found in any table in the query (or SLV is undefined).

We’re on 8.0; I checked the data dictionary and there is no column fknumplan in device table. Any suggestions?

Just taking a guess at what you are trying to do:

The device and numplan tables are related via the devicenumplanmap table. The relationship is usually something like this:

Device.pkid = devicenumplanmap.fkdevice
Numplan.pkid = devicenumplanmap.fknumplan

Dennis Heim | Sr. UC Engineer
World Wide Technology | 314.212.1814 | dennis.heim@wwt.com<mailto:dennis.heim@wwt.com>
“Creating Impact, Ignition & Scalability”

From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Wednesday, October 17, 2012 11:15 AM
To: cdicuser@developer.cisco.com
Subject: New Message from Shivinder Singh in Administration XML (AXL) - Administration XML Questions: RE: New Message from David Staudt in Administration XML (AXL) - Administrat

Shivinder Singh has created a new message in the forum "Administration XML Questions": -------------------------------------------------------------- David, thanks for detailed response. executeSQLQuery approach seems much better. I tried and I got SoapException (using C#):
Unhandled Exception: System.Web.Services.Protocols.SoapException: Column (fknumplan) not found in any table in the query (or SLV is undefined).

We’re on 8.0; I checked the data dictionary and there is no column fknumplan in device table. Any suggestions?
--
To respond to this post, please click the following link: http://developer.cisco.com/web/axl/forums/-/message_boards/view_message/7746348 or simply reply to this email.

If you know where to expect the lines to appear on the device you could use the numplanindex field in devicenumplanmap 'WHERE devicenumplanmap.numplanindex = '1''.  But you might want to include the typepatternusage table as well to make sure you're returning results for numbers assigned to devices and not translation patterns or such.  You could add 'numplan.tkpatternusage = '2'' for that. 
HTH

You could query the user for it's IPCC extension, this is stored in endusernumplanmap (tkdnusage = '2')

yeah, these are the primary extension and iopcc extension that you see on the enduser page ;
 


run sql select E.userid,N.dnorpattern,NM.tkdnusage from numplan N join endusernumplanmap NM on (NM.fknumplan = N.pkid) join enduser E on (E.pkid = NM.fkenduser) order by E.userid,NM.tkdnusage
userid     dnorpattern tkdnusage 
========== =========== ========= 
agent21    8021        1         
agent21    8021        2         
agent22    8022        1         
agent22    8022        2   

 
I only have 1 line on the phone, but if you had a 2nd line for UCCX, you would see that extn in the row with tkdnusage = 2

Roadblock! It seems, like David mentioned earlier, the system is somehow limiting the number of rows returned. I’ve tried issuing multiple queries to get ranges of phone numbers in a for loop, for e.g.,
SQL…”where enduser.phonenumber between 2000 and 3000”

But this does not give expected results. I only get like 60 rows for this range. I’m expecting around 150 rows.

I then tried getting ranges in even smaller increments of 50 like this ”where enduser.phonenumber between 2150 and 2200” and then I get even lesser number, like 8 rows

We have a total of around 550 devices, and the data I’m querying should be extremely small and should ideally be returned in a single query. What else can I try?

If you're referring to the User 'Telephone Number' field, then that's true: it's basically an arbitrary field that may or not be present, or may or not accurately reflect actual user->line relationships in UCM.  The field is typically entered manually by the admin or auto-synced from an LDAP system.

I found this equivalent syntax in Informix, but still get syntax error when run via CLI or AXL’s executeSQLQuery:

select count(*) from enduser into temp temp_endusercount select * from temp_endusercount

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin365.htm


Regards,

Shivinder Singh * Programmer III, Computer Services * Lamar Institute of Technology * Beaumont, Texas * Phone: 409 839 2097 * ssingh@lit.edu<mailto:ssingh@lit.edu> * http://www.lit.edu<http://www.lit.edu/>


From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Thursday, October 18, 2012 4:55 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Shivinder Singh in Administration XML (AXL) - Administration XML Questions: RE: New Message from David Staudt in Administration XML (AXL) - Administrat

Shivinder Singh has created a new message in the forum "Administration XML Questions": -------------------------------------------------------------- Looks like Informix does not like this syntax:

with tsource as (select count(*) from enduser) select * from tsource

CLI says syntax error.

But this syntax works fine on Oracle. I need this syntax to get the desired output in original post. Is there any equivalent in Informix?
--
To respond to this post, please click the following link: http://developer.cisco.com/web/axl/forums/-/message_boards/view_message/7823743 or simply reply to this email.