# State of synchronization between inventory and 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.