OpenNIT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

-- Migration 235: Software-Inventar – welche Software auf welchem Gerät läuft
--
-- Der Inventar-Agent und der Intune-Abgleich holen bisher Hardware und
-- Richtlinien, aber keine installierten Programme. Damit fehlt die Grundlage,
-- um Lizenzen gegen die tatsächliche Installation zu halten.
--
-- Zwei Tabellen statt einer: Ein Programm trägt seinen Namen einmal
-- (`software_titles`), die Installationen verweisen darauf. Sonst stünde
-- „Microsoft 365 Apps for enterprise" bei dreihundert Geräten dreihundertmal,
-- und eine Auswertung „auf wie vielen Geräten?" müsste über Zeichenketten
-- gruppieren.

-- ------------------------------------------------------------
-- 1. Programme
-- ------------------------------------------------------------
-- `normalized_key` fasst dasselbe Programm zusammen, das die Quellen
-- unterschiedlich schreiben. Er steht als Spalte und nicht als Ausdruck im
-- Index, damit die Regel an einer Stelle liegt und sich ändern lässt.
CREATE TABLE IF NOT EXISTS `software_titles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `publisher` VARCHAR(255) NOT NULL DEFAULT '',
    `normalized_key` VARCHAR(255) NOT NULL,
    `is_system` TINYINT(1) NOT NULL DEFAULT 0
        COMMENT 'Systembestandteil (Update, Laufzeitumgebung, Sprachpaket)',
    `notes` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_software_titles_key` (`normalized_key`),
    INDEX `idx_software_titles_name` (`name`),
    INDEX `idx_software_titles_system` (`is_system`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Erfasste Programme des Software-Inventars';

-- ------------------------------------------------------------
-- 2. Installationen
-- ------------------------------------------------------------
-- Eine Zeile je Gerät, Programm und Quelle. Die Quelle gehört in den
-- Schlüssel: Agent und Intune melden dasselbe Gerät unabhängig voneinander,
-- und beim Ersetzen darf die eine Quelle die Meldung der anderen nicht
-- mitnehmen.
--
-- Gespeichert wird der gegenwärtige Stand, keine Versionshistorie: Für den
-- Abgleich gegen Lizenzen zählt, was heute installiert ist.
CREATE TABLE IF NOT EXISTS `software_installations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title_id` INT UNSIGNED NOT NULL,
    `asset_id` INT UNSIGNED NOT NULL,
    `version` VARCHAR(100) NOT NULL DEFAULT '',
    `install_date` DATE NULL,
    `source` ENUM('agent','intune') NOT NULL,
    `first_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_software_install` (`asset_id`, `title_id`, `source`),
    INDEX `idx_software_install_title` (`title_id`),
    INDEX `idx_software_install_source` (`source`, `asset_id`),
    CONSTRAINT `fk_software_install_title` FOREIGN KEY (`title_id`)
        REFERENCES `software_titles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_software_install_asset` FOREIGN KEY (`asset_id`)
        REFERENCES `assets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Installierte Programme je Gerät und Quelle';

-- ------------------------------------------------------------
-- 3. Berechtigungen
-- ------------------------------------------------------------
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
    ('view_software',   'Software-Inventar ansehen',                        'software', 'low'),
    ('manage_software', 'Software-Inventar pflegen und Erfassung einstellen','software', 'medium');

-- Administratorrollen bekommen die neuen Rechte, sonst wäre das Modul nach
-- dem Einschalten für niemanden erreichbar.
INSERT IGNORE INTO `role_capabilities` (`role_id`, `capability_id`)
SELECT r.id, c.id
  FROM `roles` r
  JOIN `capabilities` c ON c.name IN ('view_software', 'manage_software')
 WHERE r.is_admin = 1;