Gestion centralisée des informations

L’objectif pour centraliser les données est de convenir avec l’utilisation d’une base de données suffisamment performante pour répondre aux besoins futurs. Selon la topologie du réseau et la structure des appareils, les données peuvent être à la fois mise en cache sur les appareils et par la suite, être traitées localement, puis transférées; ou simplement transférer le lot des données brutes, afin qu'elles soient traitées par la suite.
Les principes sont simples:

1. Connaître les outils existants de l’entreprise
2. Connaître les différents référentiels établis par l’entreprise
3. Connaître les niveaux de services, idéalement des applications affaires et des logiciels
4. Discuter avec les responsables des outils existants
   1. Définir les API permettant la relation entre l’outil centralisé et les autres outils
   2. Définir les champs nécessaires et les relations d’information
5. S’assurer d’avoir une liste complète des rôles et responsabilités avec le processus de changement

Le concept d'une CMDB

• Le choix s'impose davantage sur la conservation locale, si la cache est chiffrée et invisible à l'utilisateur.
  • Les données sensibles sont traitées en priorité
  • Le traitement locale diminue la charge de travail du serveur et devient un bon compromis entre la relation client et serveur
  • Les données brutes demeurent consultables au besoin du serveur, il les rapatrie lorsque nécessaire

Les Pré-requis

• Infrastructure de serveurs assurant la redondance et la haute disponibilité
• Base de données: PostgreSQL --> Libre et Puissant
• Structure Web: NGINX, Apache, NodeJS, IIS
• Connaissance des langages Scripts orientés aux OS: Shell (BSH/KSH), DOS, VBScript, PowerShell, JavaScript

Le prototype

Dans le cadre de projets nouveaux qui requiert une actualisation continue des données, tel que Windows 10, l'absence d'une CMDB, un cycle de mise à jour en continue, impose la mise en place d'un outil centralisé pour recueillir les informations des postes de travail, l'état, les logiciels, les applications Maisons/Missions, les intervenants et responsables. La solution la plus adaptée consiste à centraliser ces informations au sein d'une base de données, afin qu'elle soit traitée par la suite, par des requêtes adaptées aux besoins avec un mécanisme d’actualisation automatisé.

Schéma descriptif de l'outil de gestion de la continuité du Windows As A Service (WaaS)

L'environnement

[Contexte actuel]Les produits Microsoft sont majoritaires au sein du gouvernement québécois, ainsi il est préconisé d'utiliser les outils offerts par le fournisseur, lorsqu'ils sont accessibles. Dans le cadre de l'environnement présent par exemple, les accès sont très limités. Les points bloquant sont:

1. Les utilisateurs ne peuvent installer de logiciels.
2. Les utilisateurs ne peuvent ainsi créer de services ou ouverture de ports
3. Les demandes entre différents services peuvent prendre un temps important voir décourageant
   1. Facteur de démotivation vis-à-vis l'innovation
4. Les produits Microsoft ne sont pas toujours adaptés pour répondre au besoin
   1. Ils peuvent être moins performant dans certaines situations

La BD

Mon choix s'est orienté vers des applications portables et légères: PostgreSQL sur Windows 10
Attention:Choisir la version de la BD en fonction des outils qui la supporte. Exemple prendre une version 9.6 ou +, au lieu de la 10, car phpPgAdmin ne supporte que 9.6

1. Extraire le fichier dans C:\WORK\PostgreSQL
2. Télécharger les drivers ODBC 32bit et 64bit de PostgreSQL adaptés à la version BD téléchargée, accéder au source de PostgreSQL
3. Installer ces Drivers ODBC !Attention, des droits Admins sont nécessaires!
4. Démarrer la BD en exécutant PostgreSQLPortable.exe
5. Dans l'invite de commande, créer une table au nom de invposte
   1. postgres=# CREATE DATABASE invposte;
   2. Si ok = CREATE DATABASE
6. Ensuite créer un utilisateur invposte avec le mot de passe invposte
   1. postgres=# CREATE USER invposte WITH password 'invposte';
   2. Si ok = CREATE ROLE
7. Affecter les pleins droits du compte invposte sur la BD invposte:
   1. postgres=# GRANT ALL privileges ON DATABASE invposte TO invposte;
   2. Si ok = GRANT
8. Valider votre installation en listant les BD existante avec \l
   1. postgres-# \l

Nom	Propriétaire	Encodage	Collationnement	Type caract.	Droits d'accès
invposte	postgres	UTF8	C	C	=Tc/postgres + postgres=CTc/postgres + invposte=CTc/postgres
postgres	postgres	UTF8	C	C
template0	postgres	UTF8	C	C	=c/postgres + postgres=CTc/postgres
template1	postgres	UTF8	C	C	=c/postgres + postgres=CTc/postgres

Tester la connexion ODBC

1. Lancer l'Administrateur de données ODBC (64bit)
   1. {Win + R} et taper: odbcad32.exe
2. Sous l'onglet 'Source de données utilisateur, cliquer Ajouter
   1. Sélectionner PostgreSQL UNICODE (x64)
   2. Entrer les informations suivantes:
      1. Data Source: Test SQL
      2. Database: invposte
      3. Server: localhost
      4. User Name: invposte
      5. Port: 5432
      6. Password: invposte
   3. Cliquer sur Test
      1. Une fenêtre apparaît et indique Connection Successful
   4. Cliquer sur Save

La BD est désormais accessible en localhost, si on veut qu'elle soit ouverte pour l'extérieur, s'assurer d'ouvrir le port 5432 du Firewall Windows

Structure Web

Dans la même lancée que la base de donnée portable, mon choix s'est arrêté sur WNMP - la solution portable de Windows/Nginx/MySQL/PHP serveur. Nginx comme une plateforme Web nouvelle, solide et prometteuse. PHP comme langage simple , commun et connu par le grand publique.La portion MySQL est inutile ici puisqu'on utilise PostgreSQL.

1. Télécharger WNMP
2. Décompresser WNMP dans le répertoire C:\WORK\WNMP
3. Maintenant, les composants principaux {Nginx|PHP|Memcache} doivent être télécharger et les fichiers binaires doivent être déposés dans leur répertoire respectif sous WNMP !Prendre les dernières versions
   1. PHP prendre la version zip et décompresser les binaires dans le répertoire PHP (C:\WORK\WNMP\PHP)
   2. Memcached-32bit et décompresser les binaires dans le répertoire memcached (C:\WORK\WNMP\memcached)
   3. Nginx et décompresser les binaires dans le répertoire Nginx (C:\WORK\WNMP\Nginx)
4. Ajouter l'outil d'administration Web pour PostgreSQL: phpPgAdmin
   1. phpPgAdmin et décompresser les binaires dans le répertoire phpPgAdmin sous nginx\html (C:\WORK\WNMP\nginx\html\phpPgAdmin)
5. Maintenant il faut paramétrer les fichiers configs
   1. Nginx: sous le répertoire C:\WORK\WNMP\nginx\conf, éditer le fichier nginx.conf
      1. Ouvrir les ports 80 et 9000 du FireWall Windows sur le poste
   2. PHP: Éditer le fichier php.ini sous C:\WORK\WNMP\PHP\ et s'assurer d'avoir toutes les extensions nécessaires
   3. phpPgAdmin: Éditer le fichier config.inc.php sous C:\WORK\WNMP\nginx\html\phpPgAdmin\conf
      1. Les valeurs suivantes doivent être présente:
         1. $conf['servers'][0]['desc'] = 'PostgreSQL';
         2. $conf['servers'][0]['host'] = 'localhost';
         3. $conf['servers'][0]['port'] = 5432;
         4. $conf['servers'][0]['pg_dump_path'] = 'C:\\WORK\\PostgreSQLPortable\\App\\PgSQL\\bin\\pg_dump.exe';
         5. $conf['servers'][0]['pg_dumpall_path'] = 'C:\\WORK\\PostgreSQLPortable\\App\\PgSQL\\bin\\pg_dumpall.exe';
         6. $conf['servers'][0]['sslmode'] = 'allow';
   4. Scripts de lancement: Éditer Start_server.bat
      1. Commenter la partie MySQL --> ajouter REM devant les 2 lignes pour obtenir REM :: Start MySQL using the mysql\my.ini config file | REM start mysql\bin\mysqld
      2. Ajouter Start C:\WORK\PostgreSQL\PostgreSQLPortable.exe
      3. Reformuler le Nginx afin qu'il ressemble à ceci:
         1. cd nginx
         2. start nginx
         3. cd ..
      4. Fermer et Sauvegarder
   5. Script d'arrêt: Éditer Stop_server.bat
      1. Commenter la partie MySQL en ajoutant REM devant la ligne
      2. NOTE: Pour l'arrêt de PostgreSQL portable, il faut exécuter manuellement dans la fenêtre de commande Windows. TAPER: \q

La configuration

Notre infrastructure est prête à recevoir du contenu, c.-à-d. que la coquille est vide pour le moment.

Avant tout, créer un schéma permettant d'établir un plan d'ensemble des relations entre les différentes tables et fournir une projection des besoins

Schéma des relations BD entre différentes tables/données de l‘entreprise

Nous alimenterons notre BD grâce aux différents scripts suivants qui vont, à la fois, créer la table si elle n'existe pas et injecter les données nécessaires.

1. UIDexportSQL.ps1 : Le script qui alimente la partie utilisateur en scannant l'Active Directory de l'entreprise
2. SCCMexportSQL.ps1 : Le script qui alimente la partie ordinateur en scannant l'export des données de la plateforme SCCM
3. HPSMexportSQL.ps1 : Le script qui alimente la partie statique de l'inventaire ordinateur en scannant l'export des données de la plateforme HPSM
4. GrpPrdAPPexportSQL.ps1 : Le script qui alimente la partie Applicative (maison) des utilisateurs en scannant les groupes d'appartenance de l'Active Directory de l'entreprise
5. GrpPrdLOGexportSQL.ps1 : Le script qui alimente la partie Logiciel des utilisateurs en scannant les groupes d'appartenance de l'Active Directory de l'entreprise

• Lancer ces scripts l'un après l'autre avec PowerShell. Assurez-vous d'exécuter les scripts avec les droits d'Admin
• Vérifier les tables dans l'invite de commande de PostreSQL Portable:
  • Taper: \c invposte
  • Taper: \dt
    • La liste devrait apparaître comme suit:

         Liste des relations
Schéma |  Nom  | Type  | Propriétaire
--------+-------+-------+--------------
public | ADUID | table | invposte
public | SCCM  | table | invposte

La plateforme phpPgAdmin

La BD est désormais alimentée, maintenant nous allons utiliser phpPgAdmin pour la consulter simplement:

1. Dans un fureteur, taper: http://localhost/phpPgAdmin/
2. Sélectionner PostgreSQL à gauche et taper le login
   1. Utilisateur: invposte
   2. Mot de passe: invposte
3. Sélectionner la BD invposte et agrandir
4. Sélectionner 'Tables' sous 'Schémas->public'
   1. La liste des tables ci-dessous devraient s'afficher:
      1. ADUID , SCCM , HPSM , GrpPrdAPP , GrpPrdLOG

Création des tables de liens

L'objectif est de simplifier la recherche et accélérer les requêtes. Ainsi la création de clefs primaires sera nécessaire pour accélérer la correspondance entre les différentes tables.
Nous allons utiliser le langage natif SQL pour effectuer les requêtes et les commandes. Pour simplifier la vue, nous restons dans l'interface phpPgAdmin. Note: nous aurions pu utiliser l'interface PostgreSQL portable pour lancer les commandes. Elles doivent se terminer avec ";"

• Lancer phpPgAdmin - SQL en sélectionnant SQL à droite de la fenêtre.
  • Une nouvelle fenêtre apparaît
• Créer la table appcomp
  • Copier-coller les informations ci-dessous dans la fenêtre SQL

CREATE TABLE appcomp
(
    "GroupeAPP" text,
    "W7" numeric(5,0),
    b1709 numeric(5,0),
    b1803 numeric(5,0)
)

Cliquer sur Lancer afin d'exécuter la commande

• Créer la table logcomp
  • Copier-coller les informations ci-dessous dans la fenêtre SQL

CREATE TABLE logcomp
(
    "Logiciel" text,
    "W7" numeric(5,0),
    b1709 numeric(5,0),
    b1803 numeric(5,0)
)

Cliquer sur Lancer afin d'exécuter la commande

• Créer la table app_log
  • Copier-coller les informations ci-dessous dans la fenêtre SQL

CREATE TABLE app_log
(
    appli_logi text,
    membres numeric(6,0),
    refid text
)

Cliquer sur Lancer afin d'exécuter la commande

• Créer les index de cette table:

CREATE INDEX fk1_applog ON app_log (appli_logi)

Cliquer sur Lancer afin d'exécuter la commande

• Répéter à nouveau

CREATE INDEX fk2_applog ON app_log (refid)

Cliquer sur Lancer afin d'exécuter la commande

• Créer la table poste_os
  • Copier-coller les informations ci-dessous dans la fenêtre SQL

CREATE TABLE poste_os
(
    poste text,
    os text,
    uid text
)

Cliquer sur Lancer afin d'exécuter la commande

• Créer les index de cette table:

CREATE INDEX fk1_posteos ON poste_os (poste)

Cliquer sur Lancer afin d'exécuter la commande

Création des index ou clefs primaires

Pour chacun des index suivants, exécuter la commande SQL pour chaque ligne, c.-à-d. qu'il n'est pas possible d'exécuter toutes les lignes en même temps

CREATE INDEX fk1_ad ON "ADUID" ("UtilisateurID")

CREATE INDEX fk1_grplog ON "GrpPrdLOG" ("UID")

CREATE INDEX fk1_grpapp ON "GrpPrdAPP" ("UID")

CREATE INDEX fk1_logcomp ON logcomp ("Logiciel")

CREATE INDEX fk1_sccm ON "SCCM" ("PosteID")

CREATE INDEX fk1_hpsm ON "HPSM" ("PosteID","UtilisateurID")

Validation des tables

La liste des tables suivantes doivent être présente:

• ADUID
• SCCM
• HPSM
• GrpPrdAPP
• GrpPrdLOG
• app_log
• appcomp
• logcomp
• poste_os

Mise à jours des données croisées

Maintenant que nos scripts de base sont créés, un mécanisme d'exécution sous forme de tâches planifiées doit être mis en place, afin d'exécuter les scripts sur une base régulière.

Exemple: Il faut convenir à exécuter l'extraction des données SCCM et HPSM à toutes les semaines, voir tous les jours, selon les besoins.

Convenir d'un répertoire de dépôt des extractions fichiers (.csv)

Convenir du système de tâches planifiées (CRON) avec journalisation lorsqu'il y a erreur d'exécution

Automatisation des Mises à jours

Requête de MAJ dans PostgreSQL
Ces requêtes doivent être exécutées lorsque les données sont mise à jour à travers les scripts de PowerShell. Elles sont exécutées dans le langage SQL. Cette partie doit être évoluée vers des scripts exécuté par un ordonnanceur

MAJ Table logcomp

UPDATE logcomp
SET "b1709" =
(SELECT COUNT("Logiciel")
FROM "SCCM"
WHERE "OS"='W10' AND "Poste" LIKE 'B%' AND "SCCM"."Logiciel" = logcomp."Logiciel" 
GROUP BY "Logiciel"
ORDER BY "Logiciel"),
"W7" =
(SELECT COUNT("Logiciel")
FROM "SCCM"
WHERE "OS"='W7' AND "Poste" LIKE 'B%' AND "SCCM"."Logiciel" = logcomp."Logiciel" 
GROUP BY "Logiciel"
ORDER BY "Logiciel");

MAJ Table poste_os

TRUNCATE TABLE poste_os;

INSERT INTO poste_os
SELECT DISTINCT "SCCM"."Poste" AS poste,
 "SCCM"."OS" AS os,
 CONCAT('U',"HPSM"."UtilisateurID") AS uid
FROM "SCCM"
 JOIN "HPSM" ON "SCCM"."PosteID" = "HPSM"."PosteID"
WHERE "SCCM"."Poste" ~~ 'B%' OR "SCCM"."Poste" ~~ 'P%'

MAJ Table app_log

TRUNCATE TABLE app_log;

INSERT INTO app_log
SELECT "GroupeAPP" as appli_logi, count(*) OVER (PARTITION BY "GroupeAPP") AS membres, "UID" AS refid
FROM "GrpPrdAPP"
UNION
SELECT "Logiciel", count(*) OVER (PARTITION BY "Logiciel") AS membres, "Poste" as refid
FROM "SCCM"
WHERE "Logiciel" ~~ '!%'
AND ("Poste" ~~ 'B%' OR "Poste" ~~ 'P%')

Le portail CMDB

Le but est d'offrir un service dynamique, accessible par un bassin d'utilisateurs considérables, plus de 150 utilisateurs.

• Assurer un service rapide et complet, consolidant l'ensemble des données utilisateurs, ordinateurs, appartenances de groupes, logiciels et applications
• Connaître les niveaux de services applicatives et logiciels
  • Les responsables, les pilotes, les développeurs, les techniciens, les analystes, les architectes, les gestionnaires

Création d'un portail de recherche

La structure Web permet de publier simplement et rapidement le résultat des recherches effectuées sur la BD PostgreSQL. Nous utilisons le langage PHP pour simplifier la création, puisque plusieurs modèles ont été développés dans ce langage.

• Le fichier C:\WORK\WNMP\nginx\html\index.php
  • Ce fichier index.php sous C:\WORK\WNMP\nginx\html contient les informations permettant une introduction à la recherche et page d'accueil.
• Le fichier C:\WORK\WNMP\nginx\html\uid.php
  • Ce fichier uid.php sous C:\WORK\WNMP\nginx\html contient les informations permettant d'exécuter les recherches des utilisateurs avec leurs appartenances tant au niveau applicatifs , logiciels, groupes AD et ordinateurs.

L'utilisation

Nous avons désormais les bases pour débuter nos recherches, l'accès principale:

http://localhost/index.php

Voici l'exemple:
Par la suite, si l'on veut définir des requêtes, l'utilisation de phpPgAdmin est grandement utile: http://localhost/phpPgAdmin/