CUCM 8.x SQL Syntax - MoH Dependency

[bbenner]

Has anyone posted the syntax of a SQL query that returns the devices associated to an MoH audio file (Network or User)?

Thanks,

Beau

[markbatts · Accepted solution]

try this

run sql select name,userholdmohaudiosourceid,networkholdmohaudiosourceid from device

this lists all devices and the associated moh id.

[Brian Meade · Accepted solution]

You can get fancy and also display the names:

run sql select device.name, usersource.name as usersource, networksource.name as networksource from device left join mohaudiosource as usersource on device.userholdmohaudiosourceid=usersource.sourceid left join mohaudiosource as networksource on device.networkholdmohaudiosourceid=networksource.sourceid

Also don't forget to check the lines as they take precedence over device:

run sql select np.dnorpattern as extension,partition.name as partition, usersource.name as UserSource,networksource.name as NetworkSource from numplan as np left join routepartition as partition on np.fkroutepartition=partition.pkid left join mohaudiosource as usersource on np.userholdmohaudiosourceid=usersource.sourceid left join mohaudiosource as networksource on np.networkholdmohaudiosourceid=networksource.sourceid

[bbenner]

Thank you both.

Mark - it works as expected pulling devices with user & network hold file names.

Brian - this too works to pull the pattern's associated MoH file name.

I've used them both and verified the syntax.

[derek.fraser]

Thank you Mark and Brian, nice work +5. I can confirm this works on CUCM 11.5 and was helpful when querying MOH settings for devices (CTI Ports from CCX Triggers).