CUCM 4.13 - Need SQL to get all End User Account userids

Version 1
    This document was generated from CDN thread

    Created by: Keith Lunn on 29-07-2009 11:29:34 PM
    Hello Folks!
    I have a script that gets all all End user account userids that begin with a digit in CUCM 6.13: (executeSQLQuery)
    SELECT userid, firstname, lastname FROM enduser WHERE userid MATCHES '[1-9]*'

    Now I need to do the same ting on one of our old CUCM 4.13 servers but the table "enduser" does not exist in 4.13 and I cannot find any Data Dictionary document for 4.13 either.
     
    Can someone tell me what SQL I can use in 4.13 to accomplish the same?
    Can someone point me to a Data Dictionary doc for 4.13?
     
    Thanks!!!

    Subject: RE: CUCM 4.13 - Need SQL to get all End User Account userids
    Replied by: Keith Lunn on 30-07-2009 02:07:39 PM
    OK, I have part of my answer! Users are NOT stored in the SQL Server database in 4.13 - they are stored in the DC Directory.
    So you have to use the listUserByName API and include throttlening  - I discovered on my 4.13 systems they returned a max of 500 records and then bombed with <SOAP-ENV:Body>
      <axl:listUserByNameResponse xmlns:axl="http://www.cisco.com/AXL/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.cisco.com/AXL/API/1.0 http://CMPOPP1/CCMApi/AXL/V1/axlsoap.xsd" sequence="400">
       <return>
        <ErrorMsg>Search result exceeded the size limit. Displaying truncated result</ErrorMsg>.
    I had to code a search for <lastname>A*<lastname> and then <lastname>B*<lastname> and so on until I went thrugh all 26 letters of the alphabet - very gruesome - unless someone knows a better way to do this when you have 500+ records?
     
    I am still looking for docs on the 4.13 Data Dictionary.........
    Thanks!!

    Subject: RE: CUCM 4.13 - Need SQL to get all End User Account userids
    Replied by: David Staudt on 31-07-2009 04:27:19 PM
    Breaking up the query by alphabetic letter is about as good an approach as any.  As the user info is stored in LDAP, you also have the possibility of using direct LDAP protocol to perform your queries.
     
    The Data Dictionary documents were produced starting with CM5.  For CM4, the MS SQL Enterprise Manager is available on the UCM, which provides full capabilities to explore the DB schema - albeit without the cryptic comments found in the Data Dictionaries

    Subject: RE: CUCM 4.13 - Need SQL to get all End User Account userids
    Replied by: Keith Lunn on 31-07-2009 05:05:14 PM
    Thx david! Yea - i used SQL Server Enterprise manager and got as list of tables in the database.
    Thx for info on documentation! (I suspected)
    Keith