Excel als Security-Monitoring-Tool Security-Log-Einträge mit Microsoft Excel aufbereiten und analysieren

Redakteur: Stephan Augsten

Microsoft Excel ist als Teil der Office Suite ohnehin schon auf vielen Firmenrechnern installiert, wird von IT-Security-Verantwortlichen aber selten bis nie genutzt. Oft ist Excel jedoch die schnellste Möglichkeit zur Aufbereitung von Firewall- und Proxy-Logs, Antivirus-Daten, Betriebssystem-Protokollen und Dateilisten. Security-Insider.de zeigt, wie die Datenanalyse mit Excel funktioniert.

Firma zum Thema

Microsoft Excel eignet sich wunderbar dafür, unübersichtliche Log-Dateien zu strukturieren und zu analysieren.
Microsoft Excel eignet sich wunderbar dafür, unübersichtliche Log-Dateien zu strukturieren und zu analysieren.
( Archiv: Vogel Business Media )

Sämtliche IT- und Security-Systeme erzeugen zahlreiche Informationen, die nützlicher sind als vielleicht angenommen – sofern man sie richtig analysiert und aufbereitet. Microsoft Excel ist hierfür vielleicht nicht immer die beste und eleganteste Lösung, dafür aber oft die schnellste und einfachste.

In den Vorgängerversionen von Excel 2007 war die maximale Anzahl der Zeilen auf 65.000 beschränkt. Microsoft hat diese Einschränkung aufgehoben, sodass man nun riesige Datenmengen in eine Excel-Tabelle importieren und darin bearbeiten kann.

Bevor wir zur versprochenen Aufbereitung und Analyse von Datensätzen kommen, widmen wir uns einer nützlichen und grundlegenden Funktion. Excel bietet die Möglichkeit, eine große Liste sich wiederholender Systembefehle zu erstellen.

Gehen wir beispielhaft von einer Liste mit 1.000 bestimmten Dateien aus, die aus dem Verzeichnis eines Systems gelöscht werden sollen. Leider liegen diese Dateien in verschiedenen Ordnern und können nicht aufgrund von Kriterien wie ihrer Größe oder dem letzten Zugriff ausgewählt werden.

Die Liste der Dateien kann durch einfaches Copy and Paste in die Spalte B einer leeren Excel-Datei übertragen werden. Anschließend schreibt man del “ in die erste Zeile der Spalte A.

(Archiv: Vogel Business Media)

Indem man (wie auf diesem Bild dargestellt) die Zelle auswählt und den Rahmen bis zum Ende der Liste nach unten zieht, erstellt man bequem 999 Kopien des Befehls. Fehlt nur noch ein Anführungszeichen in der Spalte C, das man ebenfalls mithilfe der Drag-Funktion kopieren kann.

Nun kann man die Excel-Liste in das Notepad einfügen und damit eine Batch-Datei für die 1.000 einzelnen Delete-Befehle erstellen. Hierfür ist es aber zunächst einmal erforderlich, die Tabs aus der Liste zu entfernen.

(Archiv: Vogel Business Media)

Hiefür markiert man (wie auf diesem Bild) eines der Tabs, kopiert es und ruft mit der Tastenkombination [Strg]+[H] die Ersetzen-Funktion des Editors auf. Beim Einfügen in das „Suchen nach“-Feld wird das Tab durch ein Kastensymbol dargestellt, das Feld „Ersetzen mit“ bleibt frei. Durch den Klick auf „Alle ersetzen“ sind in kürzester Zeit alle Tab-Stopps ausradiert.

Auf den folgenden Seiten widmen wir uns spezieller Excel-Funktionen, die man zur Analyse und Aufbereitung von Daten kennen sollte.

Seite 2: Welche Excel-Funktionen man beherrschen sollte.

Folgende Excel-Funktionen sollte man beherrschen:

  • Text in Spalten…
  • AutoFilter
  • Spezialfilter
  • Pivot-Tabellen-Bericht

Text in Spalten…: Diese Option unter dem Menüpunkt Daten ist zwar weitreichend bekannt – aber auch derart nützlich, dass sie nicht unerwähnt bleiben sollte. Sofern ein System seine Informationen in Form eines Strings ausgibt, kann man diesen mithilfe der „Text in Spalten“-Funktion wunderbar aufbrechen, indem man das Trennzeichen (Komma, Tab, Leerzeichen etc.) bestimmt.

Die einzelnen Werte lassen sich nun besser sortieren und untersuchen. Enthält ein Log beispielsweise Strings wie „Transaktionszeit (23) Millisekunden“, kann man dem eingeklammerten Wert eine eigene Spalte zuweisen.

Dies ist insbesondere dann hilfreich, wenn Daten automatisch durch ein Trennzeichen separiert sind, anschließend aber mittels eines zweiten Trennzeichens weiter heruntergebrochen werden müssen. Ein gutes Beispiel hierfür sind Logs aus dem Active Directory, die zahlreiche durch Kommata getrennte Felder, aber auch durch Tabs abgegrenzte Werte enthalten.

AutoFilter: Der AutoFilter findet sich unter dem Menüpunkt Daten > Filter und ergänzt die Tabelle um ein Drop-Down-Feld mit den einzelnen Werten, die in einer Spalte enthalten sind. Damit kann man sich nur jene Zeilen anzeigen lassen, die den jeweils ausgewählten Wert enthalten.

(Archiv: Vogel Business Media)

Dieses Bild erläutert diese Funktion am Beispiel eines Active-Directory-Logs. Hier wird der Autofilter verwendet, um nur die Nachrichten mit der Event-ID 540 (Erfolgreicher Log-on) anzuzeigen.

Seite 3: Excel-Daten mit dem Spezialfilter und Pivot-Tabellen aufbereiten

Excel-Daten mit dem Spezialfilter und Pivot-Tabellen aufbereiten

Spezialfilter: Der Spezialfilter ist ebenfalls im Menüpunkt Daten > Filter enthalten und ermöglicht es, nur eine Zeile für einen bestimmten Wert anzuzeigen. Auf diese Weise können sich wiederholende Einträge, die das Datenblatt unübersichtlich machen, schnell ausgeblendet werden. So kann man beispielsweise aus einem Authentifizierungs-Log eine überschaubare Liste sämtlicher Anwender generieren, auch wenn diese sich im Laufe des Log-Zeitraums mehrfach angemeldet haben.

Als weiteres Anwendungsbeispiel stelle man sich einen Dateiordner vor, der etliche Datei-Duplikate beinhaltet. Mit einem MD5-Hashing-Programm wie MD5 Deep kann man nun eine Liste mit den MD5-Hashes sämtlicher Dateien erstellen. Wendet man den Spezialfilter auf den MD5-Hash an, erhält man eine Liste, in der sämtliche Duplikate ausgeblendet sind.

Mithilfe der auf der ersten Seite beschriebenen Systembefehl-Funktion kann man anschließend nur die angezeigten Dateien in einen separaten Ordner kopieren und die übrig gebliebenen Dateien löschen.

Pivot-Tabellen: Mit dem PivotTable-Bericht im Menüeintrag Daten kann man Informationen grafisch aufbereiten und untersuchen. Gehen wir einmal davon aus, eine Antivirus-Lösung liefert ein Log mit einem Eintrag pro Alarmmeldung.

(Archiv: Vogel Business Media)

Wie hier dargestellt, lässt sich mithilfe einer Pivot-Tabelle schnell herausfinden, welche Viren welche Systeme infiziert haben und welche Systeme die meisten Viren beinhalten.

Während einfache Excel-Tabellen sich insbesondere für eine Analyse der Rohdaten eignen, erlauben es Pivot-Tabellen, ausgewählte Daten miteinander in Beziehung zu setzen und viel tiefgreifender auszuwerten. Gleichzeitig fassen Pivot-Tabellen identische Einträge zusammen und ermöglichen durch einen Doppelklick auf eine Zelle, die Liste auf bestimmte Details herunterzubrechen.

Fazit

Microsoft Excel eignet sich also insbesondere, um Log-Dateien mit Daten in zufälliger Reihenfolge zu sortieren und zu analysieren. Wie wir gezeigt haben, kann Excel ein hilfreiches Tool sein und ist noch dazu auf vielen Rechnern installiert.

(ID:2040071)