Unity Connection SQL query that displays subscriber and extension

[Jay Schulze]

Hello,

Does anyone know a sql query for unity connection which will show just Alias and Extension? 

[lindborg · Accepted solution]

if you just want the alias and primary extension of the user you can simply get them both from the vw_subscriber view:

select alias, dtmfaccessid from vw_subscriber

If you need all the possible extensions for users (users can have 10 alternate extensions for instance) you'll need to pull them in from the vw_dtmfaccessid view like this:

select vw_subscriber.alias, vw_dtmfaccessid.dtmfaccessid 
from vw_subscriber, vw_dtmfaccessid
where vw_dtmfaccessid.Parent_SubscriberObjectId = vw_subscriber.ObjectId
order by alias

[Jay Schulze]

Thank you sir. That works well.

[shaijal.allipra]

I tried both but didnt work with 'run sql '.
Is there any command to see with calling party

[lindborg]

if you're running this at the Unity Connection admin command line you need to prefix "run cuc dbquery unitydirdb " in front of the SQL query there - UnityDirDb is the main directory database for Unity (as opposed to the MbxDbs for messages and such).

Not sure what you're trying to query here with regards to calling party - are you wanting to search for messages in the database from particular calling numbers or the like?

[shaijal.allipra]

Many thanks

something like this, this is from CUCM. similarly from CUC I am looking for customer number,subscriber and date time

call_type callingpartynumber originalcalledpartynumber datetimestamporigination
========= ================== ========================= ========================
S 113122 113118 2019-02-04 09:29:11
S 113122 13146529924 2019-02-04 13:32:43