cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
5197
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

Getting Started

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: