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