01-27-2015 03:55 PM
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
06-12-2018 08:03 AM
Dear,
Thanks for that code. it's super useful.
I sometimes face the below error for no reason:
Exception calling "executeSQLQuery" with "1" argument(s): "The request failed with HTTP status 401: Unauthorized."
At C:\PowerShellScripts\AccountAutomation\CUCM-AssignDNtoPhoneInAD.ps1:41 char:1
+ $ReturnValue = $AXL.executeSQLQuery($SQLstr)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : WebException
This happens randomly and then for no reason starts working again after a few days... for example, today it fails from my laptop but works from my scripting server..
Any clue what could be wrong?
Thx,
Ben
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: