[CUCM] How to retrieve the IP Address of user's device?

[aaronngan]

I am using CUCM 8.5, and I am trying to retrieve the user device IP address from his/her extension number. I have called the AXLAPIService.executeSQLQuery(), using this function and the following SQL query, I can successfully retrieve the device name (d.name).

        ExecuteSQLQueryReq exec = new ExecuteSQLQueryReq();

        string mySQL = "select d.name " +

            "from DeviceNumPlanMap dn, device d, NumPlan n  " +

            "where dn.fkDevice = d.pkid and dn.fkNumPlan = n.pkid and n.DNOrPattern='3345'";

        exec.sql = mySQL;

        ExecuteSQLQueryRes resp = axlApiService.executeSQLQuery(exec);

SQL:

          select d.name

          from DeviceNumPlanMap dn, device d, NumPlan n

          where dn.fkDevice = d.pkid and dn.fkNumPlan = n.pkid and n.DNOrPattern='3345'

I want to alter the query to find the user device's IP address, but after a few hours research, I have no clue which table and column contains that information. Would any expert share your experience to me, please?

[dstaudt · Accepted solution]

As device IP addresses are pretty volatile, these are not stored in the UCM database, but rather in an in-memory process (Realtime Information Service.)  You can query for the IP address (and few other interesting fields like registration status, registration time, etc.) using the the UCM Serviceability XML SOAP API called 'Risport':

https://developer.cisco.com/site/collaboration/management/uc-manager-serviceability/overview/

[aaronngan]

Thanks for pointing out the right direction dstaudt. I have programmed some code on Risport, but I encountered a new problem. When I try to post the https://mycucmip:8443/realtimeservice/services/RisPort, I keep receiving a 505 error: "HTTP version not supported". It seems to be a coding issue rather than a configuration / authentication issue.

Here is how I post to Risport, I appreciate if you can spot the mistake.

HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(string.Format("https://{0}:8443/realtimeservice/services/RisPort", ccmIp));

        NetworkCredential mNetworkCredential = new NetworkCredential(user, password);

        string myXML = "POST /realtimeservice/services/RisPort HTTP/1.0 \r\n" +

            "Content-Type: text/xml; \r\n" +

            "SOAPAction: \"http://schemas.cisco.com/ast/soap/action/#RisPort#SelectCmDevice\" \r\n" +

            "Authorization: Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("username:password")) + " \r\n" +

            "Host: mycucmip:8443 \r\n";

        myXML += "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +

            "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" " +

            "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" " +

            "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +

            "<soapenv:Body>" +

            "<ns1:SelectCmDevice soapenv:encodingStyle=\"http://schemas.xmlsoap.org/soap/encoding/\" " +

            "xmlns:ns1=\"http://schemas.cisco.com/ast/soap/\">" +

            "<StateInfo xsi:type=\"xsd:string\"/>" +

            "<CmSelectionCriteria href=\"#id0\"/>" +

            "</ns1:SelectCmDevice>" +

            "<multiRef id=\"id0\" soapenc:root=\"0\" " +

            "soapenv:encodingStyle=\"http://schemas.xmlsoap.org/soap/encoding/\" " +

            "xsi:type=\"ns2:CmSelectionCriteria\" " +

            "xmlns:soapenc=\"http://schemas.xmlsoap.org/soap/encoding/\" " +

            "xmlns:ns2=\"http://schemas.cisco.com/ast/soap/\">" +

            "<MaxReturnedDevices xsi:type=\"xsd:unsignedInt\">200</MaxReturnedDevices>" +

            "<Class xsi:type=\"xsd:string\">Phone</Class>" +

            "<Model xsi:type=\"xsd:unsignedInt\">255</Model>" +

            "<Status xsi:type=\"xsd:string\">Registered</Status>" +

            "<NodeName xsi:type=\"xsd:string\" xsi:nil=\"true\"/>" +

            "<SelectBy xsi:type=\"xsd:string\">Name</SelectBy>" +

            "<SelectItems soapenc:arrayType=\"ns2:SelectItem[1]\" xsi:type=\"soapenc:Array\">" +

            "<item href=\"#id1\"/>" +

            "</SelectItems>" +

            "</multiRef>" +

            "<multiRef id=\"id1\" soapenc:root=\"0\" " +

            "soapenv:encodingStyle=\"http://schemas.xmlsoap.org/soap/encoding/\" " +

            "xsi:type=\"ns3:SelectItem\" xmlns:ns3=\"http://schemas.cisco.com/ast/soap/\" " +

            "xmlns:soapenc=\"http://schemas.xmlsoap.org/soap/encoding/\">" +

            "<Item xsi:type=\"xsd:string\">*</Item>" + // put device mac address here

            "</multiRef>" +

            "</soapenv:Body>" +

            "</soapenv:Envelope>";

        string myPostData = "XML=" + myXML;

        byte[] myByteArray = Encoding.UTF8.GetBytes(myPostData);

        webRequest.Method = "POST";

        webRequest.ContentLength = myByteArray.Length;

        System.Net.ServicePointManager.ServerCertificateValidationCallback = MyCertHandler;

        webRequest.Credentials = mNetworkCredential;

        using (Stream myRequestStream = webRequest.GetRequestStream())

        {

            myRequestStream.Write(myByteArray, 0, myByteArray.Length);

        }

        Stream myResponseStream = ((HttpWebResponse)webRequest.GetResponse()).GetResponseStream(); //Exception here

        XmlTextReader myXMLReader = new XmlTextReader(myResponseStream);

[tinghche]

Please try this.

ServicePointManager.Expect100Continue = False;

Regards,

Howard