Following SQL query in CUCM (11.5) cli returns with the following result:
device number loggedin_to_lg linegroup
=============== =============== ============== ======================================
CSFABCDEF \+49325874147 f LG-HG_BER01_49325874147
CSFRFETRS \+49325800848 f LG-HG_BER01_493225800848
run sql select d.name as Device, n.dnorpattern as Number, dhd.hlog as LoggedIn_to_LG, lg.name as LineGroup
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
order by lg.name
However, if we try to inner join one more table -extensionmobilitydynamic- to this query and display one of its columns, it returns with no result.
run sql select d.name as Device, n.dnorpattern as Number, dhd.hlog as LoggedIn_to_LG, lg.name as LineGroup, e.datetimestamp
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
inner join extensionmobilitydynamic as e on e.fkdevice = d.pkid
order by lg.name
It's not finding any matching rows and hence the INNER JOIN fails to retrieve anything. You must try left join for the new table where there is a possibility to have no matching results. The resultant column will be NULL in this instance.
run sql select d.name as Device, n.dnorpattern as Number, dhd.hlog as LoggedIn_to_LG, lg.name as LineGroup, e.datetimestamp
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
left join extensionmobilitydynamic as e on e.fkdevice = d.pkid
order by lg.name