Retrieve associatedDevices with listLine

[lpsnetwork]

Hello,

I'm doing the following request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/8.5">

    <soapenv:Header/>

    <soapenv:Body>

        <ns:listLine>

                <searchCriteria>

                     <routePartitionName>DN_W</routePartitionName>

                </searchCriteria>

                <returnedTags>

                     <associatedDevices></associatedDevices>

                     <pattern></pattern>

                </returnedTags>

        </ns:listLine>

    </soapenv:Body>

</soapenv:Envelope>

It does return all the DN with the pattern field, but I don't get the list of associatedDevices, I also tried

                     <associatedDevices><device></device></associatedDevices>

Any hints ?

My goals is to get all numbers with associate device (UDP in my case), then for each UDP use getDeviceProfile to retrieve the login associated to the phone number.

May be it could be easier with a SQL Query but I've no idea of which table I could use.

Thanks for your help!

[npetrele]

I'm getting the same results you are.  I think you may have stumbled on a defect.

I'm going to look into how to do it with a SQL query and get back to you. 

[npetrele]

Hopefully this query showing the relationships between tables will get you started.  Here's a query to find the device name for the line pattern "3000". 

select name from device INNER JOIN devicenumplanmap ON devicenumplanmap.fkdevice=device.pkid

     INNER JOIN numplan ON devicenumplanmap.fknumplan=numplan.pkid

     WHERE numplan.dnorpattern='3000'

[lpsnetwork]

Thanks a lot Nicholas,

I've been able to retrieve link between numbers and user with the three following requests

SELECT numplan.dnorpattern, device.name, enduser.userid FROM numplan INNER JOIN devicenumplanmap ON numplan.pkid=devicenumplanmap.fknumplan INNER JOIN device ON devicenumplanmap.fkdevice=device.pkid INNER JOIN enduser ON device.fkenduser=enduser.pkid

SELECT numplan.dnorpattern, enduser.userid FROM numplan INNER JOIN endusernumplanmap ON numplan.pkid=endusernumplanmap.fknumplan INNER JOIN enduser ON endusernumplanmap.fkenduser=enduser.pkid

SELECT numplan.dnorpattern, device.name, enduser.userid FROM numplan INNER JOIN devicenumplanmap ON numplan.pkid=devicenumplanmap.fknumplan INNER JOIN device ON devicenumplanmap.fkdevice=device.pkid INNER JOIN enduserdevicemap ON device.pkid=enduserdevicemap.fkdevice INNER JOIN enduser ON enduserdevicemap.fkenduser=enduser.pkid

The first one seems to return only DN of users with CSF or DN associate to a phone. If I remove "INNER JOIN enduser ON device.fkenduser=enduser.pkid" I do get both device (CSF & UDP - when both exist of course) but the fkenduser=NULL on the UDP. May be it's because we use EM. So it doesn't seems to be a good idea as I guess I would miss numbers if i have user without CSF

The second one seems to work well for my particular need. I'm not sure how the link is performed and what could be the result if a user has only a CSF or only a UDP. And what would be the result if a user has different number on CSF & UDP.

The third one seems the most complete and return a line by CSF & UDP. So i guess it works 100% even if one user has different number on CSF & UDP.

I'll make more test tomorrow to test my hypothesis and result with different kind of users (UDP only, CSF only, different number on UDP & CSF). In the meantime if you have any advice you're very welcome.

PS: for the listLine not returning the devices, do you suggest to open a ticket to TAC ?

(not sure if they will process as we don't have any devnet subscription)

[lpsnetwork]

So I can confirm that

- 1st request doesn't works for user with just an UDP.

- 2nd request provide inconsistent result.

- 3rd request works well for user with only a CSF, only a UDP, a CSF and a UDP (with same or different number)

So for records if it can help someone else in the future

SELECT numplan.dnorpattern, device.name, enduser.userid FROM numplan INNER JOIN devicenumplanmap ON numplan.pkid=devicenumplanmap.fknumplan INNER JOIN device ON devicenumplanmap.fkdevice=device.pkid INNER JOIN enduserdevicemap ON device.pkid=enduserdevicemap.fkdevice INNER JOIN enduser ON enduserdevicemap.fkenduser=enduser.pkid

Seems the best in a CFS+EM environment.