State of synchronization between inventory and supervision

Have a state of synchronization between its inventory and its supervision

State of synchronization between inventory and supervision

Have a state of synchronization between its inventory and its supervision

Have a state of synchronization between its inventory and its supervision

Initial Need

I use the following tools every day:

  • Centreon for supervision
  • Glpi for my inventory

Unfortunately I am not the only one to manage and create servers by cons I am the only one to manage the supervision. Naturally as always in this kind of case it often happens that servers are created without my being informed and suddenly a de-correlation between the supervision and the inventory appears.

The Inventory Agent Fusion Inventory is pushed by GPO on all the servers put in the domain so, normally, my inventory should be roughly up to date.

The goal was therefore to make a tool that collects the server information present in my GLPI and to compare it with my objects present in the supervision.

The steps are as follows:

  • Connect to Mysql databases
  • Export information from the 2 databases
  • Compare the information with a readable rendering for the user

Step 1: Connecting to a MySql database

To be able to connect to a MySql database, you must first install the following .net connector: Connector/NET

It is then necessary, initially, to load this connector to be able to use it by adding the following command at the beginning of our script.

[void] [system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

As usual, we set some variables to simplify the use of the script

$ServGLPI = "srv-glpi01" #Adresse du serveur GLPI
$UserGlpi = "root"  #Nom de l'utilisateur utilisé pour se connecter
$PasswordGlpi = "sdfghjkl" #Mot de passe de l'utilisateur
$DbGlpi = "glpidb" #Nom de la base de donnée MySql

$ServCentreon = "srv-centreon01" #Adresse du serveur Centreon
$UserCentreon = "root"  #Nom de l'utilisateur utilisé pour se connecter
$PasswordCentreon = "sdfghjkl" #Mot de passe de l'utilisateur
$DbCentreon = "centreondb" #Nom de la base de donnée MySql

$port = "3306"  #Port d'écoute du serveur MySql

We must then create a connection chain to our Mysql servers

$ConnInventaire = server=$ServGlpi;uid=$UserGlpi;password=$PasswordGlpi;database=$DbGlpi;Port=$Port"
$ObjMysqlInventaire = New-Object MySql.Data.MySqlClient.MySqlConnection($ConnInventaire)
$ObjMysqlInventaire.Open()

$ConnSuperviser = “server=$ServerCentreon;uid=$UserCentreon;password=$PasswordCentreon;database=$DbCentreon;Port=$Port"
$ObjMysqlSuperviser = New-Object MySql.Data.MySqlClient.MySqlConnection($ConnSuperviser)
$ObjMysqlSuperviser.Open()

From this moment we have 2 connections $ConnInventory and $ConnSuperviser open respectively to the databases on the SRV-GLPI01 and SRV-CENTREON01 servers.

We will be able to use these connections to go to the second step.

Step 2: Query the databases

$ReqInventaire = "SELECT `glpi_computers`.name AS `Name`, `glpi_states`.`completename` AS `State`, `glpi_computertypes`.name AS `Type`, `glpi_computermodels`.name AS `Modele`, `glpi_locations`.`completename` AS `Localisation`
FROM `glpi_computers`
LEFT JOIN `glpi_entities` ON (`glpi_computers`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_states` ON (`glpi_computers`.`states_id` = `glpi_states`.`id` )
LEFT JOIN `glpi_manufacturers` ON (`glpi_computers`.`manufacturers_id` = `glpi_manufacturers`.`id` )
LEFT JOIN `glpi_computertypes` ON (`glpi_computers`.`computertypes_id` = `glpi_computertypes`.`id` )
LEFT JOIN `glpi_computermodels` ON (`glpi_computers`.`computermodels_id` = `glpi_computermodels`.`id` )
LEFT JOIN `glpi_operatingsystems` ON (`glpi_computers`.`operatingsystems_id` = `glpi_operatingsystems`.`id` )
LEFT JOIN `glpi_locations` ON (`glpi_computers`.`locations_id` = `glpi_locations`.`id` )
LEFT JOIN `glpi_items_deviceprocessors` ON (`glpi_computers`.`id` = `glpi_items_deviceprocessors`.`items_id` AND `glpi_items_deviceprocessors`.`itemtype` = 'Computer' )
LEFT JOIN `glpi_deviceprocessors` AS `glpi_deviceprocessors_7083fb7d2b7a8b8abd619678acc5b604` ON (`glpi_items_deviceprocessors`.`deviceprocessors_id` = `glpi_deviceprocessors_7083fb7d2b7a8b8abd619678acc5b604`.`id` )
LEFT JOIN `glpi_ipaddresses` AS `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540` ON (`glpi_computers`.`id` = `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`mainitems_id` AND `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`mainitemtype` = 'Computer' AND `glpi_ipaddresses_0cc35feab42e5909929ff742b4834540`.`is_deleted` = 0 )
WHERE `glpi_computers`.`is_deleted` = '0' AND `glpi_computers`.`is_template` = '0' AND ( (`glpi_computertypes`.`id` = '22') AND (`glpi_states`.`id` = '1') ) GROUP BY `glpi_computers`.`id`
ORDER BY `Name` ASC "

First I create a variable $ReqInventory which will contain my request. In this case I get all the server type machines.

Little tip: to find this request I pass GLPI in debug mode ;-)

$SQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand($ReqInventaire,$ObjMysqlInventaire)
$MySQLDataAdaptater = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($SQLCommand)
$MySQLDataSet = New-Object System.Data.DataSet
$RecordCount = $MySQLDataAdaptater.Fill($MySQLDataSet)
$ServersInventorier = $MySQLDataSet.Tables
$ObjMysqlInventaire.close()

Then I pass this request in my connection $ ObjMysqlInventory, I execute it, recover the data in a DataSet and finally from this DataSet I export the information which interests me in the variable $ ServersInventorier

For more information on this part, I let you take a look at Google

I do the same for the request in Centreon

$ReqSuperviser = "SELECT nagios_hosts.display_name AS Name FROM nagios_hosts ORDER BY Name ASC "
$SQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand($ReqSuperviser,$ObjMysqlSuperviser)
$MySQLDataAdaptater = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($SQLCommand)
$MySQLDataSet = New-Object System.Data.DataSet
$RecordCount = $MySQLDataAdaptater.Fill($MySQLDataSet)
$ServersSupeviser = $MySQLDataSet.Tables
$ObjMysqlSuperviser.close()

At this stage, we are therefore left with 2 variables $ServersInventorier and $ServersSupeviser containing respectively the list of servers inventoried in GLPi and the list of servers supervised in Centreon.

Step 3: Compare the 2 lists

To compare these 2 lists I used the Compare-Object command (quite simply ;-))

The Compare-Object cmdlet compares two sets of objects. One set of objects is the “reference set,” and the other set is the “difference set.” The result of the comparison indicates whether a property value appeared only in the object from the reference set (indicated by the <= symbol), only in the object from the difference set (indicated by the => symbol) or, if the IncludeEqual parameter is specified, in both objects (indicated by the == symbol). extract from the online doc

So as the extract above explains, we need:

  • reference set = $ServersInventorier
  • difference set = $ServersSupeviser
  • IncludeEqual: because I still want to know if my machine is inventoried and supervised
$Servers = Compare-Object -ReferenceObject $ServersInventorier.name -DifferenceObject $ServersSupeviser.name -IncludeEqual
ForEach ($server in $Servers)
{
    Switch ($server.SideIndicator)
    {
        "==" {Write-Host $server.SideIndicator $server.InputObject "est Inventorié et Supervisé" }
        "<=" {Write-Host $server.SideIndicator $server.InputObject "est Inventorié mais PAS SUPERVISE"}
        "=>" {Write-Host $server.SideIndicator $server.InputObject "est Supervisé mais PAS INVENTORIE"}
    }
}

To do this I get the result of my compare-object command in a $ Servers variable. Then I loop on this variable to display a clear sentence depending on the case.

The output resulting from this loop is like

== Server01 is Inventoried and Supervised
=> Server10 is Supervised but NOT INVENTORIED
<= Server20 is inventoried but NOT SUPERVISED

Here it is, it only remains for me to update my inventory and my supervision as appropriate.


See also