Getting list of all users in a User Group

Version 1
    This document was generated from CDN thread

    Created by: MIKE WILCOX on 06-08-2009 04:55:15 PM
    Can someone kindly suggest a method of getting a list of all users in the Standard CCM End User group and the Standard CCM CTI enabled group? CUCM is 6.1.2.1125-1 but will also need a solution that works on 7.1.2.
     
    The following request returns a list of users, but I don't believe it is all of the, We have some clusters that will have 10,000 to 15,000 users in that group.
     
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

             <SOAP-ENV:Body>
           <getUserGroup>
            <name>Standard CCM End Users</name>
    </getUserGroup>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
     
    I also tried SQL:
     
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/6.1">
     <SOAP-ENV:Header/>
     <SOAP-ENV:Body>

           <executeSQLUpdate>
            <sql>SELECT * FROM enduser WHERE userid = mjwilcox</sql>
    </executeSQLUpdate>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
     
    and got the following error:
     
    <SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><SOAP-ENV:Header/>
    <SOAP-ENV:Body><SOAP-ENV:Fault><faultcode>SOAP-ENV:Client</faultcode>
    <faultstring>Column (mjwilcox) not found in any table in the query (or SLV is undefined).</faultstring>
    <detail><axl:Error xmlns:axl="http://www.cisco.com/AXL/API/1.0"><axl:code>-217</axl:code>
    <axl:message>Column (mjwilcox) not found in any table in the query (or SLV is undefined).</axl:message>
    <request>executeSQLUpdate</request></axl:Error></detail></SOAP-ENV:Fault></SOAP-ENV:Body></SOAP-ENV:Envelope>
     
    Thanks,
     
    Mike
     

    Subject: RE: Getting list of all users in a User Group
    Replied by: Keith Lunn on 06-08-2009 04:58:30 PM
    With reference to your SQL - I believe that you need the value enclosed with quotes...like:
    <sql>SELECT * FROM enduser WHERE userid = 'mjwilcox'</sql>

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 05:02:32 PM
    Kieth,
     
    I forgot to mention that I tried that with both ' and " and both ways get the following error:
     
    Request:
     <SOAP-ENV:Body>

           <executeSQLUpdate>
            <sql>SELECT * FROM enduser WHERE userid = 'mjwilcox'</sql>
    </executeSQLUpdate>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
     
    Response:
     
    <SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><SOAP-ENV:Header/>
    <SOAP-ENV:Body><SOAP-ENV:Fault><faultcode>SOAP-ENV:Client</faultcode>
    <faultstring>Cannot execute a SELECT statement that is PREPAREd - must use cursor.</faultstring>
    <detail><axl:Error xmlns:axl="http://www.cisco.com/AXL/API/1.0"><axl:code>-260</axl:code>
    <axl:message>Cannot execute a SELECT statement that is PREPAREd - must use cursor.</axl:message>
    <request>executeSQLUpdate</request></axl:Error>
    </detail></SOAP-ENV:Fault></SOAP-ENV:Body></SOAP-ENV:Envelope>
     
    Thanks,
     
    Mike

    Subject: RE: Getting list of all users in a User Group
    Replied by: Keith Lunn on 06-08-2009 05:11:22 PM
    Is this on your 6.12 system? I have 6.13 and have done a similar SQL many times with no issue:
    <sql>SELECT userid, firstname, lastname FROM enduser WHERE userid MATCHES '[1-9][0-9]*'</sql>
     
    Are you sure your SOAP Message is well formatted? I had a few discussions on this with TAC and finally got them to give me the standard way to do it - they have many differenent ways of doing it in the Developer's guide. This is the one I always use with issues:
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:axlns="http://www.cisco.com/AXL/API/6.1">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
    <axlns:executeSQLQuery sequence="601">
    <sql>SELECT userid, firstname, lastname FROM enduser WHERE userid MATCHES '[1-9][0-9]*'</sql>
    </axlns:executeSQLQuery>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
    This one is using sequence # 601 but you can use any sequence number you like.

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 05:17:02 PM
    Kieth,
     
    I'll give this a shot. What I am trying to do is add an End User to Standard CCM End Users and Standard CCM CTI Enabled user groups. According to TAC I have to list out all of the users, add my user to the list and updateUserGroup to write them all back in.
     
    How would I modify your query below to get just the users in Standard CCM End Users and Standard CTI Enabled?
     
    Thanks,
     
    Mike

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 05:24:53 PM
    Kieth,
     
    Using your query I was able to get the SQL to work on 6.1.2 and 7.1.2. Now that I know SQL is qorking I need to take the next step to getting the list of users in those two User Groups.
     
    Thanks for you continued help.
     
    Mike

    Subject: RE: Getting list of all users in a User Group
    Replied by: Keith Lunn on 06-08-2009 05:35:16 PM
    Wow - that sounds bizzare - however, I just checked the API documentation and actually that's the interpretation I am getting too: (I haven't used that API before)
    UpdateUserGroupReq
    The new members of this User Group. If this element is null, then all members are removed from this user group. Note: If the "members" element is present, then all existing members are removed and replaced with the "member" sub-elements.

    So it looks like you have to get all the current members in this group and store in an array object and then add the new member(s) into the array, and then use updateUserGroup to add them back in using the entire array of members.

    In order to get a list if userids yo ucan use listUserByName but you may run ionto a data throttling issue - like i did - and I have to build a loop that went through all 26 letters of the alphabet building a structure like:
    var arrAlpha = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
    for (var i=0; i < arrAlpha.length; i++) { // cycle thru each letter of the alphabet
     doBuildAXLRequest(i);
     doBuildHTTPHeader();
     doProcessRequest();  //get userid and store in an array
    }

    function doBuildAXLRequest(thisidx){
    strSoapPayload = "<SOAP-ENV:Envelope ";
    strSoapPayload += "xmlns:SOAP-ENV=\"http://schemas.xmlsoap.org/soap/envelope/\" ";
    strSoapPayload += "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">";
    strSoapPayload += " <SOAP-ENV:Header/>";
    strSoapPayload += " <SOAP-ENV:Body>";
    strSoapPayload += " <axl:listUserByName ";
    strSoapPayload += "xmlns:axl=\"http://www.cisco.com/AXL/API/1.0\" sequence=\"" + intAXLSeqNum + "\">";
    strSoapPayload += " <firstname>*</firstname>";
    strSoapPayload += " <lastname>" + arrAlpha + "*</lastname>";
    strSoapPayload += " </axl:listUserByName>";
    strSoapPayload += " </SOAP-ENV:Body>";
    strSoapPayload += " </SOAP-ENV:Envelope>";
    intSoapPayloadLength = strSoapPayload.length;

    Once you get your list of userids, then I guess you would have to cycle through that array and do a getUser for each userid. See listUserByName only returns useris, lastname, firstname so u need to use getUser() to get the group membership stuff.
    According to the API spec, the membership info is stored in the <associatedGroups> node.
    That's my take on it - others may have other ways or comments on the way to do it.
    K

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 05:42:33 PM
    There is the AXL getUserGroup that returns members of a group, but that runs into the data throttling issue. Is there a similar way to do it via execute SQLQuery?
     
    Mike

    Subject: RE: Getting list of all users in a User Group
    Replied by: Keith Lunn on 06-08-2009 06:14:53 PM
    Yea - I was just looking at that - there is the getUserGroup API:
    strSoapPayload += " <axl:getUserGroup";
    strSoapPayload += "xmlns:axl=\"<a>http://www.cisco.com/AXL/API/1.0\</a>]" sequence=\"" + intAXLSeqNum + "\">";
    strSoapPayload += " <name>YourGroupName</name>";
    strSoapPayload += " </axl:getUserGroup>";
    Give that a try...with using the SOAP message format I listed above. do u know how many members in it now? The data throttling issue I ran into was on a large system with more that 500 end user accounts and that was on a 4.13 system - thats why i had to break it up into smaller queries using listUserByName. I have used SQL query on the enduser table on a 6.13 system with 800 end users accounts and it worked fine but it may choke if I use the same script on a larger system. Try the getUserGroup and see if you are able to get all the members first. If it does choke you should get a SOAP message similar to:
    <return>
        <ErrorMsg>Search result exceeded the size limit. Displaying truncated result</ErrorMsg>

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 06:23:07 PM
    Keith,
     
    Is the data limit on the amount of data (8M or number of rows? If it is amount of data, which I think it is, then I don't see the output of getUserGroup getting over 8MB. Still verifying if the numbers match but a request against a userGroup of 5700 members returned 500KB of data. I don't see going over 8MB.
     
    Thanks,
     
    Mike
     

    Subject: RE: Getting list of all users in a User Group
    Replied by: Keith Lunn on 06-08-2009 06:26:48 PM
    according to the developers guide.........hmmmmmm....its 8MB. Put a debug print in your program to dump the resulting xml to a file so you can search for an error message. The last one I saw where i ran into a limit was on 4.13 and it put it at the top of the data return like such:
    XML Results Dump[
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
     <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>
        <user userid="5555551212">
         <firstname>John</firstname>
         <lastname>Doe</lastname>
        </user>

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 06:31:32 PM
    There is no error in the return text file. Still working to figure out how to either:
    Count the number of </member>
    or
    Split the file on </member> so I can count the lines.
     
    So if I exceed the 8 MB I will get an error I can program with that in mind.
     
    Thanks,
     
    Mike

    Subject: RE: Getting list of all users in a User Group
    Replied by: Keith Lunn on 06-08-2009 06:50:18 PM
    How are you parsing the return XML? What programming or scripting language? If you are loading it into a XMLDOM object,
    colRows = objXML.getElementsByTagName("member") - which creates a collection/array of node objects - one per member and then take the length of the array. If you don't see any error message or fault I would think that it retrieved all the members successfully?

    Subject: RE: Getting list of all users in a User Group
    Replied by: MIKE WILCOX on 06-08-2009 06:54:48 PM
    Scripting is being done in perl. I think we are on the path to success here...thanks for all of your help!
     
    Mike