Verwendung von Microsoft SQL Server in SmartCollect SC²

Nur in SmartCollect SC² v5.1+ verfügbar.

SmartCollect SC² wird mit einem integrierten Microsoft SQL Server (MSSQL)-Datenquellen-Plugin ausgeliefert, mit dem Sie Daten von jedem Microsoft SQL Server 2005 oder neuer, einschließlich Microsoft Azure SQL Database, 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 Link “Configuration” (Konfiguration) sollten Sie einen Link namens “Data Sources” (Datenquellen) finden.
3. Klicken Sie auf die Schaltfläche “+ Datenquelle hinzufügen” in der oberen Kopfzeile.
4. Wählen Sie Microsoft SQL Server aus der Dropdown-Liste Typ.

Eine untere Grenze für die Variablen $__interval und $__interval_ms. Es wird empfohlen, diese auf die Schreibhäufigkeit zu setzen, zum Beispiel 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 dagegen zu schützen, empfehlen wir hoch, einen speziellen MSSQL-Benutzer mit eingeschränkten Rechten anzulegen.

Beispiel:

 CREATE USER smartcollectreader WITH PASSWORD 'password'
 GRANT SELECT ON dbo.YourTable3 TO smartcollectreader

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

Wenn Sie eine ältere Version von Microsoft SQL Server wie 2008 und 2008R2 verwenden, müssen Sie möglicherweise die Verschlüsselung deaktivieren, um eine Verbindung herstellen zu können. Wenn möglich, empfehlen wir Ihnen, für eine optimale Kompatibilität das neueste verfügbare Service Pack zu verwenden.

Sie finden den MSSQL-Abfrage-Editor auf der Registerkarte “Metriken” im Bearbeitungsmodus des Graph-, Singlestat- oder Tabellen-Panels. Sie gelangen in den Bearbeitungsmodus, indem Sie auf den Panel-Titel und dann auf Bearbeiten klicken. Mit dem Editor können Sie eine SQL-Abfrage definieren, um die zu visualisierenden Daten auszuwählen.

1. Wählen Sie Format als Zeitreihe (u. a. für die Verwendung im Grafik- oder Singlestat-Panel) oder Tabelle (u. a. für die Verwendung im Tabellen-Panel).
2. Dies ist der eigentliche Editor, in dem Sie Ihre SQL-Abfragen schreiben.
3. Hilfe-Abschnitt für MSSQL unterhalb des Abfrage-Editors anzeigen.
4. Zeigt die aktuell ausgeführte SQL-Abfrage an. Wird erst nach erfolgreicher Ausführung einer Abfrage verfügbar sein.
5. Fügen Sie eine zusätzliche Abfrage hinzu, wobei ein zusätzlicher Abfrage-Editor angezeigt wird.

Zur Vereinfachung der Syntax und um dynamische Teile, wie Datumsbereichsfilter, zu ermöglichen, kann die Abfrage Makros enthalten.

Wir planen, viele weitere Makros hinzuzufügen. Wenn Sie Vorschläge für Makros haben, die Sie gerne sehen würden, öffnen Sie bitte [ein Problem] (https://github.com/scada-smartcollect/scada-smartcollect) in unserem GitHub-Repository.

Der Abfrage-Editor hat einen Link namens Generated SQL, der angezeigt wird, nachdem eine Abfrage ausgeführt wurde, während man sich im Panel-Bearbeitungsmodus befindet. Klicken Sie darauf und er wird erweitert und zeigt die rohe interpolierte SQL-Zeichenkette, die ausgeführt wurde.

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.

Beispiel-Datenbanktabelle:

CREATE TABLE [event] (
  time_sec bigint,
  description nvarchar(100),
  tags nvarchar(100),
)

CREATE TABLE [mssql_types] (
  c_bit bit, c_tinyint tinyint, c_smallint smallint, c_int int, c_bigint bigint, c_money money, c_smallmoney smallmoney, c_numeric numeric(10,5),
  c_real real, c_decimal decimal(10,2), c_float float,
  c_char char(10), c_varchar varchar(10), c_text text,
  c_nchar nchar(12), c_nvarchar nvarchar(12), c_ntext ntext,
  c_datetime datetime,  c_datetime2 datetime2, c_smalldatetime smalldatetime, c_date date, c_time time, c_datetimeoffset datetimeoffset
)

INSERT INTO [mssql_types]
SELECT
  1, 5, 20020, 980300, 1420070400, '$20000.15', '£2.15', 12345.12,
  1.11, 2.22, 3.33,
  'char10', 'varchar10', 'text',
  N'☺nchar12☺', N'☺nvarchar12☺', N'☺text☺',
  GETDATE(), CAST(GETDATE() AS DATETIME2), CAST(GETDATE() AS SMALLDATETIME), CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), SWITCHOFFSET(CAST(GETDATE() AS DATETIMEOFFSET), '-07:00')

Abfrage-Editor mit Beispielabfrage:

Die Abfrage:

SELECT * FROM [mssql_types]

Sie können den Namen der Spalten des Tabellenbedienfelds steuern, indem Sie die reguläre SQL-Spaltenauswahlsyntax AS verwenden. Beispiel:

SELECT
  c_bit as [column1], c_tinyint as [column2]
FROM
  [mssql_types]

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 haben, die entweder eine SQL datetime oder einen beliebigen numerischen Datentyp zurückgibt, der die Unix-Epoche in Sekunden darstellt. Sie können eine Spalte mit dem Namen metric zurückgeben, die als Metrikname für die Wertespalte verwendet wird. Jede Spalte außer time und metric wird als Wertspalte behandelt. Wenn Sie die Spalte metric weglassen, wird der Name der Wertspalte der Metrikname sein. Sie können mehrere Wertspalten auswählen, jede wird ihren Namen als Metrik haben. 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-Datenbanktabelle:

CREATE TABLE [event] (
  time_sec bigint,
  description nvarchar(100),
  tags nvarchar(100),
)

CREATE TABLE metric_values (
  time datetime,
  measurement nvarchar(100),
  valueOne int,
  valueTwo int,
)

INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 12:30:00', 'Metric A', 62, 6)
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 12:30:00', 'Metric B', 49, 11)
...
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 13:55:00', 'Metric A', 14, 25)
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 13:55:00', 'Metric B', 48, 10)

Beispiel mit einer “Value”- und einer “Metric”-Spalte.

SELECT
  time,
  valueOne,
  measurement as metric
FROM
  metric_values
WHERE
  $__timeFilter(time)
ORDER BY 1

Wenn die obige Abfrage in einem Diagrammpanel verwendet wird, erzeugt sie zwei Reihen mit den Namen “Metrik A” und “Metrik B” mit den Werten “WertEins” und “WertZwei”, die über “Zeit” aufgetragen werden.

Beispiel mit mehreren “value”-Spalten:

SELECT
  time,
  valueOne,
  valueTwo
FROM
  metric_values
WHERE
  $__timeFilter(time)
ORDER BY 1

Wenn die obige Abfrage in einem Diagrammpanel verwendet wird, erzeugt sie zwei Reihen mit den Namen “Metrik A” und “Metrik B” mit den Werten “WertEins” und “WertZwei”, die über “Zeit” aufgetragen werden.

Beispiel für die Verwendung des Makros $__timeGroup:

SELECT
  $__timeGroup(time, '3m') as time,
  measurement as metric,
  avg(valueOne)
FROM
  metric_values
WHERE
  $__timeFilter(time)
GROUP BY
  $__timeGroup(time, '3m'),
  measurement
ORDER BY 1

Wenn die obige Abfrage in einem Diagrammfenster verwendet wird, erzeugt sie zwei Reihen mit den Namen “Metrik A” und “Metrik B” mit den Werten “WertEins” und “WertZwei”, die über “Zeit” aufgetragen werden. Wenn zwei Serien in einem Drei-Minuten-Fenster keinen Wert haben, wird eine Linie zwischen diesen beiden Linien dargestellt. Sie werden feststellen, dass der Graph auf der rechten Seite nie auf Null geht.

Beispiel für die Verwendung des Makros $__timeGroup mit einem auf Null gesetzten Füllparameter:

SELECT
  $__timeGroup(time, '3m', 0) as time,
  measurement as metric,
  sum(valueTwo)
FROM
  metric_values
WHERE
  $__timeFilter(time)
GROUP BY
  $__timeGroup(time, '3m'),
  measurement
ORDER BY 1

Wenn die obige Abfrage in einem Diagrammfenster verwendet wird, ist das Ergebnis zwei Serien mit den Namen “Metrik A” und “Metrik B” mit einer Summe von “WertZwei”, die über “Zeit” aufgetragen werden. Jede Reihe, die in einem 3-Minuten-Fenster keinen Wert hat, hat einen Wert von Null, den Sie im Diagramm rechts sehen können.

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 Daten, die in Ihrem Dashboard angezeigt werden, leicht ändern.

In der Dokumentation Templating finden Sie eine Einführung in die Templating-Funktion und die verschiedenen Typen von Template-Variablen.

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

Sie können z. B. eine Variable haben, die alle Werte für die Spalte Hostname in einer Tabelle enthält, wenn Sie eine solche Abfrage in der Einstellung für die 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]

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 in der Auswahlliste haben einen Text und einen Wert, so dass Sie einen freundlichen Namen als Text und eine ID als Wert haben können. Eine Beispielabfrage mit “hostname” als Text und “id” als Wert:

SELECT hostname __text, id __value FROM host

Sie können auch verschachtelte Variablen erstellen. Wenn Sie zum Beispiel eine weitere Variable mit dem Namen “Region” haben. Dann könnten Sie die Hosts-Variable aus der aktuell ausgewählten Region mit einer Abfrage wie dieser anzeigen lassen (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)

Von SmartCollect SC² 4.3.0 bis 4.6.0 werden Template-Variablen immer automatisch in Anführungszeichen gesetzt. Wenn es sich also um einen String-Wert handelt, sollten Sie ihn in Where-Klauseln nicht in Anführungszeichen einschließen.

Ab SmartCollect SC² 5.0.0 werden 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 namenshostname:

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

[[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

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'. Deaktivieren Sie die Anführungszeichen, verwenden Sie die csv-Formatierungsoption für Variablen:

${servers:csv}

Lesen Sie mehr über die Formatierungsoptionen für Variablen in der Variablen Dokumentation.

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

Columns:

Example database tables:

CREATE TABLE [events] (
  time_sec bigint,
  description nvarchar(100),
  tags nvarchar(100),
)

Wir verwenden auch die Datenbanktabelle, die in Time series queries.

Beispielabfrage unter Verwendung der Zeitspalte mit Epochenwerten:

SELECT
  time_sec as time,
  description as [text],
  tags
FROM
  [events]
WHERE
  $__unixEpochFilter(time_sec)
ORDER BY 1

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

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

SELECT
  time_sec as time,
  time_end_sec as timeend,
  description as [text],
  tags
FROM
  [events]
WHERE
  $__unixEpochFilter(time_sec)
ORDER BY 1

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

SELECT
  time,
  measurement as text,
  convert(varchar, valueOne) + ',' + convert(varchar, valueTwo) as tags
FROM
  metric_values
WHERE
  $__timeFilter(time_column)
ORDER BY 1

Gespeicherte Prozeduren wurden auf ihre Funktionsfähigkeit überprüft. Bitte beachten Sie jedoch, dass wir nichts Spezielles getan haben, um dies zu unterstützen, so dass es Randfälle geben kann, in denen es nicht so funktioniert, wie Sie es erwarten würden. Gespeicherte Prozeduren sollten in Tabellen-, Zeitreihen- und Anmerkungsabfragen unterstützt werden, solange Sie die gleiche Benennung der Spalten verwenden und die Daten im gleichen Format zurückgeben, wie oben im entsprechenden Abschnitt beschrieben.

Bitte beachten Sie, dass jede Makrofunktion nicht innerhalb einer gespeicherten Prozedur funktioniert.

Erste Abfrage:

SELECT
  $__timeGroup(time, '5m') as time,
  measurement + ' - value one' as metric,
  avg(valueOne) as valueOne
FROM
  metric_values
WHERE
  $__timeFilter(time)
GROUP BY
  $__timeGroup(time, '5m'),
  measurement
ORDER BY 1

Zweite Abfrage:

SELECT
  $__timeGroup(time, '5m') as time,
  measurement + ' - value two' as metric,
  avg(valueTwo) as valueTwo
FROM
  metric_values
GROUP BY
  $__timeGroup(time, '5m'),
  measurement
ORDER BY 1

Wir können eine gespeicherte Prozedur definieren, die alle Daten zurückgibt, die wir benötigen, um 4 Serien in einem Diagrammfeld wie oben darzustellen. In diesem Fall akzeptiert die gespeicherte Prozedur zwei Parameter @from und @to vom Datentyp int, die ein Zeitbereich (from-to) im Epochenformat sein sollten die verwendet werden, um die Daten zu filtern, die von der gespeicherten Prozedur zurückgegeben werden sollen.

Wir ahmen die $__timeGroup(time, '5m') in den select- und group by-Ausdrücken nach, und deshalb sind eine Menge langer Ausdrücke erforderlich - diese könnten in MSSQL-Funktionen extrahiert werden, falls gewünscht.

CREATE PROCEDURE sp_test_epoch(
  @from int,
  @to 	int
)	AS
BEGIN
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int) as time,
    measurement + ' - value one' as metric,
    avg(valueOne) as value
  FROM
    metric_values
  WHERE
    time >= DATEADD(s, @from, '1970-01-01') AND time <= DATEADD(s, @to, '1970-01-01')
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int),
    measurement
  UNION ALL
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int) as time,
    measurement + ' - value two' as metric,
    avg(valueTwo) as value
  FROM
    metric_values
  WHERE
    time >= DATEADD(s, @from, '1970-01-01') AND time <= DATEADD(s, @to, '1970-01-01')
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int),
    measurement
  ORDER BY 1
END

Dann können wir die folgende Abfrage für unser Diagramm-Panel verwenden.

DECLARE
  @from int = $__unixEpochFrom(),
  @to int = $__unixEpochTo()

EXEC dbo.sp_test_epoch @from, @to

Wir können eine gespeicherte Prozedur definieren, die alle Daten zurückgibt, die wir benötigen, um 4 Serien in einem Diagrammfeld wie oben darzustellen. In diesem Fall akzeptiert die gespeicherte Prozedur zwei Parameter @from und @to vom Datentyp datetime, die einen Zeitbereich (from-to) darstellen sollten der verwendet wird, um die Daten zu filtern, die von der gespeicherten Prozedur zurückgegeben werden sollen.

Wir ahmen die $__timeGroup(time, '5m') in den select- und group by-Ausdrücken nach und deshalb sind eine Menge langer Ausdrücke erforderlich - diese könnten in MSSQL-Funktionen extrahiert werden, falls gewünscht.

CREATE PROCEDURE sp_test_datetime(
  @from datetime,
  @to 	datetime
)	AS
BEGIN
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int) as time,
    measurement + ' - value one' as metric,
    avg(valueOne) as value
  FROM
    metric_values
  WHERE
    time >= @from AND time <= @to
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int),
    measurement
  UNION ALL
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int) as time,
    measurement + ' - value two' as metric,
    avg(valueTwo) as value
  FROM
    metric_values
  WHERE
    time >= @from AND time <= @to
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int),
    measurement
  ORDER BY 1
END

Dann können wir die folgende Abfrage für unser Diagramm-Panel verwenden.

DECLARE
  @from datetime = $__timeFrom(),
  @to datetime = $__timeTo()

EXEC dbo.sp_test_datetime @from, @to

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 Seite provisioning docs page

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

apiVersion: 1

datasources:
  - name: MSSQL
    type: mssql
    url: localhost:1433
    database: smartcollect
    user: smartcollect
    jsonData:
      maxOpenConns: 0         # SmartCollect SC² v5.4+
      maxIdleConns: 2         # SmartCollect SC² v5.4+
      connMaxLifetime: 14400  # SmartCollect SC² v5.4+
    secureJsonData:
      password: "Password!"