cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
5331
Views
0
Helpful
1
Replies

Powershell and AXL Web services

roy.cohen2
Level 1
Level 1

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

1 Reply 1

bkaison
Level 1
Level 1

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