Smarte Analyse von Log-Daten mit Excel und ElasticSearch

Smarte Analyse von Log-Daten

Data-Scientists möchten mit den Unternehmensdaten auch ohne aufwändige IT-Projekte jonglieren, um Erkenntnisse für Analysten und Stakeholdern zu gewinnen und bereitzustellen. Doch wie können Self-Service-Analyse-Tools wie Excel dazu verwendet werden, um Milliarden von Systemnachrichten zu analysieren? Dieser Frage werde ich in diesem Post nachgehen und mit Hilfe von Excel, ElasticSearch und Logstash eine mögliche Lösung aufzeigen. Als Grundlage dienen dabei ca. 30.000 zu Test- und Demonstrationszwecken generierte Log-Daten von Temperatursensoren (stark vereinfacht).

In vielen Unternehmen fallen Unmengen von Log-Daten in verschiedenen, oft unabhängigen, Systemen an. Um Informationen aus diesen Daten in Korrelation zu bringen, müssen diese in einem systemübergreifenden Storage abgelegt und analysierbar gemacht werden. Da der Fokus bei derartigen Lösungen auf statistischen Auswertungen und Echtzeitanalysen liegt, habe ich mich für ElasticSearch zur Ablage und für das Retrieval entschieden. Das Processing-Framework Logstash bietet sich hierbei als optimales Add-On für das Laden und Verarbeiten von massenhaften Log-Daten unterschiedlichster Formate an.

Im weiteren Beitrag gehe ich nicht weiter auf die Aufbereitung und Ablage der Log-Daten ein, sondern auf die Bereitstellung und Analyse. Die hier verwendeten Beispiele wurden zu Demonstrationszwecken generiert.

Bereitstellen der Analyseschnittstelle

Ab der Version 1.1.0 unterstützt die ElasticSearch sogenannte Search Templates. Diese Funktion ermöglicht es, komplexe Analyseabfragen vorher zu definieren und über die API oder in Form einer entsprechenden JSON-Datei (mustache file) auf die Suchschnittstelle zu registrieren.

Für die Demonstration soll dem Data-Scientist eine Schnittstelle zur Ad-hoc-Abfrage von aggregierten Informationen (Realtime Aggregation) für Temperatursensor-Daten bereitgestellt werden. Dafür bieten sich beispielsweise die Histogramm Facetten an. Mit deren Hilfe erzeugt die ElasticSearch in einem vorgegebenen Intervall ein Histogramm zur Abfragezeit.

Die Standard-Facetten können leider nur in Form einer Liste abgefragt werden. Um dem Data-Scientist jedoch die Informationen gleich gruppiert nach dem entsprechenden Log-Typen (Info, Warning, Error) bereitzustellen, können so genannte Aggregations verwendet werden. Diese ermöglichen eine Verschachtelung von verschiedenen Facetten und Metriken, was im konkreten Anwendungsfall heißt, dass der Analyst die Daten direkt aggregiert für die Darstellung (beispielsweise in einem gestapelten Diagramm) und weiteren Verarbeitung erhält. Der nachfolgende Graph zeigt die Aggregation für diesen Anwendungsfall.

Um die Histogramme für alle Temperatur-Sensoren zu erzeugen, wurden diese entsprechend für die einzelnen Felder kopiert und unterschiedlich benannt. Da in diesem simplen Anwendungsfall nur die Aggregations abgefragt werden sollen, wurde für die Query „match_all“ verwendet. Die vollständige Mustache-Datei kann unter dem nachfolgenden Link heruntergeladen werden und muss im Ordner config\scripts\ abgelegt werden.

Nachdem das Query-Template erfolgreich geladen wurde, kann dieses durch die nachfolgende Abfrage getestet werden (Sense-Abfrage):

      POST /_search/template
      {
       "template": "aggregation_histogram"
      }

Wurde das Template erfolgreich geladen, so befinden sich die Ergebnisse (Buckets) der Aggregation unterhalb der „aggregations“ Sektion innerhalb des Ergebnis-JSON.

Einbinden der Analyseschnittstelle in Excel

Um die Daten mit Hilfe von Excel visualisieren zu können, muss zuerst die ElasticSearch REST-Such-Schnittstelle angebunden werden. Dafür kann das Self-Service Business-Intelligence Add-In PowerQuery (PQ) verwendet werden (LINK). Mit dieser Funktion stellt Excel Benutzern eine einfach bedienbare Toolbox zur Anbindung externer Datenquellen (lightweight ETL) zur Verfügung, wie in diesem Beispiel ElasticSearch.

Im Standard-Funktionsumfang bietet PQ leider nur die Möglichkeit Web-Quellen mit Get-Request-Parametern abzufragen. Da es jedoch in der aktuellen ElasticSearch-Version nicht möglich ist, das Search-Template über einen GET-Request-Parameter zu definieren, muss man sich mit Hilfe der PQ-Syntax einen POST-Request inkl. des benötigten Bodies selbst erzeugen. Dazu muss wie folgt vorgegangen werden:

1. Hinzufügen einer Blank-Query

2. Starten des Advanced Editors 

3. Abfragen der Analyseschnittstelle

Dieser Schritt ist beim Einbinden der ElasticSearch in Excel der entscheidende. Wie im nachfolgenden Listing ersichtlich ist, wird die Funktion Web.Contents() mit den ElasticSearch-Parametern für die Template Search-Schnittstelle und dem entsprechenden Body zur Definition des Templates aufgerufen.

let
     Source = Json.Document(Web.Contents("http://localhost:9200/bauhans/_search/template, 
     [Content=Text.ToBinary("{""template"": ""aggregation_histogram""}")])),
     aggregations = Source[aggregations]
in
   aggregations

4. Transformation der Daten

Nach dem der Inhalt im Advanced Editor fehlerfrei gespeichert werden konnte, sollten nun die drei vordefinierten Histogramme bereitstehen.

In den nachfolgenden Schritten werden die Daten per „Drill-Down“ für ein Diagramm (temp_in_histogram) in die geeignete Form gebracht. Dazu bietet PQ die Funktion „Into Table“ mit der die einzelnen Einträge in die für Excel benötigt Tabellenstruktur transformiert werden können.

Anschließend werden die Daten durch wiederholtes Aufklappen der verschachtelten Einträge in die erste Normalform gebracht. Nachdem die Aggregations bis zum letzten Blattelement aufgeklappt wurden, sieht man in der Kopfzeile den Pfad aus der JSON-Struktur als Spaltenbezeichner.

Um für den Endbenutzerprozess ein einfacheres Handling mit den Daten zu ermöglichen, ist eine Umbenennung der Spalten in sprechende Namen empfohlen. Zum Schluss müssen die Spalten in nummerische Werte umgewandelt werden, da die Daten aus dem JSON-Response keinem Datentypen zugeordnet sind und somit als Zeichenkette interpretiert werden (Spalte wählen → Change Type).

5. Speichern und Bereitstellen der Daten

Nach dem die Daten in das benötigte Format überführt wurden, können die gesamten Schritte als Anfrage gespeichert und verwendet werden. Hierzu bietet Excel die Möglichkeit die Ergebnisse entweder direkt in ein Sheet zu laden oder ab der Version 2013 in ein Data Model. Ich bevorzuge die Nutzung des Data Models da sich dies transparent integriert und die Nutzung innerhalb von Powerpivot ermöglicht.

Ein weiterer großer Vorteil bei der Nutzung von der Excel Power-Tools ist die integrierte Möglichkeit Anfragen im Unternehmen mit anderen zu teilen. So könnte der Data Scientist (Experte) über diesen Weg die Daten aufbereiten und den Analysten und Stakeholdern zur weiteren Analyse zur Verfügung stellen.

Visualisierung der Daten mit PowerView

Nach dem die Daten mittels Power Query bereitgestellt wurden, sollen diese als gestapeltes Balkendiagramm dargestellt werden. Dazu bieten sich die Excel Power View Funktionalität an. Power View ermöglicht eine Add-Hoc-Erstellung von interaktiven Dashboards.

Nach dem Hinzufügen eines neuen Power View Diagramms können die betreffenden Felder, wie im nachfolgenden Screenshot ersichtlich ist, selektiert und den entsprechenden Zielfeldern zugewiesen werden.

Mit Hilfe der neuen Office 365 und SharePoint-Komponenten können solche Analysedashboards als Apps exportiert und mit anderen geteilt werden.

Fazit

Die Integration von ElasticSearch in Excel über die Standard REST-Schnittstelle mit Hilfe von Power Query stellt eine einfache Möglichkeit zur Datenvisualisierung, neben bekannten Tools wie Kibana, dar. Zudem können komplexe Anfragen mit Hilfe der ElasticSearch-Query-Templates so ausgelagert werden, dass auch ohne tiefgreifendes ElasticSearch-Know-How eine Anbindung und Analyse durchgeführt werden kann.

Ein weiterer nennenswerter Vorteil besteht in der Flexibilität und Einfachheit, die erstellten Power-View-Dashboards in einer vorhandenen SharePoint-Infrastruktur zu publizieren.

Neben den gezeigten „einfachen“ Aggregationen bietet die ElasticSearch, vor allem für die Analyse von Log-Daten, wie bspw. mit Percentiles Aggregation zur Ausreißeridentifikation, viele weitere Funktionen zur Realtime-Analyse von Massendaten.

Kommentare

Interessant: Geht das auch mit einer normalen Query (ohne Mustache-Template)?

Hallo, eine Abfrage der ElasticSearch ohne die Nutzung von Mustach-Templates ist möglich. Hierzu muss man nur die Anfrage entsprechend des nachfolgenden Beispiels anpassen (URL + Body):

let
  Source = Json.Document(Web.Contents("http://localhost:9200/bauhans/_search",[Content=Text.ToBinary("{""query"": {""match_all"":{}}}")])),
  hits = Source[hits]
in
  hits

Diese Anfrage liefert die Ergebnisse der match_all query. Jedoch habe ich mich für das Template entschieden, da Anfragen sehr komplex werden können und somit auch die Anfrage in PowerQuery.

VG
Matthias

 

 

Hi,

erst mal tolles Tutorial, hatte ich schon vor einiger Zeit so etwas gesucht.

Hab es aber für mich etwas angepasst um die Query schneller auszutauschen/anzupassen und übersichtlicher zu gestalten.

Einfach fürs "json-query" eine extra Variable definieren. Diese kann man dann auch ohne "Advanced Editor" dann einfach bearbeiten ohne die Anführungszeichen zu Escapen.

Beispiel:

<code>

let
json ="",
Source = Json.Document(Web.Contents("http://localhost:9200/_search",[Content=Text.ToBinary(json)])),
hits = Source[hits],
in
hits

</code>

Hi I am running following query. Then I am able to get following output.

let
Source = Json.Document(Web.Contents("http://10.1.37.98:9200/logstash-jira2/_search",[Content=Text.ToBinary("{""query"": {""match_all"":{}}}")])),
hits = Source[hits],
#"Converted to Table" = Record.ToTable(hits)
in
#"Converted to Table"

Output:

Name Value
total 233
max_score 1
hits [List]

But when I run following query I just able to get 10 rows of records. Like when I run hits2 = hits1{10} or more than 10 value it gives error "Expression.Error: There weren't enough elements in the enumeration to complete the operation".

let
content = "{
""query"":
{ ""match_all"": {} },
""_source"": [""owner""]}",
Source = Json.Document(Web.Contents("http://10.1.37.98:9200/logstash-jira2/_search",[Content=Text.ToBinary(content)])),
hits = Source[hits],
hits1 = hits[hits],
hits2 = hits1{0},
_source = hits2[_source],
#"Converted to Table" = Record.ToTable(_source)

in
#"Converted to Table"

So My question is

1) How to get all rows of records which is 233?
2) And how all the records value will come into tabular format?

Thanks
Deepesh

Neuen Kommentar schreiben