From OCS Inventory NG
Revision as of 19:12, 7 February 2014 by Bezourox (Talk | contribs) (Created page with "= Retrieve running database instances= == Introduction== The goal of this plugin is to enable OCS to inventory the running database instances on a Windows device. The current ...")

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Retrieve running database instances


The goal of this plugin is to enable OCS to inventory the running database instances on a Windows device.

The current version is limited to Microsoft SQL Server databases

The plugin retrieves the following information:

  • SQL Server "long" product name. For example: "Microsoft SQL Server 2008 R2"
  • Edition. For example: "Enterprise Edition (64-bit)"
  • Version number. For example: "10.50.1600.1"
  • Instance name. For example: "MSSQLSERVER"


This plugin was written for and tested on OCS NG 2.0

The agent script has been tested, and was found to be working on:

  • Windows 2000
  • Windows XP
  • Windows Vista
  • Windows 7
  • Windows 8 (Pro and Entreprise)
  • Windows 2003
  • Windows 2008
  • Windows 2008 R2

Windows Agent configuration

We will now create and test the script, that will be run automatically by the OCS Agent every time it launches an inventory.

Creating the script

This script is a VB script. It should be placed in %ProgramFiles%\OCS Inventory Agent\Plugins folder on the devices you want to inventory (or %ProgramFiles(x86)% on 64 bits devices). The file name of the script can be anything you like, but you must use .vbs extension.

Note: Any file found in this folder will be processed by the agent.

The script is as follows (comments are in french, sorry):

' Web :
' Liste les bases de données SQL Server du poste
'  4 données sont remontées :
'  - strSQLName :     Nom long du produit SQL Server
'                     Par exple : "Microsoft SQL Server 2008 R2"
'  - strServiceName : Nom de l'instance
'                     Par exple : "MSSQLSERVER"
'  - strEdition :     Edition.
'                     Par exple : "Enterprise Edition (64-bit)"
'  - strVersion :     Version "chiffrée".
'                     Par exple : "8.00.194"
' Auteur  : Sylvie Grimonpont
' Date    : 21-10-2011
' Version : 1.0.0
' This code is open source and may be copied and modified as long as the source
' code is always made freely available.
' Please refer to the General Public Licence or Licence.txt

On Error Resume Next

'Déclaration des constantes
Const DblQuote  = """"
Const ForReading = 1
Const HKEY_LOCAL_MACHINE   = &H80000002
Const strMSSQLServerRegKey = "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters"
Const REG_SZ = 1

' Spécificités SQL 2000
   ' RegExp pour récupération de l'édition dans un fichier ERRORLOG
   Set regexpEdition = New RegExp
   regexpEdition.IgnoreCase = True
   regexpEdition.Global = True
   regexpEdition.Pattern = "^.*dition"

   ' RegExp pour récupération de la version dans un fichier ERRORLOG
   Set regexpVersion = New RegExp
   regexpVersion.IgnoreCase = True
   regexpVersion.Global = True
   regexpVersion.Pattern = "-[^-(]+(\(|$)"

' Initialisation des variables
strSourceServer = "."

' Recherche d'un service ayant sqlservr.exe dans son path. Si ce service n'existe pas, aucune base sql ne tourne.
Set objWMIcimv2  = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strSourceServer & "\root\cimv2")
If Err = 0 Then

   Set colServices = objWMIcimv2.ExecQuery("Select Name , PathName from Win32_Service Where PathName Like '%sqlservr.exe%'")
   If Err = 0 Then
      If colServices.count > 0 Then
         If Err = 0 Then
            'Wscript.Echo "SQL Server trouvé !"
            For Each objService in colServices
               ' ServiceName
               strServiceName = objService.Name
               If InStr(strServiceName,"$") > 0 Then
                  strServiceName =  Mid(strServiceName, InStr(strServiceName,"$") + 1)
               End If

               ' PathName, Drive et Path
               strPathName = objService.PathName
               If Left(strPathName, 1) = DblQuote Then strPathName= Mid(strPathName, 2)
               strDrive = Mid(strPathName, 1, 2)
               strPath  = Mid(strPathName, 3, InStr(1, strPathName, "sqlservr.exe") - 3)

               ' Recherche la version du fichier sqlservr.exe
               strCIMDatafile = "Select FileName,Extension,Version from CIM_Datafile" _
                              & " Where Drive = '" & strDrive & "'" _
                              & " and Path = '" & Replace( strPath ,"\","\\") & "'" _
                              & " and FileName = 'sqlservr' and Extension = 'exe'"

               ' Si on a eu une erreur entre temps, on efface

               Set colSQLFile = objWMIcimv2.ExecQuery ( strCIMDatafile )
               If Err = 0 Then
                  For Each objSQLFile in colSQLFile
                     If Not IsNull(objSQLFile.Version) Then
                     End If

                     ' Initialisation
                     strVersion =""
                     strEdition =""
                     strWMIsql = ""
                     strError = ""

                     ' Positionne la classe WMI SqlServer en fonction de la version du fichier sqlservr.exe
                     If strSQLFileVersion = 90 Then strWMIsql = "ComputerManagement"
                     If strSQLFileVersion > 90 Then strWMIsql = "ComputerManagement10"

                     ' Recherche la version et l'édition de la base SQL via la classe WMI SqlServer si disponible
                     If (strWMIsql <> "") Then
                        ' Si on a eu une erreur entre temps, on efface
                        Set objWMIsql = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strSourceServer & "\root\Microsoft\SqlServer\" & strWMIsql)
                        If Err = 0 Then
                           For Each prop in objWMIsql.ExecQuery("select * from SqlServiceAdvancedProperty Where SQLServiceType = 1 And ServiceName='" & objService.Name & "'")
                              If Err = 0 Then
                                 If (prop.PropertyName="VERSION") Then strVersion = prop.PropertyStrValue
                                 If (prop.PropertyName="SKUNAME") Then strEdition = prop.PropertyStrValue
                              End If
                           If Err <> 0 Then WriteError()
                        End If
                     End If

                     ' Si on n'a pas pu determiner la version et l'édition à partir de la classe WMI (cas SQL Server 2000), on essaie de la déterminer à partir du fichier ERRORLOG (si en local)
                     If (((strWMIsql= "") Or (strVersion = "") Or (strEdition = "")) And (strSourceServer = ".")) Then
                        ' On ne trappe plus les erreurs...
                        On Error Goto 0
                        ' Recherche du chemin du fichier ERRORLOG dans la base de registre
                        Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strSourceServer & "\root\default:StdRegProv")
                        If objRegistry.EnumKey (HKEY_LOCAL_MACHINE, strMSSQLServerRegKey, arrSubKeys) = 0 Then
                           strErrorlogPath = ""
                           objRegistry.EnumValues HKEY_LOCAL_MACHINE, strMSSQLServerRegKey, arrValueNames, arrValueTypes
                           For I=0 To UBound(arrValueNames)
                               If arrValueTypes(I) = REG_SZ Then
                                  objRegistry.GetStringValue HKEY_LOCAL_MACHINE,strMSSQLServerRegKey,arrValueNames(I),strValue
                                  ' Dans HKLMSOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters, le paramètre qui commence par "-e" définie le path de ERRORLOG
                                  If Left(strValue,2) = "-e" Then
                                     strErrorlogPath = Mid(strValue,3)
                                  End If
                               End If

                           ' Si on a trouvé le chemin du fichier ERRORLOG dans la base de registre, on essaie de lire le fichier en question
                           If strErrorlogPath <> "" Then
                              ' Teste l'existence du fichier
                              Set objFSO = CreateObject("Scripting.FileSystemObject")
                              If objFSO.FileExists(strErrorlogPath) Then
                                 ' Teste la taille du fichier
                                 Set objErrorlogFile = objFSO.GetFile(strErrorlogPath)
                                 If objErrorlogFile.Size > 0 Then
                                    ' Lit le fichier
                                    ' On essaie avec le fichier ERRORLOG.1
                                    Set objErrorlogFile = objFSO.GetFile(strErrorlogPath & ".1")
                                    If objErrorlogFile.Size > 0 Then
                                       ReadErrorLog(strErrorlogPath & ".1")
                                       strError = "Le fichier " & strErrorlogPath & " est vide"
                                    End If
                                 End If
                                 strError = "Le fichier " & strErrorlogPath & " n'existe pas"
                              End If
                              strError = "Information sur fichier ERRORLOG non trouvée en base de registre"
                           End If
                           strError = "Information sur fichier ERRORLOG non trouvée en base de registre"
                        End If
                     End If
                     ' Récupération du nom SQL Server
                     If Left(strVersion,3) = "6.5" Then
                        strSQLName = "Microsoft SQL Server 6.5"
                     ElseIf Left(strVersion,1) = "7" Then
                        strSQLName = "Microsoft SQL Server 7.0"
                     ElseIf Left(strVersion,1) = "8" Then
                        strSQLName = "Microsoft SQL Server 2000"
                     ElseIf Left(strVersion,1) = "9" Then
                        strSQLName = "Microsoft SQL Server 2005"
                     ElseIf Left(strVersion,4) = "10.0" Then
                        strSQLName = "Microsoft SQL Server 2008"
                     ElseIf Left(strVersion,4) = "10.5" Then
                        strSQLName = "Microsoft SQL Server 2008 R2"
                        strSQLName = "Microsoft SQL Server"
                     End if

                     ' Ecrit les données de sortie en XML

                     ' Les données disponibles sont :
                     ' - strSQLName :     Nom long du produit SQL Server
                     ' - strServiceName : Nom de l'instance
                     ' - strEdition :     Edition. Par exple : Enterprise Edition (64-bit) / Express Edition
                     ' - strVersion :     Version "chiffrée". Par exemple : 8.00.194 / 10.50.1600.1

                     ' Le format remonté est spécifique à un process interne. A vous d'adapter en fonction de vos besoins. :-)

                     Wscript.Echo "<DBINSTANCES>"
                     Wscript.Echo "<PUBLISHER>Microsoft Corporation</PUBLISHER>"
                     Wscript.Echo "<NAME>" & strSQLName & "</NAME>"
                     Wscript.Echo "<VERSION>" & strVersion & "</VERSION>"
                     Wscript.Echo "<EDITION>" & strEdition & "</EDITION>"
                     Wscript.Echo "<INSTANCE>" & strServiceName & "</INSTANCE>"
                     Wscript.Echo "</DBINSTANCES>"

               End If
         End If
         On Error Goto 0
         'Wscript.Echo "Aucun SQL Server trouvé !"
      End if
   End If
End If

On Error Goto 0


Sub WriteError()
   strError = "Erreur " & Err.Number & " - " & Err.Description

   '         ' On écrit l'erreur dans le fichier

End Sub

Function MultilineTrim (Byval TextData)
    Dim textRegExp
    Set textRegExp = new regexp
    textRegExp.Pattern = "\s{0,}(\S{1}[\s,\S]*\S{1})\s{0,}"
    textRegExp.Global = False
    textRegExp.IgnoreCase = True
    textRegExp.Multiline = True

    If textRegExp.Test (TextData) Then
        MultilineTrim = textRegExp.Replace (TextData, "$1")
        MultilineTrim = ""
    End If
End Function

Sub ReadErrorLog (strFilePath)
   ' Lit le fichier
   Set objTextFile =objFSO.OpenTextFile(strFilePath, ForReading, False)
   For i = 1 To 4
      strErrorlogText = objTextFile.Readline

      ' La version se trouve sur la première ligne
      If i = 1 Then
         Set versions = regexpVersion.Execute(strErrorlogText)
         For Each version In versions
            strVersion = version
            If Left(strVersion,1)  = "-" Then strVersion = Mid(strVersion,2)
            If Right(strVersion,1) = "(" Then strVersion = Left(strVersion,Len(strVersion)-1)
            strVersion = MultilineTrim(strVersion)
         If strVersion="" Then strVersion = "Errolog"
      End If

      ' L'édition se trouve sur la quatrième ligne
      If i = 4 Then
         strEdition = strErrorlogText
         Set editions = regexpEdition.Execute(strErrorlogText)
         For Each edition In editions
            strEdition = edition
            strEdition = MultilineTrim(strEdition)
         If strEdition="" Then strEdition = "Errolog"
      End If

End Sub

Checking the result

The output of the script is inserted in the XML file generated by the agent, and is sent to the server as part of the inventory data. If you want to generate an inventory without sending it to the server, and also have a look at the resulting XML file, launch your agent OCSInventory.exe (on a device running an SQL Server database!) with this parameter: /XML="C:\Like\you\want". The XML file will be written to the C:\Like\you\want folder. It should contain a section similar to this:

  <PUBLISHER>Microsoft Corporation</PUBLISHER>
  <NAME>Microsoft SQL Server 2005</NAME>
  <EDITION>Standard Edition</EDITION>

Database Server configuration

Creating a new table in the database

We now have to create a database table where our new inventory information will be stored. You can create the table by running the following script in MySQL or in MySQL Workbench:

CREATE TABLE `dbinstances` (
   `ID`          INT(11)      NOT NULL AUTO_INCREMENT,
   `NAME`        VARCHAR(255)     NULL DEFAULT NULL,
   INDEX `ID` (`ID`)

Management Server configuration

Engine configuration

Warning: Since OCS inventory NG 2.1, you do not have to modify directly, but you have to configure engine to include perl scripts

In OCS Inventory NG Server configuration file (z-ocsinventory-server.conf), define Plugins conf and script path :

===== PLUGINS =====
  PerlSetEnv OCS_PLUGINS_PERL_DIR "/etc/ocsinventory-server/perl"
  PerlSetEnv OCS_PLUGINS_CONF_DIR "/etc/ocsinventory-server/plugins"

Création du fichier /etc/ocsinventory-server/plugins/DBInstances.conf

PerlModule Apache::Ocsinventory::Plugins::DBInstances::Map

Création du fichier /etc/ocsinventory-server/perl/Apache/Ocsinventory/Plugins/DBInstances/

  ## Copyleft Guillaume PROTET 2013
  ## Web :
  ## This code is open source and may be copied and modified as long as the source
  ## code is always made freely available.
  ## Please refer to the General Public Licence or Licence.txt

  package Apache::Ocsinventory::Plugins::OfficeKey::Map;

  use strict;

  use Apache::Ocsinventory::Map;
  $DATA_MAP{dbinstances} = {
   mask => 0,
   multi => 1,
   auto => 1,
   delOnReplace => 1,
   sortBy => 'NAME',
   writeDiff => 1,
   cache => 0,
   mandatory => 1,
   fields => {
       PUBLISHER => {},
       NAME => {},
       VERSION => {},
       EDITION => {},
       INSTANCE => {},

Warning: When you upgrade your OCS Inventory Server, will be overwritten. Don't forget to back this file up before upgrading your server.

Communication Server configuration

In this step we set up the OCS Inventory web interface so we can view our inventory results.

Creating the workspace

We will now create the necessary folders and files for our plugin on the server.

First, we create a new folder inside the directory


The new folder will be named


This folder will contain our new cd_dbinstances.php, which we will create in a later step.

Then, we can set up the icons that will represent our new inventory results on the web interface. To do this, we copy our 3 icons into the following folder:


The name of the new icons will be: cd_dbinstances.png for the inactive icon, cd_dbinstances_a.png for the active icon, and cd_dbinstances_d.png for the greyed-out icon (shown when there is no inventoried data for this plugin).

You may use the 3 icons provided. Just right click and save the following images:

Cd dbinstances.pngCd dbinstances a.pngCd dbinstances d.png

Creating the PHP file

Now we create a php file that will show the details of our inventory. Save the following script into the


folder we created above, and name it

// Copyleft Sylvie Grimonpont 2012
// Web:
// This code is open source and may be copied and modified as long as the source
// code is always made freely available.
// Please refer to the General Public Licence or Licence.txt
    if (!isset($protectedPost['SHOW']))
        $protectedPost['SHOW'] = 'NOSHOW';
    echo "<form name='".$form_name."' id='".$form_name."' method='POST' action=''>";
    $list_fields=array($l->g(6501) => 'PUBLISHER',
                       $l->g(49) => 'NAME',
                       $l->g(6502) => 'EDITION',
                       $l->g(277) => 'VERSION',
                       $l->g(6503) => 'INSTANCE',);
        $list_col_cant_del=array($l->g(6503) => $l->g(6503));
    $default_fields= $list_fields;
    $queryDetails  = "SELECT * FROM dbinstances WHERE (hardware_id = $systemid)";
    echo "</form>";

Warning: If your server is running under linux/unix, you need to change the permissions on cd_dbinstances.php.

You can do this by running the following in the console:

chmod 0755 ./cd_dbinstances.php
chown root:www-data ./cd_dbinstances.php

Modifying language file

We have to modify language file so that data could be shown in the different languages.

In file


Add the following lines:

6500 Instance(s) de bases de données
6501 Editeur
6502 Edition
6503 Instance

In file


Add the following lines:

6500 Databases instance(s)
6501 Publisher
6502 Edition
6503 Instance

Activating the plugin

In order for the plugin to show up in the web interface, it needs to be "activated", which is done by editing the following file:


We need to add four lines into their relevant sections. The following snippet should give you an idea where to add the lines. Please note, that in the case there is already a plugin with the number 23, you will have to give a different number to our plugin.





Display the result in administration console

Connect to the GUI, select a device, and click on our new icon. You should see the list of running Microsoft SQL Server databases.

Cd dbinstances screenshot en.png