Verwendung von PostgreSQL in SmartCollect SC²

SmartCollect SC² wird mit einem eingebauten PostgreSQL-Datenquellen-Plugin geliefert, mit dem Sie Daten aus einer PostgreSQL-kompatiblen Datenbank abfragen und visualisieren können.

1. Öffnen Sie das Seitenmenü, indem Sie auf das SmartCollect SC²-Symbol in der oberen Kopfzeile klicken.
2. Im Seitenmenü unter dem Symbol “Konfiguration” sollten Sie einen Link namens “Datenquellen” finden.
3. Klicken Sie auf die Schaltfläche “+ Datenquelle hinzufügen” in der oberen Kopfzeile.
4. Wählen Sie PostgreSQL aus dem Dropdown-Menü Typ.

Eine untere Grenze für die Variablen $__interval and $__interval_ms Variable. Es wird empfohlen, die Schreibfrequenz einzustellen, z. B. “1m”, wenn Ihre Daten jede Minute geschrieben werden. Diese Option kann auch in einem Dashboard-Panel unter Datenquellenoptionen überschrieben/konfiguriert werden. Es ist wichtig zu beachten, dass dieser Wert als Zahl formatiert werden muss, gefolgt von einer gültigen Zeitkennung. Zahl, gefolgt von einem gültigen Zeitbezeichner, z. B. “1m” (1 Minute) oder “30s” (30 Sekunden). Die folgenden Zeitbezeichner werden unterstützt:

Der Datenbankbenutzer, den Sie beim Hinzufügen der Datenquelle angeben, sollte nur SELECT-Berechtigungen für die angegebene Datenbank und die Tabellen haben, die Sie abfragen möchten. SmartCollect SC² prüft nicht, ob die Abfrage sicher ist. Die Abfrage kann jede SQL-Anweisung enthalten. Beispielsweise würden Anweisungen wie DELETE FROM user; und DROP TABLE user; ausgeführt. Um sich davor zu schützen, empfehlen wir hoch, einen speziellen PostgreSQL-Benutzer mit eingeschränkten Rechten anzulegen.

Beispiel:

 CREATE USER smartcollectreader WITH PASSWORD 'password';
 GRANT USAGE ON SCHEMA schema TO smartcollectreader;
 GRANT SELECT ON schema.table TO smartcollectreader;

Stellen Sie sicher, dass der Benutzer keine unerwünschten Berechtigungen von der öffentlichen Rolle erhält.

Sie finden den PostgreSQL-Abfrage-Editor auf der Registerkarte “Metriken” im Bearbeitungsmodus des Graph- oder Singlestat-Panels. Sie gelangen in den Bearbeitungsmodus, indem Sie auf den Panel-Titel und dann auf “Bearbeiten” klicken.

Der Abfrage-Editor hat einen Link namens Generated SQL, der nach der Ausführung einer Abfrage im Bearbeitungsmodus des Panels angezeigt wird. Klicken Sie darauf und er wird erweitert und zeigt die rohe interpolierte SQL-Zeichenkette, die ausgeführt wurde.

Wenn Sie zum ersten Mal in den Bearbeitungsmodus gehen oder eine neue Abfrage hinzufügen, wird SmartCollect SC² versuchen, den Abfragegenerator mit der ersten Tabelle vorzufüllen, die eine Zeitstempelspalte und eine numerische Spalte hat.

Im FROM-Feld wird SmartCollect SC² Tabellen vorschlagen, die sich im Suchpfad des Datenbankbenutzers befinden. Um eine Tabelle oder Ansicht auszuwählen, die sich nicht in Ihrem Suchpfad befindet können Sie manuell einen voll qualifizierten Namen (schema.table) wie “public.metrics” eingeben.

Das Feld “Time column” bezieht sich auf den Namen der Spalte, die Ihre Zeitwerte enthält. Die Auswahl eines Wertes für das Spaltenfeld “Metrik” ist optional. Wenn ein Wert ausgewählt wird, wird das Metrik-Spaltenfeld als Serienname verwendet.

Die metrischen Spaltenvorschläge enthalten nur Spalten mit einem Text-Datentyp (char,varchar,text). Wenn Sie eine Spalte mit einem anderen Datentyp als metrische Spalte verwenden möchten, können Sie den Spaltennamen mit einem Cast eingeben: ip::text. Sie können auch beliebige SQL-Ausdrücke in das Feld für die metrische Spalte eingeben, die auf einen Text-Datentyp ausgewertet werden, wie hostname || ' ' || container_name.

In der Zeile SELECT können Sie angeben, welche Spalten und Funktionen Sie verwenden wollen. In das Spaltenfeld können Sie anstelle eines Spaltennamens beliebige Ausdrücke schreiben, z. B. Spalte1 * Spalte2 / Spalte3.

Die verfügbaren Funktionen im Abfrage-Editor hängen von der PostgreSQL-Version ab, die Sie beim Konfigurieren der Datenquelle ausgewählt haben. Wenn Sie Aggregatfunktionen verwenden, müssen Sie Ihre Ergebnismenge gruppieren. Der Editor fügt automatisch ein GROUP BY time ein, wenn Sie eine Aggregatfunktion hinzufügen.

Der Editor versucht, diesen Teil der Abfrage zu vereinfachen und zu vereinheitlichen. Zum Beispiel:

Das obige erzeugt die folgende PostgreSQL SELECT-Klausel:

avg(tx_bytes) OVER (ORDER BY "time" ROWS 5 PRECEDING) AS "tx_bytes"

Sie können weitere Wertspalten hinzufügen, indem Sie auf die Plus-Schaltfläche klicken und “Spalte” aus dem Menü auswählen. Mehrere Wertspalten werden als separate Reihen im Diagrammfenster gezeichnet.

Um einen Filter hinzuzufügen, klicken Sie auf das Plus-Symbol rechts neben der WHERE-Bedingung. Sie können Filter entfernen, indem Sie auf den Filter klicken und “Entfernen” wählen. Ein Filter für den aktuell ausgewählten Zeitbereich wird automatisch zu neuen Abfragen hinzugefügt.

Um nach der Zeit oder einer anderen Spalte zu gruppieren, klicken Sie auf das Plus-Symbol am Ende der Zeile GROUP BY. Das Dropdown-Menü mit den Vorschlägen zeigt nur die Textspalten der aktuell ausgewählten Tabelle an, aber Sie können jede Spalte manuell eingeben. Sie können die Gruppierung entfernen, indem Sie auf das Element klicken und dann “Entfernen” wählen.

Wenn Sie eine Gruppierung hinzufügen, muss auf alle ausgewählten Spalten eine Aggregatfunktion angewendet werden. Der Query Builder fügt automatisch Aggregatfunktionen zu allen Spalten ohne Aggregatfunktionen hinzu, wenn Sie Gruppierungen hinzufügen.

SmartCollect SC² kann fehlende Werte auffüllen, wenn Sie nach Zeit gruppieren. Die Zeitfunktion nimmt zwei Argumente an. Das erste Argument ist das Zeitfenster, nach dem Sie gruppieren möchten, und das zweite Argument ist der Wert, mit dem SmartCollect SC² fehlende Elemente auffüllen soll.

Sie können in den Rohabfrage-Editormodus wechseln, indem Sie auf das Hamburger-Symbol klicken und “Editormodus umschalten” wählen oder indem Sie auf “SQL bearbeiten” unterhalb der Abfrage klicken.

Wenn Sie den Rohabfrage-Editor verwenden, stellen Sie sicher, dass Ihre Abfrage mindestens “ORDER BY time” und einen Filter für den zurückgegebenen Zeitbereich enthält.

Makros können innerhalb einer Abfrage verwendet werden, um die Syntax zu vereinfachen und dynamische Teile zu ermöglichen.

Wir planen, viele weitere Makros hinzuzufügen. Wenn Sie Vorschläge für Makros haben, die Sie gerne sehen würden, dann öffnen Sie ein Request unter open an issue in unserem GitHub-Repositorium.

Wenn die Abfrageoption “Format als” auf “Tabelle” eingestellt ist, können Sie grundsätzlich jede Art von SQL-Abfrage durchführen. Das Tabellenfeld zeigt automatisch die Ergebnisse aller Spalten und Zeilen an, die Ihre Abfrage liefert.

Abfrage-Editor mit Beispielabfrage:

Die Abfrage:

SELECT
  title as "Title",
  "user".login as "Created By",
  dashboard.created as "Created On"
FROM dashboard
INNER JOIN "user" on "user".id = dashboard.created_by
WHERE $__timeFilter(dashboard.created)

Sie können den Namen der Spalten des Tabellen-Panels steuern, indem Sie die reguläre “as”-Syntax für die Spaltenauswahl verwenden.

Das resultierende Tabellenfeld:

Wenn Sie Format als auf Zeitreihe setzen, z. B. für die Verwendung im Diagrammbedienfeld, dann muss die Abfrage eine Spalte mit dem Namen time zurückgeben, die entweder eine SQL datetime oder einen beliebigen numerischen Datentyp, der eine Unix-Epoche darstellt, zurückgibt. Jede Spalte außer time und metric wird als Wertspalte behandelt. Sie können eine Spalte namens “metric” zurückgeben, die als Metrikname für die Wertspalte verwendet wird. Wenn Sie mehrere Wertspalten und eine Spalte mit dem Namen metric zurückgeben, wird diese Spalte als Präfix für den Seriennamen verwendet (nur in SmartCollect SC² 5.3+ verfügbar).

Ergebnismengen von Zeitreihenabfragen müssen nach Zeit sortiert werden.

Beispiel mit metrischer Spalte:

SELECT
  $__timeGroup("time_date_time",'5m'),
  min("value_double"),
  'min' as metric
FROM test_data
WHERE $__timeFilter("time_date_time")
GROUP BY time
ORDER BY time

Beispiel für die Verwendung des Füllparameters im Makro $__timeGroup, um Nullwerte in Nullwerte zu konvertieren:

SELECT
  $__timeGroup("createdAt",'5m',0),
  sum(value) as value,
  measurement
FROM test_data
WHERE
  $__timeFilter("createdAt")
GROUP BY time, measurement
ORDER BY time

Beispiel mit mehreren Spalten:

SELECT
  $__timeGroup("time_date_time",'5m'),
  min("value_double") as "min_value",
  max("value_double") as "max_value"
FROM test_data
WHERE $__timeFilter("time_date_time")
GROUP BY time
ORDER BY time

Anstatt Dinge wie Server, Anwendung und Sensorname in Ihren metrischen Abfragen hart zu kodieren, können Sie an deren Stelle Variablen verwenden. Variablen werden als Dropdown-Auswahlfelder am oberen Rand des Dashboards angezeigt. Mit diesen Dropdown-Feldern können Sie die in Ihrem Dashboard angezeigten Daten leicht ändern.

Eine Einführung in die Schablonenfunktion und die verschiedenen Typen von Schablonenvariablen finden Sie unter Templates and variables.

Wenn Sie eine Template-Variable vom Typ Query hinzufügen, können Sie eine PostgreSQL-Abfrage schreiben, die Dinge wie Messungsnamen, Schlüsselnamen oder Schlüsselwerte zurückgeben, die als Dropdown-Auswahlfeld angezeigt werden.

Sie können zum Beispiel eine Variable haben, die alle Werte für die Spalte Hostname in einer Tabelle enthält, wenn Sie eine solche Abfrage in der Einstellung der Template-Variable Query angeben.

SELECT hostname FROM host

Eine Abfrage kann mehrere Spalten zurückgeben und SmartCollect SC² wird automatisch eine Liste daraus erstellen. Zum Beispiel gibt die folgende Abfrage eine Liste mit Werten von “Hostname” und “Hostname2” zurück.

SELECT host.hostname, other_host.hostname2 FROM host JOIN other_host ON host.city = other_host.city

Um zeitbereichsabhängige Makros wie $__timeFilter(column) in Ihrer Abfrage zu verwenden, muss der Aktualisierungsmodus der Template-Variable auf On Time Range Change gesetzt werden.

SELECT event_name FROM event_log WHERE $__timeFilter(time_column)

Eine weitere Möglichkeit ist eine Abfrage, die eine Schlüssel/Wert-Variable erstellen kann. Die Abfrage sollte zwei Spalten zurückgeben, die __text und __value heißen. Der Wert der Spalte __text sollte eindeutig sein (wenn er nicht eindeutig ist, wird der erste Wert verwendet). Die Optionen im Dropdown haben einen Text und einen Wert, so dass Sie einen Friendly-Namen als Text und eine ID als Wert haben können. Eine Beispielabfrage mit “hostname” als Text und “id” als Wert:

SELECT hostname AS __text, id AS __value FROM host

Sie können auch verschachtelte Variablen erstellen. Mit einer Variable namens “region” könnten Sie die Variable “hosts” nur Hosts aus der aktuell ausgewählten Region mit einer Abfrage wie dieser anzeigen (wenn “region” eine mehrwertige Variable ist, verwenden Sie den Vergleichsoperator “IN” anstelle von “=”, um mit mehreren Werten übereinzustimmen):

SELECT hostname FROM host  WHERE region IN($region)

Verfügbar ab SmartCollect SC² 6.5 und höher

Die Verwendung von __searchFilter im Abfragefeld filtert das Abfrageergebnis basierend darauf, was der Benutzer in das Dropdown-Auswahlfeld eingibt. Wenn der Benutzer nichts eingegeben hat, ist der Standardwert für __searchFilter %.

Wichtig ist, dass Sie den Ausdruck __searchFilter mit Anführungszeichen umgeben, da SmartCollect SC² dies nicht für Sie tut.

Das folgende Beispiel zeigt, wie Sie __searchFilter als Teil des Abfragefelds verwenden, um die Suche nach Hostname zu ermöglichen, während der Benutzer in das Dropdown-Auswahlfeld eingibt.

Abfrage

SELECT hostname FROM my_host  WHERE hostname LIKE '$__searchFilter'

Von SmartCollect SC² 4.3.0 bis 4.6.0 werden Template-Variablen immer automatisch in Anführungszeichen gesetzt. Wenn Ihre Template-Variablen Strings sind, schließen Sie sie nicht in Anführungszeichen in Where-Klauseln ein.

Ab SmartCollect SC² 4.7.0 werden die Werte von Template-Variablen nur in Anführungszeichen gesetzt, wenn die Template-Variable ein “Multi-Wert” ist.

Wenn die Variable eine mehrwertige Variable ist, dann verwenden Sie den Vergleichsoperator IN anstelle von =, um mit mehreren Werten übereinzustimmen.

Es gibt zwei Syntaxen:

$<varname> Beispiel mit einer Template-Variable namens hostname:

SELECT
  atimestamp as time,
  aint as value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in($hostname)
ORDER BY atimestamp ASC

[[varname]] Beispiel mit einer Template-Variable namens hostname:

SELECT
  atimestamp as time,
  aint as value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in([[hostname]])
ORDER BY atimestamp ASC

SmartCollect SC² erstellt automatisch eine durch Anführungszeichen und Kommata getrennte Zeichenkette für mehrwertige Variablen. Zum Beispiel: Wenn Server01 und Server02 ausgewählt werden, dann wird sie formatiert als: 'server01', 'server02'. Um die Anführungszeichen zu deaktivieren, verwenden Sie die csv-Formatierungsoption für Variablen:

${servers:csv}

Lesen Sie mehr über Variablenformatierungsoptionen in der Doumentation Variables.

Mit Annotations können Sie umfangreiche Ereignisinformationen über Diagramme legen. Sie fügen Annotationsabfragen über das Dashboard-Menü / Ansicht “Annotations” hinzu.

Beispielabfrage mit Zeitspalte und Epochenwerten:

SELECT
  epoch_time as time,
  metric1 as text,
  concat_ws(', ', metric1::text, metric2::text) as tags
FROM
  public.test_data
WHERE
  $__unixEpochFilter(epoch_time)

Beispiel für eine Regionsabfrage unter Verwendung der Spalten time und timeend mit Epochenwerten:

Nur in SmartCollect SC² v6.6+ verfügbar.

SELECT
  epoch_time as time,
  epoch_time_end as timeend,
  metric1 as text,
  concat_ws(', ', metric1::text, metric2::text) as tags
FROM
  public.test_data
WHERE
  $__unixEpochFilter(epoch_time)

Beispielabfrage unter Verwendung der Zeitspalte des nativen SQL-Datentyps Datum/Zeit:

SELECT
  native_date_time as time,
  metric1 as text,
  concat_ws(', ', metric1::text, metric2::text) as tags
FROM
  public.test_data
WHERE
  $__timeFilter(native_date_time)

Zeitreihenabfragen sollten in Alarmbedingungen funktionieren. Tabellenformatierte Abfragen werden noch nicht in Alert-Regel Bedingungen unterstützt.

Es ist jetzt möglich, Datenquellen mit Hilfe von Konfigurationsdateien mit dem Provisioning-System von SmartCollect SC² zu konfigurieren. Sie können mehr darüber lesen, wie es funktioniert und alle Einstellungen, die Sie für Datenquellen festlegen können, auf der provisioning docs page finden

Hier finden Sie einige Beispiele für die Bereitstellung dieser Datenquelle.

apiVersion: 1

datasources:
  - name: Postgres
    type: postgres
    url: localhost:5432
    database: smartcollect
    user: smartcollect
    secureJsonData:
      password: "Password!"
    jsonData:
      sslmode: "disable" # disable/require/verify-ca/verify-full
      maxOpenConns: 0         # SmartCollect SC² v5.4+
      maxIdleConns: 2         # SmartCollect SC² v5.4+
      connMaxLifetime: 14400  # SmartCollect SC² v5.4+
      postgresVersion: 903 # 903=9.3, 904=9.4, 905=9.5, 906=9.6, 1000=10
      timescaledb: false

Wenn Sie metrische Anfragefehler oder andere Probleme feststellen:

• Stellen Sie sicher, dass die Parameter Ihrer Datenquellen-YAML-Datei genau dem Beispiel entsprechen. Dies beinhaltet Parameternamen und die Verwendung von Anführungszeichen.
• Stellen Sie sicher, dass der “Datenbank”-Name nicht in der “URL” enthalten ist.