To make life easier on everyone I have created a sample piece of code that you can run sql against the Callmanager database.
I am putting this hear so I can find it again in the future:)
It's not perfect, but tweak as you need it.
clear
$scriptPath = "PATH TO WSDL AND EXPORT"
#This is for certificate issues - was used in dev...
[Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}
[Net.ServicePointManager]::Expect100Continue = $false
$MyCreds = Get-Credential
#GET THE WSDL FROM CUCM administrator->Applications->Plugins - it is a zip file, in it you will find the wsdl
$AXL = New-WebServiceProxy -Uri ($scriptPath + "\AXLAPI.wsdl") -Credential $MyCreds
$AXL.Url = "https://SERVERNAME:8443/axl/"
#$AXL | get-member -type method
$ns = $AXL.getType().namespace
$SQLstr = New-Object ($ns + ".ExecuteSQLQueryReq")
#$SQLstr.sql = "select e.userid, n.dnorpattern from enduser as e, numplan as n, endusernumplanmap as x where (e.pkid = x.fkenduser and x.fknumplan = n.pkid)"
#$SQLstr.sql = "select * from enduser"
#$SQLstr.sql = "select * from numplan"
#$SQLstr.sql = "select * from endusernumplanmap"
#$SQLstr.sql = "select * from callmanager"
#The following query includes building the number
$SQLstr.sql = "select distinct dmap.e164mask,
SUBSTRING(dmap.e164mask FROM 0 FOR (instr(dmap.e164mask,'X'))) ||
SUBSTRING(n.dnorpattern FROM (LENGTH(n.dnorpattern) - (LENGTH(dmap.e164mask) - instr(dmap.e164mask,'X'))) FOR (LENGTH(dmap.e164mask) - instr(dmap.e164mask,'X')+1)) ,
n.dnorpattern, euser.userid
from enduser euser, endusernumplanmap emap, devicenumplanmap dmap, numplan n
where
n.pkid=dmap.fknumplan
and n.pkid=emap.fknumplan
and euser.pkid=emap.fkenduser
and emap.tkdnusage ='1'
and dmap.e164mask is not null"
$ReturnValue = $AXL.executeSQLQuery($SQLstr)
$usercsv = @()
$ReturnValue.return | % {
$user = "" | select userid,ext,ddi
$user.userid = $_[3].InnerText
$user.ext = $_[2].InnerText
$user.ddi = $_[1].InnerText
#Dial plan rules here
if ($user.ddi -match "^463") { $user.ddi = "02" + $user.ddi }
$usercsv += $user
}
$ExportFile = $scriptPath + "\CiscoExport.csv"
$usercsv | Export-Csv -Path $ExportFile -NoTypeInformation
If you use the script, Credit is appreciated.
Thanks,
Roy Cohen
