Now that we’ve used the listphone API call to find a phone to copy, we’ll use the getphone API call to pull phone details that will be copied to the new phone. The image below shows the blanks this section will be filling in.
The getphone API call is made using the devicename of the phone to be copied.
Specific returned tags will be requested for the phone device and phone lines.
Device tags returned:
DEVICE NAME
Model
Description
Device pool
CSS
Location
owner
Line details returned:
Line number
Partition
Display(Caller ID)
Line text label
Mask
Recording option
Recording profile
Recording Source
MCSS
Max calls
busy trigger
User
Below is the SOAP AXL code string built to pull the phone and line details from the CUCM.
sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ns=""http://www.cisco.com/AXL/API/14.0"">"
sEnv = sEnv & " <soapenv:Header/>"
sEnv = sEnv & " <soapenv:Body>"
sEnv = sEnv & " <ns:getPhone>"
sEnv = sEnv & " <name>" & Cells(LDRC_Array(1, 6), LDRC_Array(1, 7)) & "</name>"
sEnv = sEnv & " <returnedTags>"
sEnv = sEnv & " <model></model>"
sEnv = sEnv & " <name></name>"
sEnv = sEnv & " <callingSearchSpaceName></callingSearchSpaceName>"
sEnv = sEnv & " <devicePoolName></devicePoolName>"
sEnv = sEnv & " <commonDeviceConfigName></commonDeviceConfigName>"
sEnv = sEnv & " <locationName></locationName>"
sEnv = sEnv & " <description></description>"
sEnv = sEnv & " <ownerUserName></ownerUserName>"
sEnv = sEnv & " <softkeyTemplateName></softkeyTemplateName>"
sEnv = sEnv & " <lines>"
sEnv = sEnv & " <line>"
sEnv = sEnv & " <partitionUsage></partitionUsage>"
sEnv = sEnv & " <index></index>"
sEnv = sEnv & " <dirn>"
sEnv = sEnv & " <pattern></pattern>"
sEnv = sEnv & " <routePartitionName></routePartitionName>"
sEnv = sEnv & " </dirn>"
sEnv = sEnv & " <associatedEndusers>"
sEnv = sEnv & " <enduser>"
sEnv = sEnv & " <userId></userId>"
sEnv = sEnv & " </enduser>"
sEnv = sEnv & " </associatedEndusers>"
sEnv = sEnv & " <label></label>"
sEnv = sEnv & " <e164Mask></e164Mask>"
sEnv = sEnv & " <display></display>"
sEnv = sEnv & " <maxNumCalls></maxNumCalls>"
sEnv = sEnv & " <busyTrigger></busyTrigger>"
sEnv = sEnv & " <recordingMediaSource></recordingMediaSource>"
sEnv = sEnv & " <recordingFlag></recordingFlag>"
sEnv = sEnv & " <recordingProfileName/>"
sEnv = sEnv & " <monitoringCssName/>"
sEnv = sEnv & " </line>"
sEnv = sEnv & " </lines>"
sEnv = sEnv & " </returnedTags>"
sEnv = sEnv & " </ns:getPhone>"
sEnv = sEnv & " </soapenv:Body>"
sEnv = sEnv & "</soapenv:Envelope>"
Req.send (sEnv)
CUCMresults = Req.responseText
Set sEnv = NothingThe only variable is the devicename that the search will be based on,
sEnv = sEnv & " <name>" & Cells(LDRC_Array(1, 6), LDRC_Array(1, 7)) & "</name>"The devicename is pulled from this cell:
Which was copied from the selected device name when the ‘Import Selected Phone’ button was pressed.
Note: LDRC_Array, (LineDeviceRowColumn) is a global array that sets the Excel row and column location for the device and each line settings in columns G and I. In case I ever needed to add more details, I can simply change the global values instead of looking through the code for every time each one of these cells are called.
1 through 5 are for the starting row of each phone line. 6 is the device starting row. 7 is the preview import column, while 8 is the create preview column.
LDRC_Array(1, 1) = 14
LDRC_Array(1, 2) = 26
LDRC_Array(1, 3) = 38
LDRC_Array(1, 4) = 50
LDRC_Array(1, 5) = 62
'device start row
LDRC_Array(1, 6) = 5
'imput column
LDRC_Array(1, 7) = 7
'output column
LDRC_Array(1, 8) = 9The image below shows the results from a getphone API call for the Jane Doe phone. Notice there are two lines tagged with ‘line’, while there is a ‘lines’ tag that encompasses both the lines. The ‘returned’ tags encompasses everything that was returned.
Now that we have the XML results applied to the CUCMresults string we can send it to the GetPhoneDevice sub routine to pull the device details and fill in these cells:
Below is the code used to pull the device settings, this is simple since there will only be one single device. There is a string array set to the tag names, so they can be easily looped through the single for next loop. The details are pulled and written to the appropriate cells. I don’t actually pull the softkey template in this version, I did in earlier versions, now I always set the template to one with five lines, since I decided not to import speeddials, BLFs, intercoms etc., I may decide to add them in a future version.
Sub GetPhoneDevice(CUCMresults)
Call SET_Globals
'set device tags
Dim XMLphonetags(20) As String
XMLphonetags(1) = "<model"
XMLphonetags(2) = "<description"
XMLphonetags(3) = "<devicePoolName"
XMLphonetags(4) = "<callingSearchSpaceName"
XMLphonetags(5) = "<locationName"
XMLphonetags(6) = "<ownerUserName"
XMLphonetags(7) = "<softkeyTemplateName"
XML_slocation = 0
XML_elocation = 0
' reads device configurations
'reads through device XML comfiguration and writes them to excel cells
For XMLcounter = 1 To 7
'XML_Slocation will be the '>' from the xml tag
XML_slocation = InStr(CUCMresults, XMLphonetags(XMLcounter)) + Len(XMLphonetags(XMLcounter))
XML_slocation = InStr(XML_slocation, CUCMresults, ">")
'checks for empty'/>' no value
If Mid(CUCMresults, XML_slocation - 1, 2) <> "/>" Then
'Finds end of XML tag
XML_elocation = (InStr(XML_slocation, CUCMresults, "</") - 1)
'writes XML tag in the Excel cell
Cells(LDRC_Array(1, 6) + XMLcounter, LDRC_Array(1, 7)) = Mid(CUCMresults, XML_slocation + 1, XML_elocation - XML_slocation)
Else
'if XML tag is empty, write "" in Excel cell
Cells(LDRC_Array(1, 6) + XMLcounter, LDRC_Array(1, 7)) = ""
End If
Next
End SubBelow are the results with the cells filled in.
Next we’ll pull the line details to fill in these cells, by calling the GetPhponeLines sub routine.
The first half of this sub routine will pull out each line and all its tags into a string array variable. This loop will run to the end of the CUCMresults string pulling out each line that exists into its own separate string stored in the string array lines_array(1,10).
'Read in the Lines
'build all_Lines_STR Includes all text from"<lines" to"</lines>"
start_str = 0
end_str = 0
len_str = 0
start_str = InStr(CUCMresults, "<lines><line")
end_str = InStr(CUCMresults, "</line></lines>")
len_str = end_str - start_str + 15
all_lines_str = Mid(CUCMresults, start_str, len_str)
Dim lines_array(1, 10) As String
'Break out each line from all_lines_str as a string between "<line uuid=" to"</lines>"
XMLl_start = 1
linewriter = 1
Do While InStr(XMLl_start, all_lines_str, "<line uuid=") <> 0
startl_str = InStr(XMLl_start, all_lines_str, "<line uuid=")
endl_str = InStr(XMLl_start + 7, all_lines_str, "</line>")
lenl_str = endl_str - startl_str
XMLl_start = endl_str
lines_array(1, linewriter) = Mid(all_lines_str, startl_str, lenl_str)
linewriter = linewriter + 1
LoopThe second half of this sub is a bit trickier since there could be up to 5 lines that need to be looped through. Plus we have to validate it’s actually a line and not an intercom or SIP URL. This validation happens by making sure the below string exist within each line string. If the correct tag doesn’t exist, the line string is skipped.
<partitionUsage>General</partitionUsage>Once again a string array is set to the tag values so they can easily be looped through.
Dim XMLlinetags(20) As String
XMLlinetags(1) = "<pattern"
XMLlinetags(2) = "<routePartitionName"
XMLlinetags(3) = "<display"
XMLlinetags(4) = "<label"
XMLlinetags(5) = "<e164Mask"
XMLlinetags(6) = "<recordingFlag"
XMLlinetags(7) = "<recordingProfileName"
XMLlinetags(8) = "<recordingMediaSource"
XMLlinetags(9) = "<monitoringCssName"
XMLlinetags(10) = "<maxNumCalls"
XMLlinetags(11) = "<busyTrigger"
XMLlinetags(12) = "<associatedEndusers"This sub routine uses nested loops, The outer loop pulls in each of the line strings from the lines_array and validates it as a phone line before sending it to the inter loop. Then the inter loop will read and write the line details for each line.
XML_slocation = 0
XML_elocation = 0
For linecounter = 1 To 5
' if line string contains "<partitionUsage>General</partitionUsage>" then it is a regular DN
If InStr(lines_array(1, linecounter), "<partitionUsage>General</partitionUsage>") <> 0 Then
For XMLcounter = 1 To 12
'finds index number(phone line number)
line_index_start = InStr(lines_array(1, linecounter), "<index>")
Line_Index = Mid(lines_array(1, linecounter), line_index_start + 7, 1)
'Slocation will be the '>' from the xml tag
XML_slocation = InStr(lines_array(1, linecounter), XMLlinetags(XMLcounter)) + Len(XMLlinetags(XMLcounter))
XML_slocation = InStr(XML_slocation, lines_array(1, linecounter), ">")
'checks for empty/ no value parameter
If Mid(lines_array(1, linecounter), XML_slocation - 1, 2) <> "/>" Then
XML_elocation = (InStr(XML_slocation, lines_array(1, linecounter), "</") - 1)
Cells(LDRC_Array(1, Line_Index) + XMLcounter - 1, LDRC_Array(1, 7)) = Mid(lines_array(1, linecounter), XML_slocation + 1, XML_elocation - XML_slocation)
' removes the extra 18 chars AXL/XML tags for username
If XMLcounter = 12 Then
Cells(LDRC_Array(1, Line_Index) + XMLcounter - 1, LDRC_Array(1, 7)) = Mid(Cells(LDRC_Array(1, linecounter) + XMLcounter - 1, LDRC_Array(1, 7)), 18)
End If
Else
Cells(LDRC_Array(1, linecounter) + XMLcounter - 1, LDRC_Array(1, 7)) = ""
End If
Next
End If
NextThis section uses the Index tag to determine which line it will populate on the new device. Each line will have a unique index value starting with 1. Note, the index value does not necessarily match the physical line position from the copied phone. The index value of 1 simply means it is the first line, index 2 means it is the second line. The second line may actually be in the 5th physical line slot from the copied phone.
To find out where the lines are actually placed physically on the phone, you need to use the getSoftKeyTemplate API call. Then read the new XML string to determine the actual detailed placement of lines, BLFs Speedials, etc.. In our environment, we rarely use these so I made the decision not to include them for now. This decision is why I call the app the ‘phone copy app’ and not the ‘super phone copy app’.
Once the lines have been read and written, the sheet looks like this:
This concludes the getphone section, I’ve only included the coding that shows how the basic concepts work. I didn’t include the input validation, error checking or the clear cells subs, if anyone is interested in something not shown here, let me know.
Next I’ll cover building the most complicated Soap string of this application with the addphone API.
Cisco Collaboration and Excel 