Help with AXL executeSQLQuery to get UDP

[gambit820]

need help guys trying to get UDP from CUCM via AXL and i get everything but that below is my query and what it pulls, what i need it to pull is the UDP. 

my end game is to pull the logged in users and then use python flask to get that on a webpage to show who is logged in

thanks for your help

<ns:executeSQLQuery>
<sql>
select lg.name as LineGroup,d.name,d.description,n.dnorpattern,dhd.hlog
from linegroup as lg
inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid
inner join numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid
inner join device as d on dmap.fkdevice=d.pkid
inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid
where lg.name IN ('HR_Benefits-LG','HR_ServiceCenter-LG')
order by lg.name
</sql>
</ns:executeSQLQuery>

<return>

<row>

<linegroup>HR_Benefits-LG</linegroup>

<name>CSF555556473</name>

<description>UserFirst UserLast</description>

<dnorpattern>\+15555558846</dnorpattern>

<hlog>f</hlog>

</row>

<row>

<linegroup>HR_Benefits-LG</linegroup>

<name>TCT555556473</name>

<description>UserFirst UserLast</description>

<dnorpattern>\+15555558846</dnorpattern>

<hlog>f</hlog>

</row>

<row>

<linegroup>HR_Benefits-LG</linegroup>

<name>BOT555556473</name>

<description>UserFirst UserLast</description>

<dnorpattern>\+15555558846</dnorpattern>

<hlog>f</hlog>

</row>

[thedd]

Try this one:

select d.name as sep_device, eu.userid as user , p.name as udp from extensionmobilitydynamic emd, device d, device p, enduser eu where emd.fkdevice=d.pkid and emd.fkdevice_currentloginprofile=p.pkid and emd.fkenduser=eu.pkid 

This will give you the SEP, logged in UDP and the related user.

[gambit820]

so this is close but doesnt have the devicehlogdynamic which is what i was using to tell me if they are logged in or not, but thank you this is a good start i will try and figure how to get dhd.log and limit to the two pilot groups

[thedd]

What output are you searching for?

What infomration should be included?

Do you need the UDP, the SEP or just the information who is logged in into Hunt group?

[gambit820]

i am trying to get the current logged in users for that hunt pilot, but my query is returning everything but UDP which is what they are logged in on their phones. so i tried adding UDP to the query and it didn't work, so ultimately what i am trying to accomplish is the ability to query who is currently logged in on the phone right now.

your query returns the following for me;

<return>

                <row>

                    <sep_device>SEPB4A8B94DB8C0</sep_device>

                    <user>30057987</user>

                    <udp>UDP30057987</udp>

                </row>

                <row>

which is fine but i am using python to display who is logged in by the  <hlog>t</hlog> tag which i cant figure out how to add to your query. 

what i need returned to me is the hlog status of udp weather it is logged in or not and then the other information below.

                <row>

                    <linegroup>HR_Benefits-LG</linegroup>

                    <name>TCT30056473</name>      <-- This needs to Be UDP

                    <description>User name is in description </description>

                    <dnorpattern>user number is here</dnorpattern>

                    <hlog>f</hlog>   <- This current status of UDP logged in or not

                </row>

also thank you for your help i really appreciate you taking the time to help me, i've been trying to figure this out for weeks and my sanity is dipping fast.

[thedd]

I think the Problem is not the UDP is logged in but the Hardphone is.

So you will have to connect the UDP and "real" Phone.

If you have a look into CUCM the UDP dos not have the option "Logged Into Hunt Group".

So if you combine mine SQL and yours you will have

SEP

USER

UDP

Linegroup

hlog

[thedd]

Try this one

select lg.name as LineGroup, sep.name as SEP, udp.name as UDP, n.dnorpattern as DN ,dhd.hlog as HuntLog
from linegroup as lg
inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid
inner join numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid
inner join device as sep on dmap.fkdevice=sep.pkid
inner join devicehlogdynamic as dhd on dhd.fkdevice=sep.pkid
inner join extensionmobilitydynamic as extmob on sep.pkid=extmob.fkdevice
inner join device as udp on extmob.fkdevice_currentloginprofile=udp.pkid
where lg.name IN ('HR_Benefits-LG','HR_ServiceCenter-LG') and dhd.hlog="t"
order by lg.name

This will give you

linegroup: LG_Name
sep: Hardphone with Hlog = true
udp: UDP Name
dn: directory_number
huntlog: Huntlog Status

[gambit820]

were you able to run this i am not able to run it gives me a syntax error it all looks like it should work but doesnt. i took out the double where and that wasnt it, below is the error i am getting.

 <faultcode>soapenv:Client</faultcode>

            <faultstring>A syntax error has occurred.</faultstring>

            <detail>

                <axlError>

                    <axlcode>-201</axlcode>

                    <axlmessage>A syntax error has occurred.</axlmessage>

                    <request>executeSQLQuery</request>

[thedd]

What CUCM Version are you running?

[gambit820]

System version: 12.0.1.21900-7

[thedd]

I see the mistake.

There is a double where.

Use this one:

select lg.name as LineGroup, sep.name as SEP, udp.name as UDP, n.dnorpattern as DN ,dhd.hlog as HuntLog
from linegroup as lg
inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid
inner join numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid
inner join device as sep on dmap.fkdevice=sep.pkid
inner join devicehlogdynamic as dhd on dhd.fkdevice=sep.pkid
inner join extensionmobilitydynamic as extmob on sep.pkid=extmob.fkdevice
inner join device as udp on extmob.fkdevice_currentloginprofile=udp.pkid
where lg.name IN ('HR_Benefits-LG','HR_ServiceCenter-LG') and dhd.hlog="t"
order by lg.name

[gambit820]

sorry for the delay that didn't work either and its actually not even spitting out an error;

<?xml version='1.0' encoding='UTF-8'?>

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

    <soapenv:Body>

        <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/12.0">

            <return/>

        </ns:executeSQLQueryResponse>

    </soapenv:Body>

</soapenv:Envelope>

i feel like we are getting close but i cant figure it out.

[thedd]

If there is no Error the SQl Syntax is ok.
If there is no Result there has been no match.
At the moment you do the sql query be sure there is a UDP logged in on an SEP and logged in on the LG.
Do you have a test phone tob e sure that there hast to be an result?
Or just ask for lg.name Like "%" then you will not filter a specific LG which will maybe better proof the SQL:
Otherwise some of the inner joins have to be changed to left join.

[thedd]

Just try this SQL Statement:

select lg.name as LineGroup, sep.name as SEP, udp.name as UDP, n.dnorpattern as DN ,dhd.hlog as HuntLog
from linegroup as lg
inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid
inner join numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid
inner join device as sep on dmap.fkdevice=sep.pkid
left join devicehlogdynamic as dhd on dhd.fkdevice=sep.pkid
left join extensionmobilitydynamic as extmob on sep.pkid=extmob.fkdevice
left join device as udp on extmob.fkdevice_currentloginprofile=udp.pkid
where lg.name Like "%" and dhd.hlog="t"
order by lg.name

[thedd]

Did my solution work for you?