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