continued from Pt1
2) Scripts in Excel, Access, whatever other program you like to use
Looking back I should have done this next task in Access where I get to use sql commands, but everybody has Excel, so this seemed like a better choice should I get hit by a bus or something.
I’m not a professional programmer. I know I do things that are not necessarily correct or pretty. I have a tendency to use functions instead of subs because I like to use the return value of the function during debug. I’m sure I have other bad programming habits that would drive some people crazy, but at the end of the day I can get the job done and make my life easier when the day of a change comes.
Here’s some code that takes a log file, dumps it into a new sheet with a timestamp and then pulls the vlan info I need, Vlan ID, Root Bridge, and any blocking ports into an existing sheet. It will do this for Cisco IOS switches, CatOS switches, and JunOS switches.
What we’re expecting in the log files is pretty specific here: CatOS “show spantree x” output, IOS “show spanning-tree” output, and Junos “show spanning-tree bridge” and “show spanning-tree interface x” output. You can tweak this code pretty easily to look for other things…I know I’m holding onto it for future tasks.
‘this imports a file which must be named with the exact hostname of the device
‘and parses the file displaying the vlans: root bridge and bridge ID
‘and the interfaces: forwarding or blocking per vlan
Sub Main()
Dim RouterId As String
Dim PromptTxt As String
Dim throwaway As Integer
Dim filePath As String
Dim devicetype As String
Dim whileBreaker As Integer
Dim sheetname As String
Dim SheetList(10, 10) As String
‘WS is current worksheet and opens a new sheet at the beginning of the run
‘I might need to move this to the functions that import the files….
Dim WS As Worksheet
Set WS = Sheets.Add
‘get the log file to parse
filePath = Application.GetOpenFilename
‘chop out the routerid from the filepath
‘this assumes that the filename is the router-id
RouterId = GetFilenameFromPath(filePath)
RouterId = Left(RouterId, Len(RouterId) – 4)
‘so the output of a couple of switches changes with the version. some didn’t have a > others did…quick fix below
If RouterId = “switch3” Or RouterId = “switch4” Then
RouterId = RouterId & “>”
End If
‘asks for the type of file to parse
whileBreaker = 0
While whileBreaker = 0
PromptTxt = “Enter device type. Only CatOS, iOS, and Junos are accepted”
devicetype = InputBox(PromptTxt)
If devicetype = “CatOS” Or devicetype = “iOS” Or devicetype = “Junos” Then
whileBreaker = 1
End If
Wend
‘Changes current sheet name to reflect the file about to be read in
sheetname = Time()
sheetname = Replace(sheetname, “:”, “-“)
sheetname = RouterId & ” ” & Replace(sheetname, “/”, “-“)
ActiveSheet.Name = sheetname
‘import the file into the new sheet
ImportFile (filePath)
‘Parse the output sheet based on device type
throwaway = Parselog(sheetname, RouterId, devicetype)
End Sub
________________________________________________________________________
Function GetFilenameFromPath(ByVal strPath As String) As String
‘ Returns the rightmost characters of a string upto but not including the rightmost ‘\’
‘ e.g. ‘c:\winnt\win.ini’ returns ‘win.ini’
If Right$(strPath, 1) <> “\” And Len(strPath) > 0 Then
GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) – 1)) + Right$(strPath, 1)
End If
End Function
________________________________________________________________________________________________
Sub ImportFile(Logfilepath As String)
‘takes the file name, reads it into a string, then uses WriteToExcel to line import it into a new sheet
Dim Streng As String
Dim StrFileArray() As String
Dim hFile As Long
hFile = FreeFile
Open Logfilepath For Input As #hFile
Streng = Input$(LOF(hFile), hFile)
Close #hFile
StrFileArray = Split(Streng, vbCrLf)
WriteToExcel (StrFileArray)
End Sub
____________________________________________________________________________________________________
Function WriteToExcel(StrArray)
‘takes the array output from ImportFile and writes it into the current sheet starting at A1
Dim counter As Integer
Dim cellname As String
For counter = LBound(StrArray) To UBound(StrArray)
cellname = “A” & (counter + 1)
Range(cellname).Value = StrArray(counter)
Next counter
End Function
________________________________________________________________________________________________________
Function Parselog(ByVal workingSheetname As String, switchname As String, devicetype As String) As Integer
Dim showVlancells() As String
Dim DRCells() As String
Dim ARTemp(4) As String
Dim BlockingCells() As String
Dim searchString As String
Dim bFound As Boolean ‘Flag
Dim sRange As Range
Dim rowCounter As String
Dim Lastcell As String
Dim throwaway As Integer
Dim sheetname As Worksheet
Dim counter As Integer
Dim rFnd As Range
Dim iArr As Integer ‘ Counter for Array
Dim rFirstAddress ‘ Address of the First Find
Dim x As Integer
‘count how much to search and set sheetname to activesheet
Lastcell = Range(“A65536”).End(xlUp).Row
rowCounter = “A2:A” & Lastcell
Set sheetname = ActiveSheet
‘start parsing based on CatOS
‘__________________________________________________CATOS_________________________________________________
If devicetype = “CatOS” Then
searchString = ” show spantree”
searchString = switchname & searchString
‘look for all instances of “routerid show spantree ” and record those cells
bFound = FindAll(searchString, sheetname, rowCounter, showVlancells())
‘look between values in showVlancells()for designated root cells
‘set the counter properly to not error at the end of the sheet
Erase DRCells()
For counter = 1 To UBound(showVlancells)
searchString = “Designated Root ”
If counter = UBound(showVlancells) Then
rowCounter = showVlancells(counter) & “:A” & Lastcell
Else
rowCounter = showVlancells(counter) & “:” & showVlancells(counter + 1)
End If
‘start the search for DR
Set rFnd = Nothing
Set rFnd = sheetname.Range(rowCounter).Find(What:=searchString, LookIn:=xlValues, LookAt:=xlPart)
ReDim Preserve DRCells(counter)
If Not rFnd Is Nothing Then
DRCells(counter) = rFnd.Address
Else
DRCells(counter) = ” ”
End If
Next counter
‘now I have 1:1 arrays with the vlan number and DR…at least I should
throwaway = MsgBox(“These should match and be one more than the count from the SecureCRT script” & vbCrLf & UBound(showVlancells) & vbCrLf & UBound(DRCells), vbOKOnly)
‘now I need to get all the blocking ports
ReDim BlockingCells(UBound(showVlancells), 4)
For counter = 1 To UBound(showVlancells)
searchString = “blocking”
If counter = UBound(showVlancells) Then
rowCounter = showVlancells(counter) & “:A” & Lastcell
Else
rowCounter = showVlancells(counter) & “:” & showVlancells(counter + 1)
End If
bFound = FindBlocking(searchString, sheetname, rowCounter, ARTemp())
‘only copy elements that matter
If ARTemp(1) <> “” Then
‘throwaway = MsgBox(ARTemp(1), vbOKOnly)
For x = 1 To UBound(ARTemp)
BlockingCells(counter, x) = ARTemp(x)
Next x
End If
Next counter
throwaway = DumpToOutputCat(showVlancells(), DRCells(), BlockingCells(), workingSheetname, switchname)
End If
‘_______________________________________________________IOS _____________________________________________________
If devicetype = “iOS” Then
searchString = “VLAN0”
‘look for all instances of “routerid show spantree ” and record those cells
bFound = FindAll(searchString, sheetname, rowCounter, showVlancells())
‘look between values in showVlancells()for designated root cells
‘set the counter properly to not error at the end of the sheet
Erase DRCells()
For counter = 1 To UBound(showVlancells)
searchString = “Root ID ”
If counter = UBound(showVlancells) Then
rowCounter = showVlancells(counter) & “:A” & Lastcell
Else
rowCounter = showVlancells(counter) & “:” & showVlancells(counter + 1)
End If
‘start the search for DR
Set rFnd = Nothing
Set rFnd = sheetname.Range(rowCounter).Find(What:=searchString, LookIn:=xlValues, LookAt:=xlPart)
ReDim Preserve DRCells(counter)
‘ios output isn’t formatted friendly for this kind of search so I need to increment the drcells up one
If Not rFnd Is Nothing Then
Set rFnd = rFnd.Offset(1, 0)
DRCells(counter) = rFnd.Address
Else
DRCells(counter) = ” ”
End If
Next counter
‘now I have 1:1 arrays with the vlan number and DR…at least I should
throwaway = MsgBox(“These should match and be one more than the count from the SecureCRT script” & vbCrLf & UBound(showVlancells) & vbCrLf & UBound(DRCells), vbOKOnly)
‘now I need to get all the blocking ports
ReDim BlockingCells(UBound(showVlancells), 4)
For counter = 1 To UBound(showVlancells)
searchString = “BLK”
If counter = UBound(showVlancells) Then
rowCounter = showVlancells(counter) & “:A” & Lastcell
Else
rowCounter = showVlancells(counter) & “:” & showVlancells(counter + 1)
End If
bFound = FindBlocking(searchString, sheetname, rowCounter, ARTemp())
‘only copy elements that matter
If ARTemp(1) <> “” Then
‘throwaway = MsgBox(ARTemp(1), vbOKOnly)
For x = 1 To UBound(ARTemp)
BlockingCells(counter, x) = ARTemp(x)
Next x
End If
Next counter
throwaway = DumpToOutputiOS(showVlancells(), DRCells(), BlockingCells(), workingSheetname, switchname)
End If
‘______________________________________________________Junos Section__________________________________________
If devicetype = “Junos” Then
searchString = “STP bridge parameters for VLAN ”
‘look for all instances of “STP bridge parameters for VLAN ” and record those cells
bFound = FindAll(searchString, sheetname, rowCounter, showVlancells())
‘look between values in showVlancells()for designated root cells
‘set the counter properly to not error at the end of the sheet
Erase DRCells()
For counter = 1 To UBound(showVlancells)
searchString = ” Root ID”
If counter = UBound(showVlancells) Then
rowCounter = showVlancells(counter) & “:A” & Lastcell
Else
rowCounter = showVlancells(counter) & “:” & showVlancells(counter + 1)
End If
‘start the search for DR
Set rFnd = Nothing
Set rFnd = sheetname.Range(rowCounter).Find(What:=searchString, LookIn:=xlValues, LookAt:=xlPart)
ReDim Preserve DRCells(counter)
If Not rFnd Is Nothing Then
DRCells(counter) = rFnd.Address
Else
DRCells(counter) = ” ”
End If
Next counter
throwaway = MsgBox(“These should match and be one more than the count from the SecureCRT script” & vbCrLf & UBound(showVlancells) & vbCrLf & UBound(DRCells), vbOKOnly)
‘ ‘now I have 1:1 arrays with the vlan number and DR…at least I should
‘instead of returning the cells that contain the string I just want a pop-up to tell me that something is blocking
‘because I’ll have to manually figure out why it’s blocking anyway
searchString = “BLK”
Set rFnd = Nothing
Set rFnd = sheetname.Range(rowCounter).Find(What:=searchString, LookIn:=xlValues, LookAt:=xlPart)
If Not rFnd Is Nothing Then
throwaway = MsgBox(“Something is blocking in cell: ” & rFnd.Address, vbOKOnly)
End If
throwaway = DumpToOutputJunos(showVlancells(), DRCells(), BlockingCells(), workingSheetname, switchname)
End If
End Function
______________________________________________________________________________________________________________
Function FindAll(ByVal searchString As String, ByRef sheetname As Worksheet, ByRef rowCounter As String, ByRef showVlancells() As String) As Boolean
‘ ————————————————————————————————————–
‘ FindAll – To find all instances of the1 given string and return the row numbers.
‘ If there are not any matches the function will return false
‘ ————————————————————————————————————–
‘On Error GoTo Err_Trap
Dim rFnd As Range ‘ Range Object
Dim iArr As Integer ‘ Counter for Array
Dim rFirstAddress ‘ Address of the First Find
Dim throwaway As Integer
Dim tossstring As String
‘ —————–
‘ Clear the Array
‘ —————–
Erase showVlancells
Set rFnd = sheetname.Range(rowCounter).Find(What:=searchString, LookIn:=xlValues, LookAt:=xlPart)
If Not rFnd Is Nothing Then
rFirstAddress = rFnd.Address
Do Until rFnd Is Nothing
iArr = iArr + 1
ReDim Preserve showVlancells(iArr)
showVlancells(iArr) = rFnd.Address ‘ rFnd.Row ‘ Store the Row where the text is found
Set rFnd = sheetname.Range(rowCounter).FindNext(rFnd)
If rFnd.Address = rFirstAddress Then Exit Do ‘ Do not allow wrapped search
Loop
FindAll = True
Else
‘ ———————-
‘ No Value is Found
‘ ———————-
FindAll = False
End If
‘ ———————–
‘ Error Handling
‘ ———————–
Err_Trap:
If Err <> 0 Then
MsgBox Err.Number & ” ” & Err.Description, vbInformation, “Find All”
Err.Clear
FindAll = False
Exit Function
End If
End Function
____________________________________________________________________________________________________________
Function FindBlocking(ByVal searchString As String, ByRef sheetname As Worksheet, ByRef rowCounter As String, ByRef ARTemp() As String) As Boolean
‘ ————————————————————————————————————–
‘ FindAll – To find all instances of the1 given string and return the row numbers.
‘ If there are not any matches the function will return false
‘ ————————————————————————————————————–
‘On Error GoTo Err_Trap
Dim rFnd As Range ‘ Range Object
Dim iArr As Integer ‘ Counter for Array
Dim rFirstAddress ‘ Address of the First Find
Dim throwaway As Integer
Dim tossstring As String
‘ —————–
‘ Clear the Array
‘ —————–
Erase ARTemp
Set rFnd = sheetname.Range(rowCounter).Find(What:=searchString, LookIn:=xlValues, LookAt:=xlPart)
If Not rFnd Is Nothing Then
rFirstAddress = rFnd.Address
Do Until rFnd Is Nothing
iArr = iArr + 1
‘ ReDim Preserve ARTemp(iArr) ‘this may need to come back later
ARTemp(iArr) = rFnd.Address ‘ rFnd.Row ‘ Store the Row where the text is found
Set rFnd = sheetname.Range(rowCounter).FindNext(rFnd)
If rFnd.Address = rFirstAddress Then Exit Do ‘ Do not allow wrapped search
Loop
FindBlocking = True
Else
‘ ———————-
‘ No Value is Found
‘ ———————-
FindBlocking = False
End If
‘ ———————–
‘ Error Handling
‘ ———————–
Err_Trap:
If Err <> 0 Then
MsgBox Err.Number & ” ” & Err.Description, vbInformation, “Find All”
Err.Clear
FindBlocking = False
Exit Function
End If
End Function
Function DumpToOutputCat(vlan() As String, DR() As String, Blocking() As String, sheetname As String, switchname As String)
‘Dim workingRow As Integer
‘Dim wokingCol As String
Dim workcounter As Integer
Dim Lastcell As Integer
Dim cRange As String
Dim throwaway As Integer
Dim vlanNumber() As String
Dim vlanid As String
Dim drID As String
Dim drNumber() As String
Dim blockint() As String
Dim x
Dim blockcellId() As String
Dim blockId As String
‘need to make Output the working sheet
Sheets(“Output”).Select
‘find the end of the sheet
Lastcell = Range(“A65536”).End(xlUp).Row
‘dump the output of vlans
For workcounter = 1 To UBound(vlan)
‘write the vlan ID’s
cRange = “B” & Lastcell + workcounter
vlanNumber() = Split(Sheets(sheetname).Range(vlan(workcounter)).Value, ” “)
vlanid = vlanNumber(UBound(vlanNumber))
Range(cRange).Value = vlanid
‘write the switch name
cRange = “A” & Lastcell + workcounter
Range(cRange).Value = switchname
‘write the DR
cRange = “C” & Lastcell + workcounter
Erase drNumber
drID = ” ”
If DR(workcounter) <> ” ” Then
drNumber() = Split(Sheets(sheetname).Range(DR(workcounter)).Value, ” “)
drID = drNumber(UBound(drNumber))
End If
Range(cRange).Value = drID
‘now you need to write the blocking ports….
cRange = “D” & Lastcell + workcounter
Erase blockcellId
For x = 1 To UBound(Blocking, 2)
ReDim Preserve blockcellId(x)
If Blocking(workcounter, x) <> “” Then
blockcellId(x) = Sheets(sheetname).Range(Blocking(workcounter, x)).Value
blockint = Split(blockcellId(x), ” “)
blockcellId(x) = blockint(1)
End If
Next x
blockId = Join(blockcellId, ” “)
Range(cRange).Value = blockId
Next workcounter
End Function
_______________________________________________________________________________________________________________
Function DumpToOutputJunos(vlan() As String, DR() As String, Blocking() As String, sheetname As String, switchname As String)
‘Dim workingRow As Integer
‘Dim wokingCol As String
Dim workcounter As Integer
Dim Lastcell As Integer
Dim cRange As String
Dim throwaway As Integer
Dim vlanNumber() As String
Dim vlanid As String
Dim drID As String
Dim drNumber() As String
Dim blockint() As String
Dim x
Dim blockcellId() As String
Dim blockId As String
‘need to make Output the working sheet
Sheets(“Output”).Select
‘find the end of the sheet
Lastcell = Range(“A65536”).End(xlUp).Row
‘dump the output of vlans
For workcounter = 1 To UBound(vlan)
‘write the vlan ID’s
cRange = “B” & Lastcell + workcounter
vlanNumber() = Split(Sheets(sheetname).Range(vlan(workcounter)).Value, ” “)
vlanid = vlanNumber(UBound(vlanNumber))
Range(cRange).Value = vlanid
‘write the switch name
cRange = “A” & Lastcell + workcounter
Range(cRange).Value = switchname
‘write the DR
cRange = “C” & Lastcell + workcounter
Erase drNumber
drID = ” ”
If DR(workcounter) <> ” ” Then
drNumber() = Split(Sheets(sheetname).Range(DR(workcounter)).Value, ” “)
drID = drNumber(UBound(drNumber))
End If
Range(cRange).Value = drID
‘now you need to write the blocking ports….
cRange = “D” & Lastcell + workcounter
Erase blockcellId
Next workcounter
End Function
___________________________________________________________________________________________________________
Function DumpToOutputiOS(vlan() As String, DR() As String, Blocking() As String, sheetname As String, switchname As String)
‘Dim workingRow As Integer
‘Dim wokingCol As String
Dim workcounter As Integer
Dim Lastcell As Integer
Dim cRange As String
Dim throwaway As Integer
Dim vlanNumber() As String
Dim vlanid As String
Dim drID As String
Dim drNumber() As String
Dim blockint() As String
Dim x
Dim blockcellId() As String
Dim blockId As String
‘need to make Output the working sheet
Sheets(“Output”).Select
‘find the end of the sheet
Lastcell = Range(“A65536”).End(xlUp).Row
‘dump the output of vlans
For workcounter = 1 To UBound(vlan)
‘write the vlan ID’s
cRange = “B” & Lastcell + workcounter
vlanNumber() = Split(Sheets(sheetname).Range(vlan(workcounter)).Value, ” “)
vlanid = vlanNumber(UBound(vlanNumber))
Range(cRange).Value = vlanid
‘write the switch name
cRange = “A” & Lastcell + workcounter
Range(cRange).Value = switchname
‘write the DR
cRange = “C” & Lastcell + workcounter
Erase drNumber
drID = ” ”
If DR(workcounter) <> ” ” Then
drNumber() = Split(Sheets(sheetname).Range(DR(workcounter)).Value, ” “)
drID = drNumber(UBound(drNumber))
End If
Range(cRange).Value = drID
‘now you need to write the blocking ports….
cRange = “D” & Lastcell + workcounter
Erase blockcellId
For x = 1 To UBound(Blocking, 2)
ReDim Preserve blockcellId(x)
If Blocking(workcounter, x) <> “” Then
blockcellId(x) = Sheets(sheetname).Range(Blocking(workcounter, x)).Value
blockint = Split(blockcellId(x), ” “)
blockcellId(x) = blockint(0)
End If
Next x
blockId = Join(blockcellId, ” “)
Range(cRange).Value = blockId
Next workcounter
End Function