11-06-2019 10:40 AM
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>
11-06-2019 11:01 PM
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.
11-07-2019 07:34 AM
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
11-07-2019 11:29 PM - edited 11-07-2019 11:32 PM
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?
11-08-2019 10:07 AM
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;
11-11-2019 02:13 AM
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
11-12-2019 11:52 PM - edited 11-14-2019 02:52 AM
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
11-13-2019 07:33 AM
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.
11-13-2019 07:55 AM
11-13-2019 08:03 AM
System version: 12.0.1.21900-7
11-14-2019 02:51 AM
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
11-15-2019 01:34 PM
sorry for the delay that didn't work either and its actually not even spitting out an error;
11-16-2019 04:22 AM
11-18-2019 01:58 PM
12-06-2019 01:16 PM
Did my solution work for you?
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide