Filter for Multiple Group Memberships in SQL

In the upcoming Icinga Web 2 release the filter functionality becomes even more powerful.
Version 2.6.0 introduces the possibility to exclude hosts and services that are member of specific groups.
You now filter for hosts that are not part of the production host group for example.
You want to filter for hosts that are member of the host groups linux and database? That will be possible as well.
I’d like to show you how the latter is done with an example. We have a database with user groups, users and their group
memberships:

CREATE TABLE user_group (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (id),
  UNIQUE KEY group_name (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;
CREATE TABLE user_group_membership (
  user_id int(10) unsigned NOT NULL,
  group_id int(10) unsigned NOT NULL,
  PRIMARY KEY (user_id,group_id),
  CONSTRAINT user_group_membership_user FOREIGN KEY (user_id) REFERENCES `user` (id),
  CONSTRAINT user_group_membership_group FOREIGN KEY (group_id) REFERENCES user_group (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;
INSERT INTO user VALUES (1,'john'),(2,'marc'),(3,'peter');
INSERT INTO user_group VALUES (1,'admins'),(3,'dev'),(2,'support');
INSERT INTO user_group_membership VALUES (1,1),(2,2),(2,3),(3,2);

User john is part of the group admins. Marc is member of the groups dev and support while Peter is part of the dev
group only. We now have the task to filter for users that are at least part of the groups dev and support. In this example
it’s easy of course because we only have three users and know the result without executing any queries.
But anyway how would you achieve this with SQL? Easy, let’s just JOIN the tables and add a WHERE IN condition:

SELECT * FROM `user` u INNER JOIN user_group_membership m ON m.user_id = u.id
INNER JOIN user_group g ON g.id = m.group_id WHERE g.name IN ('dev', 'support');
+----+-------+---------+----------+----+---------+
| id | name  | user_id | group_id | id | name    |
+----+-------+---------+----------+----+---------+
|  2 | marc  |       2 |        3 |  3 | dev     |
|  2 | marc  |       2 |        2 |  2 | support |
|  3 | peter |       3 |        2 |  2 | support |
+----+-------+---------+----------+----+---------+

The result is not surprising. Because of the WHERE IN condition we also get peter who is only part of the dev group.
So, we have to filter for users that produce two or more rows. For this aggregation, HAVING helps:

SELECT * FROM `user` u INNER JOIN user_group_membership m ON m.user_id = u.id
INNER JOIN user_group g ON g.id = m.group_id WHERE g.name IN ('dev', 'support')
HAVING(COUNT(group_id) >= 2);
+----+-------+---------+----------+----+------+
| id | name  | user_id | group_id | id | name |
+----+-------+---------+----------+----+------+
|  2 | marc  |       2 |        3 |  3 | dev  |
+----+-------+---------+----------+----+------+

Looks good! It’s best to move this to a subquery in order to be flexible if the query becomes more complex later on:

SELECT * FROM `user` u WHERE EXISTS (
  SELECT 1 FROM user_group_membership m
  INNER JOIN user_group g ON m.group_id = g.id
  WHERE m.user_id = u.id AND g.name IN ('dev', 'support')
  HAVING COUNT(*) >= 2
);
+----+-------+
| id | name  |
+----+-------+
|  2 | marc |
+----+-------+

Bonus question: how to filter for users that are member of dev and support but no other groups?

Eric Lippmann
Eric Lippmann
Lead Senior Developer

Eric kam während seines ersten Lehrjahres zu NETWAYS und hat seine Ausbildung bereits 2011 sehr erfolgreich abgeschlossen. Seit Beginn arbeitet er in der Softwareentwicklung und dort an den unterschiedlichen NETWAYS Open Source Lösungen, insbesondere inGraph und im Icinga Team an Icinga Web. Darüber hinaus zeichnet er sich für viele Kundenentwicklungen in der Finanz- und Automobilbranche verantwortlich.

Die Macht von SQL-Triggern

Trigger sind SQL-Anweisungen die bei bestimmten Events wie zum Beispiel Inserts, Deletes oder Updates ausgelöst werden. Mit Triggern ist es beispielsweise möglich komplexe Tabellenconstraints in MySQL zu realisieren, die wesentlich mächtiger als die herkömmlichen MySQL-Constraints sind. In diesem Blogpost will ich euch zeigen wie man Trigger verwendet und von welchen Anwendungen man besser die Finger lassen sollte.

Change-Logs mithilfe von Triggern in MySQL

Ein wirklich prominentes Beispiel für den Einsatz von Triggern ist das implementieren eines Change-Logs. Nehmen wir an, wir haben eine Anwendung die Blogposts verwaltet und wollen jetzt dem Nutzer zusätzlich anzeigen wann Änderungen an den Posts durchgeführt wurden.
Ausgangspunkt ist folgende Tabelle die einfach nur die Artikel eines Blogs enthält:

CREATE TABLE article (
   `id` INT AUTO_INCREMENT PRIMARY KEY,
   `title` VARCHAR(255),
   `author` VARCHAR(255),
   `text` VARCHAR(4096)
);

Wir können jetzt einen Trigger erstellen, der bei jedem UPDATE einer Tabellenzeile eine bestimmte Aktion durchführt. Für jede Änderung an einem Blogpost wollen wir den Text vor der Änderung, den Änderungszeitpunkt, die Aktion und den betroffenen Post speichern. Dafür erstellen wir uns eine Tabelle die die Einträge enthalten soll und einen Trigger der die Tabelle befüllt:

CREATE TABLE article_change_log (
   `article_id` INT, `time` DATETIME, `action` VARCHAR(16), `old` VARCHAR(4096)
);
CREATE TRIGGER update_article_logger
   BEFORE UPDATE ON article
   FOR EACH ROW
   INSERT INTO article_change_log SET
      `article_id` = NEW.`id`, `action` = 'update', `time` = NOW(), `old` = OLD.`text`;

CREATE TRIGGER erstellt einen Trigger der bei UPDATE einer Zeile der Tabelle “Article” ausgelöst wird. Der Trigger führt dann für jede betroffene Zeile eine bestimmte Anweisung durch. Die Bezeichner OLD und NEW können innerhalb dieser Anweisung verwendet werden um auf die Daten der Zeile vor und nach dem Update zuzugreifen.
Um einen vollständigen Logger zu implementieren müssten noch ähnliche Trigger für INSERT und DELETE erstellt werden. Damit ihr gleich etwas zum selbst ausprobieren habt und um den Rahmen dieses Blogposts nicht zu sprengen, überlasse ich euch diese Schritte allerdings selbst.

Vorsicht, unsichtbare Trigger!

Ein anderes Wort für “automatisches” Verhalten ist beim Programmieren auch oft “unsichtbares” Verhalten. Da Trigger automatisch Dinge tun ohne dass sie der Benutzer der Datenbank direkt angewiesen hat, sind sie auch immer eine eventuelle Fehlerquelle. Wenn möglich sollten Trigger keine Daten erstellen oder verändern, die von der eigentlichen Anwendungslogik erstellt werden, sondern immer nur eigene Datensätze verwalten. Damit kann sichergestellt werden, dass übersehene Trigger bei späteren Änderungen an der Anwendung zu keinem fehlerhaften Verhalten führen.

Jasper Reporting – Dynamische Parameter

Jasper-ReportingSeinen Report mit Parametern zu versehen hat mehrere Vorteile. Zum einen kann man die gleiche Vorlage für verschiedene Kunden und Objekte einsetzen, zum anderen hat der Endanwender die Möglichkeit der Interaktivität. Wer auf viele Parameter setzt sollte jedoch immer für eine hohe Quote an vordefinierten Reports sorgen, da die Hürde zur Ausführung nicht zu hoch liegen sollte.
Gerade beim Vergleich von Text in einem Statement muss der eingegebene Parameter exakt übereinstimmen, da sonst keine Daten ermittelt werden können. Bei etwas längeren Attributen wie Host- oder Servicegruppen ist dies einfach zu fehleranfällig. Hier können dynamische Parameter Abhilfe schaffen, da sie dem Benutzer quasi die Summe aller Möglichkeiten anbieten und man nur noch aus dieser Menge auswählen kann.
post11_screen1Die Definition erfolgt wieder direkt auf dem JasperServer durch Anlage eines neuen “Input Controls”. Bei Eingabe des Namens bitte dringend auf die richtige Schreibweise achten, da dies im Support Fehler Nummer 1 ist, wenn der Wert nicht angenommen wird und ein Default-Wert existiert.
post11_screen2Bei der Eingabe der “Input Control Details” ist der Type Single Select Query zu wählen. Anschließend kann entweder eine globale Query aus dem Repository oder eine lokale (unser Beispiel) verwendet werden. In unserem Fall ist der Wert zur Anzeige auch der Wert zur Übergabe. Wenn z.B. Namen ausgewählt, aber Personalnummern übergeben werden sollen, sind mind. zwei Columns zu selektieren.
Das Statement zur Ermittlung der verfügbaren Hostgruppen lautet:

select alias from nagios_hostgroups where instance_id = 1

post11_screen5post11_screen3post11_screen4Als Datasource übernehmen wir auch hier wieder die lokale Definition. Anschließend muss wie bereits angedeutet noch das Value und Visible Column hinterlegt werden und die Anlage des Parameters bestätigt werden. Nach Zuordnung des Parameters zum aktiven Report, kann dieser dann auch im Webinterface ausgewählt werden.
Der entsprechende Report findet sich natürlich auch wieder bei netways.org und unserem Demo-System.
Im nächsten Post steigen wir in das Thema Host-Availability ein.

Bernd Erk
Bernd Erk
CEO

Bernd ist Geschäftsführer der NETWAYS Gruppe und verantwortet die Strategie und das Tagesgeschäft. Bei NETWAYS kümmert er sich eigentlich um alles, was andere nicht machen wollen oder können (meistens eher wollen). Darüber hinaus startet er das wöchentliche Lexware-Backup und investiert seine ganze Energie in den Rest der Truppe und versucht für kollektives Glück zu sorgen. In seiner Freizeit macht er mit sinnlosen Ideen seine Frau verrückt und verbündet sich dafür mit seinem Sohn.

Jasper Reporting – Gruppierung von Daten

Jasper-ReportingDa endlose Listen von Informationen mit Host- und Service die evtl. notwendigen Schlussfolgerungen aus einem Report erschweren ist sowohl die optische Aufbereitung als auch Gruppierung der Daten sehr wichtig. Der oberste Grundsatz bei der Erstellung von Reports sollte immer Qualität vor Quantität sein. Wer auf den ersten Seiten keine nützlichen Informationen finden kann wird sich den Rest nicht antun, außer er hat keine andere Wahl. Mir ist zwar bewusst, dass gerade die ersten Beispielreports dieser Serie genau dieses Problem haben, aber wir müssen ja erstmal den Einstieg finden.
Die Gruppierung der Hostelemente aus der Datenbank starten wir mit einer Anpassung des Select-Statements:

select c.alias,
  a.host_object_id,
  a.display_name,
  a.address
from nagios_hosts a,
  nagios_hostgroup_members b,
  nagios_hostgroups c
where a.host_object_id = b.host_object_id
and b.hostgroup_id     = c.hostgroup_id
and a.instance_id      = 1
and b.instance_id      = 1
and c.instance_id      = 1
and display_name like $P{p_hostgroup}

Anschließend müssen wir den vorhandenen Report-Parameter noch umbenennen, damit er zum verwendeten SQL-Parameter passt und das neue Feld c.alias in den Report einfügen.
Im Report Inspector ist dann im Menü “Add Report Group” eine neue Gruppe zum Bericht hinzuzufügen. Der Name kann willkürlich vergeben werden, sollte aber den fachlichen Inhalt der Gruppe beschreiben, da er auch Grundlage für die daraus entstehenden Detailbänder ist. Als Group Expression verwenden wir c.alias, da dies das entsprechende Unterscheidungskriterium für den Gruppenwechsel darstellt.
Nach Anlage der Gruppe, kann dann in den neu entstandenen Detailbereichen noch die Positionierung von Überschriften, Beschreibungen oder Anzeige der neuen Gruppe erfolgen. Der ganze Report sieht dann so aus und kann wie immer auf dem Demo-System live gestestet werden. Auf netways.org gibt es wieder die Vorlage für die eigene Weiterentwicklung.
Natürlich kommt kein guter Bericht ohne Tortengrafik aus. Um das optische Feintuning fortzuführen, fügen wir im nächsten Post dieser Serie eine Torte hinzu. Hmmm lecker.

Bernd Erk
Bernd Erk
CEO

Bernd ist Geschäftsführer der NETWAYS Gruppe und verantwortet die Strategie und das Tagesgeschäft. Bei NETWAYS kümmert er sich eigentlich um alles, was andere nicht machen wollen oder können (meistens eher wollen). Darüber hinaus startet er das wöchentliche Lexware-Backup und investiert seine ganze Energie in den Rest der Truppe und versucht für kollektives Glück zu sorgen. In seiner Freizeit macht er mit sinnlosen Ideen seine Frau verrückt und verbündet sich dafür mit seinem Sohn.

Jasper Reporting – Parameter

Jasper-ReportingDie Erzeugung des Reports erfolgt meist ja für mehrere Rechner, Kunden, Abteilungen oder im Falle von Nagios für diverse Host- und Servicegruppen. Um entsprechende Parameter verarbeiten zu können, müssen diese erst im Report definiert und natürlich auch bei der Ermittlung der Daten im SQL-Statement berücksichtigt werden.
Wir starten mit der Anpassung des Select-Statements um die Daten auf den in diesem Beispiel verwendeten Host einzuschränken.

select
  host_object_id,
  display_name,
  address
from nagios_hosts
where instance_id = 1
and display_name like $P{p_host_name}

Parameter werden immer in geschweifte Klammern mit einem $P gefasst. Eine Übersicht über die entsprechenden Typbezeichner gibt es im entsprechenden Property-Editor.
post6_screen1
Der Paramter muss natürlich im Report “bekannt” gemacht werden, was über das Parameterfeld im Reports-Inspector erfolgt. Der Defaultwert für den Parameter sollte in unserem Beispiel “%” sein, um bei fehlender Parametrisierung alle Hosts zu ermitteln.
post6_screen2
Damit der Benutzer bei der Ausführung des Reports zur Eingabe eines Hostnames aufgefordert wird, muss dieser auch auf dem Server erzeugt werden, was unterhalb der aktuellen Report-Unit im Folder Input controls via Kontextmenü erfolgen kann.
post6_screen4Im Eigenschaftsbereich des Webinterfaces wiederum kann der Anwender noch die Verwendung aktivieren bzw. deaktivieren und das Layout des Parameteraufrufs beeinflussen.
Das Parameterübergabe kann in der Live-Demo als Nagios_Host_3 getestet werden. Die verwendeten Daten basieren auf unserem Nagios-Demo-System, wodurch die vorhandenen Hosts dort eingesehen werden können.

Bernd Erk
Bernd Erk
CEO

Bernd ist Geschäftsführer der NETWAYS Gruppe und verantwortet die Strategie und das Tagesgeschäft. Bei NETWAYS kümmert er sich eigentlich um alles, was andere nicht machen wollen oder können (meistens eher wollen). Darüber hinaus startet er das wöchentliche Lexware-Backup und investiert seine ganze Energie in den Rest der Truppe und versucht für kollektives Glück zu sorgen. In seiner Freizeit macht er mit sinnlosen Ideen seine Frau verrückt und verbündet sich dafür mit seinem Sohn.

Jasper Reporting – iReport

Jasper-ReportingIm letzten Post bin ich ja bereits detailliert auf die Installation des JasperServer eingegangen, welcher für die Aufbereitung und Versendung der Berichte zuständig ist. Da die aktuelle Community Version noch nicht über den Ad Hoc-Report Builder der Professional Version verfügt, was jedoch für Anfang nächstes Jahr geplant sein soll, ist noch ein entsprechendes Werkzeug zur Erzeugung der Reports notwendig. Hier kommt das Werkzeug iReport zum Einsatz.
iReport kann, wie auch die Server-Komponente, bei JasperForge.org heruntergeladen werden und ist, da generisch, für alle gängigen Plattformen verfügbar. Nach Entpacken des Archives muss lediglich die ausführbare Datei gestartet werden und schon kann es losgehen.
Nach Erstellung eines neuen Reports im Hauptmenü, erhält entsteht eine Übersicht des neuen Reports und seiner Attribute. Klar zu erkennen ist im Editor bereits die Unterteilung in verschiedene Bereiche, den so genannten Bändern, welche für die spätere Anzeige und Wiederholung von Informationen bei der Ausführung verantwortlich sind.
Die Anbindung an ein Datenbanksystem setzt die Erzeugung einer entsprechende Datasource voraus, was mit Hilfe des Container-Buttonspost3_screen3 erfolgen kann. Der Zugriff kann auf verschiedene Datenquellen erfolgen, jedoch beschränken wir uns in den nachfolgenden Beispielen auf den Datenbankzugriff via SQL.
Nach erfolgreichem Test der Datenbankanbindung kann mit Hilfe des Statement-Buttons neben dem Preview Bereich das entsprechende Statement mit dem Query-Builder erzeugt oder einfach eingegeben werden.

select
  host_object_id,
  display_name,
  address
from nagios_hosts
where instance_id = 1

Unser Beispielstatement ermittelt lediglich die Hostinformationen aus der NDO-Datenbank.
post3_screen6
Nach Validierung und Speicherung des Statements stehen die selektierten Felder im Bereich des Report Inspectors zur Verfügung und können via Drag&Drop in den entsprechenden Report Bändern platziert werden. Mit Hilfe von Formen und Labels aus der Palette kann der Report noch um statische Elemente ergänzt werden.
post3_screen7
Via Preview kann das Ergebnis sofort geprüft werden und beschleunigt so den Entwicklungsprozess erheblich.
Der Beispielreport findet sich auch in unserem Demo-System und auf netways.org.
Der nächste Post beschreibt den Upload des Berichts auf den Server.

Bernd Erk
Bernd Erk
CEO

Bernd ist Geschäftsführer der NETWAYS Gruppe und verantwortet die Strategie und das Tagesgeschäft. Bei NETWAYS kümmert er sich eigentlich um alles, was andere nicht machen wollen oder können (meistens eher wollen). Darüber hinaus startet er das wöchentliche Lexware-Backup und investiert seine ganze Energie in den Rest der Truppe und versucht für kollektives Glück zu sorgen. In seiner Freizeit macht er mit sinnlosen Ideen seine Frau verrückt und verbündet sich dafür mit seinem Sohn.

NETWAYS Kunden von globaler Virenattacke nicht betroffen

Der kürzlich ausgebrochene Wurm SQLSlammer hat weltweit massive Beeinträchtigungen im Netz und Schäden in Milliardenhöhe verursacht. Nach Schätzungen von Network Associates waren insgesamt ca. 400.000 Server von dem Wurm befallen. Als Folge des Ausbruchs hatten die Börsen in Korea technische Probleme, fielen in den USA zahlreiche Geldautomaten aus und bei einige amerikanische Airlines kam es wegen Datenproblemen zu Flugverzögerungen.
Da die Sicherheitslücke des SQL Servers, die der Wurm ausgenutzt hat, bereits seit einiger Zeit bekannt ist, sind die massiven Schäden vor allem auf die Nachlässigkeit von Firmen bei der Installation von Patches zurückzuführen. NETWAYS Kunden waren von dem Wurm nicht direkt betroffen, da im Bereich ServerManagement/2 alle auftretenden Sicherheitsprobleme umgehend beseitigt werden. Lediglich die Überlastung einiger Provider führe zu einer zeitweisen Nichterreichbarkeit einiger extern gehosteter Server.

Julian Hein
Julian Hein
Executive Chairman

Julian ist Gründer und Eigentümer der NETWAYS Gruppe und kümmert sich um die strategische Ausrichtung des Unternehmens. Neben seinem technischen und betriebswirtschaftlichen Background ist Julian häufig auch kreativer Kopf und Namensgeber, beispielsweise auch für Icinga. Darüber hinaus ist er als CPO (Chief Plugin Officer) auch für die konzernweite Pluginstrategie verantwortlich und stösst regelmässig auf technische Herausforderungen, die sonst noch kein Mensch zuvor gesehen hat.