Showing results for 
Search instead for 
Did you mean: 

UCS XML API and Excel (Visual Basic)

This blog was generated from CDN blog

Created by: John McDonough on 01-07-2011 12:25:03 PM
Recently I was cleaning out my attic and came across some code in an Excel spreadsheet for retrieving Blades, Service Profiles (if associated to a blade) and the management IP address of the blade. Basically I created a macro and assigned it to a button, nothing ground breaking. However it was something I did early on and was pleased to see that when I tried it again today it worked! Screen shots are below.

I put a few Active X controls on a spreadsheet and associated the code below to a button.  Using the supplied values when the button is clicked a spreadsheet is populated.

So what is this good for
  • Launching a KVM (you have to write the code, although I think I will be posting a blog soon about KVM launching) **Update 8-2-2011**, I did!
  • Dynamic Inventory
  • VLAN stuff
  • VSAN stuff
  • Identity pools, UUID, MAC, WWNN, WWPN
  • Service Profile stuff
  • Etc...

By no means is this production ready or does it do error checking, just wanted to show how you could populate a spreadsheet with values from UCS. The commented out MsgBox are for debugging, uncomment if you want to see whats going on.

Lines 3 - 4
Basic setup, a string for the authentication cookie, UCS location and an integer

Line 6
Something you need for XML in VB

Lines 8 - 16
Setup and send the aaaLoign http POST request to UCS manger, then load the XML results into an XML object

Lines 18 -19
Get the authentication cookie from the returned XML

Lines 22 - 26
Construct and POST a configResolveClass query, then load the results into an XML object.  If you need to brush up on the UCS queries check out this post

Lines 28 - 31
Write out some column headers and set the output start row

Lines 33 - 45
Create a list of server nodes, cycle though them while listing out the things we want to know, Server Dn, Association state, Assigned Service Profile (if any) and the Management IP

Lines 47 - 49
Setup and send the aaaLogout http POST request to the UCS Manager to end the session

 2Private Sub CommandButton1_Click()
 3Dim sAuthCookie As String
 4  Dim sUri As String
 5  Dim i As Integer
 7  Dim objElem As MSXML2.IXMLDOMElement
 9  sUri = "http://" & ucsHost.Value & "/nuova"
10  Set http = CreateObject("MSXML2.ServerXMLHTTP")
11  Set objXML = CreateObject("MSXML2.DomDocument")
12  'MsgBox http.responseText
13  http.Open "POST", sUri, False
14  http.setRequestHeader "Content-Type", "text/xml"
15  http.Send "<aaaLogin inName=""" & ucsUser.Value & """ inPassword=""" & ucsPassword.Value & """ />"
16  'MsgBox http.responseText
17  objXML.LoadXML (http.responseText)
19  Set objElem = objXML.SelectSingleNode("//aaaLogin")
20  sAuthCookie = objElem.getAttribute("outCookie")
21  'MsgBox sAuthCookie
23  http.Open "POST", sUri, False
24  http.setRequestHeader "Content-Type", "text/xml"
25  http.Send "<configResolveClass classId=""computeBlade"" inHierarchical=""true""  cookie=""" & sAuthCookie & """ />"
26  'MsgBox http.responseText
27  objXML.LoadXML (http.responseText)
29  Cells(9, 3) = "Server Dn"
30  Cells(9, 4) = "Association"
31  Cells(9, 5) = "Service Profile"
32  Cells(9, 6) = "Management IP"
33  x = 11
34  Set NodeList = objXML.SelectNodes("configResolveClass/outConfigs/computeBlade")
36  For Each node In NodeList
37  Set childNode = node.SelectSingleNode("mgmtController/mgmtIf")
39      Cells(x, 3) = node.getAttribute("dn")
40      Cells(x, 4) = node.getAttribute("association")
41      Cells(x, 5) = node.getAttribute("assignedToDn")
42      Cells(x, 6) = childNode.getAttribute("extIp")
44      x = x + 1
45     'MsgBox node.getAttribute("dn") & childNode.getAttribute("extIp")
46  Next
48  http.Open "POST", sUri, False
49  http.setRequestHeader "Content-Type", "text/xml"
50  http.Send "<aaaLogout inCookie=""" & sAuthCookie & """ />"
51  'MsgBox http.responseText
52End Sub

Hope this helps.

John McDonough
Cisco Advanced Services
UCS Compute and Virtualization Practice
CreatePlease to create content
This widget could not be displayed.