I am currently overseeing a large multi-cluster deployment with literally hundreds of SIP trunks spanning almost 10 leaf clusters, plus SME.  Maintaining configuration standardization on deployment with 10 clusters deployed by different suppliers, with managed services from different support teams and ongoing site and feature deployments done by another set of deployment teams makes oversight somewhat challenging.

My architectural team were, therefore, looking to standardize our SIP deployment to meet best practices and to highlight non-standard configuration settings wherever possible.  To do this, I crafted two useful SQL queries to analyze commonly mismatched SIP trunking, SIP Profile and SIP Security Profile settings.

select
d.name,
d.description,
stcs.name as siptrunkcalllegsecurity,
d.mtprequired,
trp.name as usetrustedrelaypoint,
d.retryvideocallasaudio,
d.srtpallowed,
stsp.name as siptrunksecurityprofile,
dsm.name as devicesecuritymode,
sp.name as sipprofile,
sp.zzredirectbyapp,
sp.ringing180,
sp.enableurioutdialsupport,
sdpattr.name as sdpattributelist,
codecpref.name as handlingofreceivedoffercodecpreferences,
sp.inactivesdprequired,
sp.sendrecvsdpinmidcallinvite,
rel1xx.name as rel1xxoptions,
srm.name as sipsessionrefreshmethod,
eo.name as eosuppvoicecall,
sp.enableoutboundoptionsping,
sp.allowpresentationsharingusingbfcp,
sp.allowixchannel,
sp.allowmultiplecodecsinanswersdp,
dtmf.name as dtmfsignaling,
d.pstnaccess,
d.runonallnodes,
qv.name qsigvariant,
tp.name as tunneledprotocol,
snsprofile.name as sipnormalizationscript_profile,
snsdevice.name as sipnormalizationscript_device
from sipdevice sd
inner join device d on d.pkid=sd.fkdevice
inner join typedtmfsignaling dtmf on dtmf.enum=d.tkdtmfsignaling
inner join typestatus trp on trp.enum=d.tkstatus_usetrustedrelaypoint
inner join typetunneledprotocol tp on tp.enum=sd.tktunneledprotocol
inner join typeqsigvariant qv on qv.enum=sd.tkqsigvariant
inner join typesiptrunkcalllegsecurity stcs on stcs.enum=sd.tksiptrunkcalllegsecurity
inner join securityprofile stsp on stsp.pkid=d.fksecurityprofile
inner join typedevicesecuritymode dsm on dsm.enum=stsp.tkdevicesecuritymode
inner join sipprofile sp on sp.pkid=d.fksipprofile
inner join typestatus codecpref on codecpref.enum=sp.tkstatus_handlingofreceivedoffercodecpreferences
inner join typeeosuppvoicecall eo on eo.enum=sp.tkeosuppvoicecall
inner join typesiprel1xxoptions rel1xx on rel1xx.enum=sp.tksiprel1xxoptions
inner join typesipsessionrefreshmethod srm on srm.enum=sp.tksipsessionrefreshmethod
left outer join sipnormalizationscript snsprofile on sp.fksipnormalizationscript=snsprofile.pkid
left outer join sipnormalizationscript snsdevice on sd.fksipnormalizationscript=snsdevice.pkid
left outer join sdpattributelist sdpattr on sdpattr.pkid=sp.fksdpattributelist
order by d.name

The following can be used to extract all the SIP trunk routing destinations, ports and their ordering.

select
d.name,
dest.sortorder,
dest.address,
dest.port
from siptrunkdestination dest
inner join sipdevice sd on sd.pkid=dest.fksipdevice
inner join device d on d.pkid=sd.fkdevice
order by d.name, dest.sortorder

Another common query is extracting the RL/RG/Trunk information:

select
drl.name as rl_name,
drl.runonallnodes,
rg.name as rg_name,
drg.name as trunk_name
from routelist rl
inner join device drl on drl.pkid=rl.fkdevice
left outer join routegroup rg on rg.pkid=rl.fkroutegroup
left outer join routegroupdevicemap rgdmap on rgdmap.fkroutegroup=rg.pkid
left outer join device drg on rgdmap.fkdevice=drg.pkid
where
rl.fklinegroup IS NULL
order by drl.name, rl.selectionorder, rgdmap.deviceselectionorder