Thread: Excel and SNMP

Hey, Just wondering if this is possible (I'm sure anything is possible...)

I have a VBScript that pulls SNMP data from some devices.

It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE"

When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices)

The script looks like this :

Code:

'Script which reports the current temperature for the  device selected.

SET objshell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

'boolean value for debugging function below
bDebug = False

Dim arrDeviceID(), arrOutPut(), arrSorted()
i = 1
strSnmpWalk = "d:\server_apps\hp openview\bin\snmpwalk.exe " 'walks the temperature device id tree and retreive all the device id's
strSnmpGet = "d:\server_apps\hp openview\bin\snmpget.exe " ' retreive the individual temperature readings
strSnmpTableString = " .1.3.6.1.4.1.5528.100.4.1.1.1.10 " 'Table which holds all the temp sensor indexes

If WScript.Arguments.Count < 1 Then 'check to see if a device name has been entered
	WScript.Echo "Please enter a device name"
	WScript.Quit
End If

strNodeName = WScript.Arguments(0)

debug "Node Name - " & strNodeName

'Get all the temperature device identifications on the system using Temp Sensor Index
'must walk the tree to get the individual device id's
Set execDeviceIDGet = objShell.Exec(strSnmpWalk & strNodeName & strSnmpTableString) 'walk the temperature device index
Set objDeviceOutput = execDeviceIdGet.StdOut
debug "Getting the Device ID's from the Device"
Do While Not objDeviceOutput.AtEndOfStream
	ReDim Preserve arrDeviceID(i) 'pull the ID's and grow the array as necessary
	ReDim Preserve arrOutPut(i) 'pull the device lables and grow the array as necessary
	strTemp = objDeviceOutput.ReadLine 'Read Each line from the output and grab the Temperature device ID
	intPosition = InStrRev(strTemp, ":") + 1 'look for the occurance of the :
	arrDeviceID(i) = Trim(Mid(strTemp, intPosition, Len(strTemp))) 'parse the string to have only the Temperature Device ID left
	debug "Device " & i & " ID " & arrDeviceID(i)
	i = i + 1
Loop

debug "Getting the information from the Device ID's"
For i = 1 to Ubound(arrDeviceID) Step 1
	debug "Device ID " & arrDeviceID(i)
	SNMPGetLabel arrDeviceID(i) 'SUB to pull the lable for the ID
	SNMPGetTemperature arrDeviceID(i) 'SUB to pull the temperature for the device ID
Next

Sub SNMPGetLabel(strDeviceID)
	strSnmpLabel = " .1.3.6.1.4.1.5528.100.4.1.1.1.4." & strDeviceID 'this is the exact string for one device ID
	debug "Label String " & strSnmpLabel
	Set execDeviceLabel = objShell.Exec(strSnmpGet & strNodeName & strSnmpLabel) 'SNMPGET the Label from the Device ID
	Set objLabelOut = execDeviceLabel.StdOut
	Do While Not objLabelOut.AtEndOfStream ' Parse 
		strTemp = objLabelOut.ReadLine
		intPosition = InStrRev(strTemp, ":") + 1
		arrOutPut(i) = Trim(Replace(Mid(strTemp, intPosition, Len(strTemp)), vbTab, "")) 'parse the string to have only the Device Label is left
	debug "Label " & arrOutPut(i)
	Loop
	
End Sub

Sub SNMPGetTemperature(strDeviceID)
	strSnmpTempString = " .1.3.6.1.4.1.5528.100.4.1.1.1.7." & strDeviceID 'base unit temperature pod identificaiton number
	debug "Temp  String " & strSnmpTempString
	Set execTemperatureGet = objShell.Exec(strSnmpGet & strNodeName & strSnmpTempString) 'SNMPGET the temperature of the Device ID
	Set objTemperatureOutput = execTemperatureGet.Stdout
	Do While Not objTemperatureOutput.AtEndOfStream
		strTemp = objTemperatureOutput.ReadLine
		strTemp = Round(Right(strTemp, 9), 2)
		arrOutPut(i)  = arrOutPut(i) & " is at " & strTemp & "c"
		debug "Current Temp " & arrOutPut(i)
	Loop
End Sub

RemovePods 'Function below to remove unwanted data from the array

BubbleSort 'Function below to sort the information 

debug "Sorted"
For i = 1 to Ubound(arrSorted) Step 1 'output sorted data to screen
	WScript.Echo arrSorted(i) 
Next

' This Debug function basically allows me to turn screen output on and off with the boolean flag at the top of the script.
Function debug(strOutput)
	If bDebug Then
		WScript.Echo strOutput
	End If
End Function

Function RemovePods
	debug "Removing Pods Function"
	'This function will remove the  pods and base units from the output as they are not necessary for the temperature graph
	For i = 1 to Ubound(arrOutPut)
		If InStr(arrOutPut(i), "Pod") > 0 Then
			debug arrOutPut(i)
			arrOutPut(i) = ""
		ElseIf	InStr(arrOutPut(i), "Base") > 0 Then
			debug arrOutPut(i)
			arrOutPut(i) = ""
		End If
	Next
			j = 1
	'This will remove any empty lines from the array
	For i = 1 to Ubound(arrOutPut)
		If Len(arrOutPut(i)) > 1 Then
			ReDim Preserve arrSorted(j) 
			arrSorted(j) = arrOutPut(i)
			j = j + 1
		End If	
	Next
End Function

Function BubbleSort 'sort the array
	For i = Ubound(arrSorted) -1 to 0 Step -1
		For j = 0 to i
			If arrSorted(j) > arrSorted(j + 1) Then
				strTemp = arrSorted(j + 1)
				arrSorted(j + 1) = arrSorted(j)
				arrSorted(j) = strTemp
			End If
		Next
	Next
End Function

When I populate this data (by hand) into my temperature contour map in Excel, I get the following map
http://farm4.static.flickr.com/3276/...af265c3794.jpg

Now, That works fine but you can imagine how long it takes me to populate 42 cells with data that I read from a dos screen.

What I would like to do is somehow get this to be a little more dynamic. I want to (ideally) have a button on my excel page which when pressed would go and get the temperature from each of the 42 temperature devices and put the information in the spreadsheet for me.

So, Thanks for reading this far and finally my question

Where should I start looking to see if Excel can somehow run a variation of my VBScript to pull the information from the devices and populate my spreadsheet for me?

Thanks again for any assistance you can offer.

Zach

So, I guess I'll answer my own question here.

I added a button to my spreadsheet and used VBA to run a script which pulls the information from the probes and updates each cell with the appropriate information. Here is the code if anyone is interested.

Code:

Private Sub UpdateTemperatures_Click()
'These are the values for the script
    Dim objShell, strSNMPGET, strDeviceID, strSnmpTempString, strTemp
    Const strNodeName = "cam1"
    Set objShell = CreateObject("WScript.Shell")
    strSNMPGET = "h:\snmpget\snmpget.exe "
    
'These are the cells which require their information updated from the script
    Dim arrCells
    arrCells = "C4, D4, E4, F4, G4, H4, I4, J4, C7, D7, E7, F7, G7, H7, I7, J7, K7, L7, M7, N7, O7"
    arrCells = Split(arrCells, ",")
'Now that the cells are split, get the temperature for each one
    For i = 0 To UBound(arrCells)
        'I placed the ID of each temperature probe as a comment on the cells, the script reads
        'the number in the comment and uses it to pull the info from the temperature probe.
        strDeviceID = Range(arrCells(i)).Comment.Text
        strSnmpTempString = " .1.3.6.1.4.1.5528.100.4.1.1.1.7." & strDeviceID
        'Now run the exe to pull the data from the device
        Set execTemperatureGet = objShell.Exec(strSNMPGET & strNodeName & strSnmpTempString)
        Set objTemperatureOutput = execTemperatureGet.Stdout
        
        'Read the data and round the number from decimal 6 places to 1
        Do While Not objTemperatureOutput.AtEndOfStream
            strTemp = objTemperatureOutput.ReadLine
            strTemp = Round(Right(strTemp, 9), 2)
        Loop
        
        'Place the rounded number in the cell
        Range(arrCells(i)).Value = strTemp
    Next
   
Set objShell = Nothing

End Sub

Curiuos to know what information was needed in the comment box?