IP-Adressliste mit Excel

Oft ist es sinnvoll einen IP Plan zu haben, um zu dokumentieren, ob und welche IP-Adressen noch frei sind, welche Netze in Benutzung sind und eine Menge anderer Gründe. Klar, es gibt viele Tools, die das anbieten, aber was ist, wenn man solch eine Liste selber generieren möchte? Ich habe mir dies kurz zur Aufgabe gemacht und mir eine IP-Adressliste mit Excel erzeugt. Die zu scannenden Netze werden als Arbeitsblatt erzeugt. Enthalten sind dort die Adressen, die gescannt werden sollen. Zusätzlich gibt es noch ein Feld für den Ping-Status, den DNS Namen falls vorhanden und etwaige Bemerkungen. Über VBA wird die IP-Adresse ausgelesen und ein ping versucht. Ist dies erfolgreich, überprüft das Script den DNS Namen eigenhändig und schreibt ihn in eine Zelle. Falls keine DNS-Auflösung stattfindet, übernimmt er die IP-Adresse.

Private Sub update_Click()
    Dim ws As Worksheet
    Dim x As Integer
    Dim i As Integer
    Dim Result As String
    Dim ip As String
    Dim arrSplitStrings() As String
    Dim excludeString As String
    
    
    x = 10 ' helfer Variable um die Zelle für die IP Bereiche zu bestimmen
    i = 2
    t_bereich = Worksheets("Settings").Range("A1:A50").Find("IP-Bereich").Row
    excludeString = Worksheets("Settings").Cells(9, 2).Value
    arrSplitStrings = Split(excludeString, ";")
    
    
    ' Bereich clearen   
    Worksheets("Überblick").Range("B10:B200").Clear
    'Gehe über jedes Arbeitsblatt
    For Each ws In Worksheets
        'Wenn dieses Arbeitsblatt nicht excluded werden soll, schreibe das Netz in die Übersicht
        If Not IsInArray(ws.Name, arrSplitStrings) Then
            Sheets("Überblick").Cells(x, 2) = ws.Name
            ' Suche den Namen des Netzwerkes in der Settings Arbeitsmappe
            Do While Not IsEmpty(Worksheets("Settings").Cells(t_bereich, 2))
                ' Wenn das Netz gefunden wurde, schreibe es in die Zelle
                If Worksheets("Settings").Cells(t_bereich, 2) = ws.Name Then
                    Sheets("Überblick").Cells(x, 3) = Worksheets("Settings").Cells(t_bereich, 3)
                End If
                ' erhöhe t_bereich, damit die nächste Zelle überprüft wird
                t_bereich = t_bereich + 1
            Loop

            'setze t_bereich wieder zurück
            t_bereich = Worksheets("Settings").Range("A1:A50").Find("IP-Bereich").Row
            
            'Solange es fortlaufende IPs gibt, führe ein Ping durch
            Do While Not IsEmpty(ws.Cells(i, 2))
                'Lese die IP aus
                ip = ws.Cells(i, 2).Value
                'Wenn ein Ping erfolgreich war, schreibe in die erste Zelle ein "S" für Success und färbe es grün
                If Ping(ip) Then
                    ws.Cells(i, 1).Value = "S"
                    ws.Cells(i, 1).Interior.Color = RGB(50, 205, 50)
                    'Wenn in der Zelle für die Namensauflösung noch nichts steht, versuche den Namen aufzulösen
                    If IsEmpty(ws.Cells(i, 3)) Then
                        'Namensauflösung zu der IP Adresse
                        ws.Cells(i, 3) = HostName(ip)
                    End If
                Else
                    'Wenn der Ping nicht erfolgreich war, schreibe "C" in die erste Zelle für Critical und färbe es rot
                    ws.Cells(i, 1).Value = "C"
                    ws.Cells(i, 1).Interior.Color = RGB(255, 120, 120)
                End If
                'erhöhe i um 1, damit die nächste IP gezogen wird
                i = i + 1
            Loop
            'setze i wieder zurück
            i = 2
            'erhöhe x für den nächsten IP-Bereich
            x = x + 1
        End If
    Next ws
    'Wenn alles fertig, gebe eine MessageBox aus
    MsgBox "Fertig!"
End Sub

In den obigen Code habe ich die Erklärung der wichtigsten Zeilen vorgenommen. Somit sollte dieser einfach zu verstehen sein. Sollte trotzdem etwas unklar sein, so fragt in den Kommentaren nach.

Weiter geht es mit der Ping Funktion. Relativ einfach ruft diese eine Shell auf und versucht die übergebene IP zu pingen. Je nach dem ob erfolgreich oder nicht, gibt es ein True oder False zurück. Durch den Parameter -n wird nur ein Paket verschickt. Mit Parameter -m haben wir eine Timeoutzeit von 1000ms und somit 1s definiert. Selbstverständlich dürfen die ICMP Pakete nicht von der Firewall verworfen werden. In dem Fall würde das Makro nicht korrekt funktionieren.

Function Ping(strcomputer)

Dim objshell, boolcode

Set objshell = CreateObject("wscript.shell")

boolcode = objshell.Run("ping -n 1 -w 1000 " & strcomputer, 0, True)

If boolcode = 0 Then

Ping = True

Else

Ping = False

End If

End Function

Ähnliches wie für den Ping gilt auch für den Hostname. Auch hier übergeben wir wieder die IP und es wird per Shell ein nslookup auf die IP gemacht. Erhalten wir den Namen so geben wir diesen zurück, ansonsten die IP.

Function HostName(ip$)
    Dim s As String, a
    s = CreateObject("Wscript.Shell").Exec("nslookup " & ip).StdOut.ReadAll
    a = Split(s, vbCrLf)

    If UBound(a) > 2 Then
        If Not a(3) = "" Then
            HostName = Right(a(3), Len(a(3)) - 9)
        Else
            HostName = ip
        End If
    End If

IsInArray ist eine Helferfunktion. Sie überprüft, ob der Wert im Array enthalten ist und gibt dementsprechend True oder False zurück.

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Da ich aus Sicherheitsgründen keine .xlsm Dateien mit Macros hochladen kann, stelle ich die Datei als .xlsx bereit.

Die oben genannten Funktionen müssen dementsprechend eigenst in Excel eingebunden werden, damit die IP-Adressliste mit Excel funktioniert. Alles andere sollte dann direkt funktionieren. Je nach Anzahl der Netze im Arbeitsblatt kann ein vollständiger Scan lange dauern. Unter anderem aus diesem Grund habe ich ein “exclude” Feld bei den Settings eingebaut.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.