Cisco Unity Connection SQL Query

[bholashrestha]

Hi All,

Just trying to figure out if i can pull out the list of users using a particular Authentication Rule. I don't see any built in reporting tools in CUC. So only option i believe would be to run some SQL query. i want to print out the list of users who is using a specific authentication rule. Even better if an authentication rule is used for Web Application or Voicemail.

Would someone be able to help me out? It would be much appreciated.

Thanks in advance..

Regards

BS

[lindborg]

Policies are stored in vw_credentialpolicy and are mapped between policy and users in vw_credential which also stores the type

select vw_subscriber.Alias, vw_credentialpolicy.displayname, vw_credential.CredentialType
from vw_credentialpolicy, vw_subscriber, vw_credential
where vw_subscriber.alias = vw_credential.alias AND
vw_credentialpolicy.ObjectId = vw_credential.CredentialPolicyObjectId
order by alias

the type you are looking for is 3 (GUI password) or 4 (phone pin)

[bholashrestha]

Thanks for this. I tried this query but failed. i tried on both 9.1 and 11.5 CUC version.

admin:run sql select vw_subscriber.Alias, vw_credentialpolicy.displayname, vw_credential.CredentialType from vw_credentialpolicy, vw_subscriber, vw_credential where vw_subscriber.alias = vw_credential.alias AND vw_credentialpolicy.ObjectId = vw_credential.CredentialPolicyObjectId order by alias
The specified table (vw_credentialpolicy) is not in the database.

admin:run cuc dbquery unitymbxdb1 select vw_subscriber.Alias, vw_credentialpolicy.displayname, vw_credential.CredentialType from vw_credentialpolicy, vw_subscriber, vw_credential where vw_subscriber.alias = vw_credential.alias AND vw_credentialpolicy.ObjectId = vw_credential.CredentialPolicyObjectId order by alias

The specified table (vw_credentialpolicy) is not in the database.

admin:run cuc dbquery unitymbxdb1 select * from vw_credentialpolicy

The specified table (vw_credentialpolicy) is not in the database.
Command failed

Thanks

BS

[lindborg]

the database you want is UnityDirDb - UnityMbxDb1(through 6) are the message databases.  the DirDb is where all the directory information is stored.

I copy and pasted that query directly from the CUDLI query builder interface attached to an 11.5 server - it's definitely correct.

[will.alvord]

You'll run that query from the cudli query builder, not from an ssh session like you would with call manager (run sql - like your example).

You'll need to download, install, and run the following application. You'll also need the Informix driver (also on this page) and enable the db proxy (instructions in the 'help' page linked to from this page):
http://ciscounitytools.com/Applications/CxN/CUDLI/CUDLI.html

[lindborg · Accepted solution]

you can certainly run it from CUDLI (a much nicer interface for exploring the databases, stored procs and such), but you can also run it from the admin CLI interface via SSH - you just need to reference the correct database like this:

admin:run cuc dbquery unitydirdb select vw_subscriber.Alias, vw_credentialpolicy.displayname, vw_credential.CredentialType from vw_credentialpolicy, vw_subscriber, vw_credential where vw_subscriber.alias = vw_credential.alias AND vw_credentialpolicy.ObjectId = vw_credential.CredentialPolicyObjectId order by alias

---------------------------- ----------------------------------------------- --------------

the output looks like this:

alias displayname credentialtype

operator Recommended Web Application Authentication Rule 3
operator Recommended Voice Mail Authentication Rule 4
rahukum4 Recommended Voice Mail Authentication Rule 4
rahukum4 Recommended Web Application Authentication Rule 3
undeliverablemessagesmailbox Recommended Voice Mail Authentication Rule 4
undeliverablemessagesmailbox Recommended Web Application Authentication Rule 3

[bholashrestha]

Yes certainly. i tried and worked. This is exactly what i wanted.

Thank you very much for you help.

Cheers

BS