Plugins:DBInstances2.1

From OCS Inventory NG
Jump to: navigation, search

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 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"


Compatibility

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):

'-------------------------------------------------------------------------------
' OCSINVENTORY-NG
' Web : http://www.ocsinventory-ng.org
'
' 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"
'
Const PluginAuthor  = "Sylvie Cozic"
Const PluginDate    = "04/06/2015"
Const PluginVersion = "1.4.0"
'
' Historique :
' 1.0.0 - 21/10/2011 - Sylvie Grimonpont : 
'         Première version
'
' 1.1.0 - 17/10/2012 - Sylvie Grimonpont :
'         Ajout des versions Service pack de SQL Server 2008
'
' 1.2.0 - 27/09/2013 - Sylvie Grimonpont : 
'         Ajout des versions Service pack de SQL Server 2008 R2 + SQL Server 2012
'          + recalcule d'un numéro de version "officiel" SQL Server (celui qu'on retrouve en lancant "Select @@version")
'          + ecriture dans la table softwares (fonctionne à partir de l'agent OCS 2.1.0)
'
' 1.3.0 - 26/11/2013 - Sylvie Grimonpont :
'         Si aucun service SQL n'est detecté mais que des produits SQL sont installés, on remonte l'information
'
' 1.4.0 - 04/06/2015 - Sylvie Cozic (Grimonpont) :
'         Ajout de la version SQL Server 2014
'          + Ré-écrtiture complète de la partie Errorlog (quand les classes WMI ne donnent rien)
'          + Ajout du plugin dbinstances dans les logiciels
'
' 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 http://www.gnu.org/ or Licence.txt
'-------------------------------------------------------------------------------

On Error Resume Next

' DECLARATIONS

'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
Const adVarChar = 200
Const MaxCharacters = 255

' RegExp pour reconstituer le n° de version "officiel" Microsoft SQL Server
' Par exemple Microsoft SQL Server 2008 10.3.5500.0 = Microsoft SQL Server 2008 10.00.5500.0 SP3 (le .3. = SP3...)
Set regexpOfficialVersion = New RegExp
regexpOfficialVersion.IgnoreCase = True
regexpOfficialVersion.Global = True
regexpOfficialVersion.Pattern = "(\d*)\.(\d*)\.(.*)"

' RegExp pour détection produits SQL Server
Set regexpSQLProduct = New RegExp
regexpSQLProduct.IgnoreCase = True
regexpSQLProduct.Global = True
regexpSQLProduct.Pattern = "^Microsoft([^ -~]| )+SQL([^ -~]| )+Server([^ -~]| )+(\d|\.)+( (R|V)\d)*( \(64-bit\))*$"

' Clefs de registre des logiciels installés à parcourir
Set objUninstallPaths = CreateObject("Scripting.Dictionary")
objUninstallPaths.Add "1", "Software\Microsoft\Windows\CurrentVersion\Uninstall"
objUninstallPaths.Add "2", "Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"

' Dictionnaire des logiciels installés (pour gestion des doublons de nom logiciel)
Set dicInstalledSoftwares = CreateObject("Scripting.Dictionary")
dicInstalledSoftwares.RemoveAll
dicInstalledSoftwares.CompareMode = 0


' MAIN

' Remontée des informations du plugin dans la table SOFTWARES
arrScriptName = Split(Wscript.ScriptName,".")
Wscript.Echo "<SOFTWARES>"
Wscript.Echo "<PUBLISHER>Sylvie Cozic</PUBLISHER>"
Wscript.Echo "<NAME>" & arrScriptName(0) & "</NAME>"
Wscript.Echo "<VERSION>" & PluginVersion & "</VERSION>"
Wscript.Echo "<COMMENTS>Data out of OCS Plugin</COMMENTS>"
Wscript.Echo "</SOFTWARES>"

' 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
                    Err.Clear

                    Set colSQLFile = objWMIcimv2.ExecQuery ( strCIMDatafile )
                    If Err = 0 Then
                        For Each objSQLFile in colSQLFile
                            If Not IsNull(objSQLFile.Version) Then
                                arrSQLFileVersion=Split(objSQLFile.Version,".")
                                strSQLFileVersion=Cint(arrSQLFileVersion(1))
                            Else
                                strSQLFileVersion=0
                            End If

                            ' Initialisation
                            strVersion =""
                            strEdition =""
                            strServicePack = ""
                            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
                                Err.Clear
                                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
                                        Else
                                            WriteError()
                                        End If
                                    Next
                                    If Err <> 0 Then WriteError()
                                Else
                                    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

                                ' Init
                                strErrorlogPath = ""
                                Set objFSO = CreateObject("Scripting.FileSystemObject")
                                
                                ' Recherche le répertoire le répertoire des ERRORLOG de la base SQL à partir du chemin de l'exécutable du service
                                If objFSO.FolderExists(strDrive & strPath) Then
                                     strServiceParentPath = objFSO.GetParentFolderName(strDrive & strPath)
                                     If objFSO.FolderExists(strServiceParentPath & "\LOG") Then
                                          strErrorlogPath = strServiceParentPath & "\LOG"
                                     End If
                                End If

                                ' Tente la recherche le répertoire des ERRORLOG dans la base de registre
                                If strErrorlogPath = "" Then
                                     Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strSourceServer & "\root\default:StdRegProv")
                                     If objRegistry.EnumKey (HKEY_LOCAL_MACHINE, strMSSQLServerRegKey, arrSubKeys) = 0 Then
                                         strErrorlogFile = ""
                                         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
                                                      strErrorlogFile = Mid(strValue,3)
                                                      Exit For
                                                  End If
                                              End If
                                         Next

                                         ' Si on a trouvé le chemin du fichier ERRORLOG dans la base de registre, on récupère le répertoire de celui-ci
                                         If strErrorlogFile <> "" Then
                                             ' Teste l'existence du fichier
                                             If objFSO.FileExists(strErrorlogFile) Then
                                                strErrorlogPath = objFSO.GetParentFolderName(strErrorlogFile)
                                             Else
                                                 strError = "Le fichier " & strErrorlogFile & " n'existe pas"
                                             End If
                                         Else
                                             strError = "Information sur fichier ERRORLOG non trouvée en base de registre"
                                         End If
                                     Else
                                         strError = "Information sur fichier ERRORLOG non trouvée en base de registre"
                                     End If
                                End If

                                ' Si on a trouvé le répertoire des ERRORLOG on essaie de lire l'un des fichiers ERRORLOG
                                If strErrorlogPath <> "" Then
                                    Set objErrorlogFolder = objFSO.GetFolder(strErrorlogPath)
                                    Set colErrorlogFiles = objErrorlogFolder.Files
                                    For Each objErrorlogFile in colErrorlogFiles
                                        If objFSO.GetBaseName(objErrorlogFile) = "ERRORLOG" Then
                                            ' Teste la taille du fichier
                                            If objErrorlogFile.Size > 0 Then
                                                strErrorlogFile = objErrorlogFile.Path
                                                ' Lit le fichier
                                                ReadErrorLog(strErrorlogFile)
                                                'Sort de la boucle
                                                Exit For
                                            End If
                                        End If
                                    Next
                                    If strVersion = "" Then
                                        strError = "Le fichier " & strErrorlogPath & "\ERRORLOG n'est pas exploitable"
                                    End If
                                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,2) = "10" Then
                                strSQLName = "Microsoft SQL Server 2008"
                                If Left(strVersion,4) = "10.5" Then
                                    strSQLName = "Microsoft SQL Server 2008 R2"
                                End if
                            ElseIf Left(strVersion,2) = "11" Then
                                strSQLName = "Microsoft SQL Server 2012"
                            ElseIf Left(strVersion,2) = "12" Then
                                strSQLName = "Microsoft SQL Server 2014"
                            Else
                                strSQLName = "Microsoft SQL Server"
                            End if

                            ' Re-"calcule" le numéro officiel de la version SQL et du service pack le cas échéant
                            ' Par exemple Microsoft SQL Server 2008 10.3.5500.0 = Microsoft SQL Server 2008 10.00.5500.0 SP3 (le .3. = SP3...)
                            Set matchesVersion = regexpOfficialVersion.Execute(strVersion)
                            If matchesVersion.Count > 0 Then
                                ' Seulement les versions > 7
                                If CInt(matchesVersion(0).Submatches(0)) > 7 Then
                                    ' Pad à gauche le 2ème nombre de la version : xx.3.xx -> xx.03.xx
                                    strPadSubversion=Right(String(2, "0") & matchesVersion(0).Submatches(1),2)
                                    ' Récupère le numéro de Service Pack si existant xx.03.xx -> SP3, xx.51.xx -> SP1,...
                                    If Right (strPadSubversion,1) <> "0" Then
                                        strServicePack = " (SP" & Right (strPadSubversion,1) & ")"
                                    End IF
                                    ' Reforme le numéro de version "officielle" : xx.03.xx -> xx.00.xx, xx.51.xx -> xx.50.xx
                                    strVersion = matchesVersion(0).Submatches(0) & "." & Left(strPadSubversion,1) & "0." & matchesVersion(0).Submatches(2)
                                End If
                            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. :-)

                            ' On garde <DBINSTANCES> pour le côté ergonomique sur l'interface Web OCS
                            Wscript.Echo "<DBINSTANCES>"
                            Wscript.Echo "<PUBLISHER>Microsoft Corporation</PUBLISHER>"
                            Wscript.Echo "<NAME>" & strSQLName & strServicePack & "</NAME>"
                            Wscript.Echo "<VERSION>" & strVersion & "</VERSION>"
                            Wscript.Echo "<EDITION>" & strEdition & "</EDITION>"
                            Wscript.Echo "<INSTANCE>" & strServiceName & "</INSTANCE>"
                            Wscript.Echo "</DBINSTANCES>"

                            ' Remontée dans la table SOFTWARES (fonctionne à partir de la version 2.1.0 de l'agent Windows)
                            Wscript.Echo "<SOFTWARES>"
                            Wscript.Echo "<PUBLISHER>Microsoft Corporation</PUBLISHER>"
                            Wscript.Echo "<NAME>" & strSQLName & strServicePack & strEdition & "</NAME>"
                            Wscript.Echo "<VERSION>" & strVersion & "</VERSION>"
                            Wscript.Echo "<COMMENTS>Data out of OCS Plugin</COMMENTS>"
                            Wscript.Echo "</SOFTWARES>"

                        Next
                    Else
                        WriteError()
                    End If
                Next
            Else
                WriteError()
            End If
        Else
            ' Aucun service SQL Server trouvé.
            ' Si des produits SQL Server sont installés, on précise dans DBInstance qu'aucun service ne tourne pour ces produits.

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

            'Parcours les logiciels installés
            Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strSourceServer & "\root\default:StdRegProv")
            colKeyPaths = objUninstallPaths.Items
            For Each strKeyPath in colKeyPaths
                objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys
                If IsArray(arrSubKeys) Then
                    For Each Subkey in arrSubKeys
                        strExpKeyPath = strKeyPath & "\" & Subkey
                        objReg.EnumValues HKEY_LOCAL_MACHINE, strExpKeyPath, arrEntryNames ,arrValueTypes
                        strDisplayName = ""
                        If IsArray(arrEntryNames) Then
                            For i = 0 To UBound(arrEntryNames)
                                If InStr(1, arrEntryNames(i), "DisplayName", vbTextCompare) Then
                                    If InStr(1, arrEntryNames(i), "ParentDisplayName", vbTextCompare) Then
                                        '## Ne rien Faire
                                    Else
                                        If arrValueTypes(i) = REG_SZ Then
                                             objReg.GetStringValue HKEY_LOCAL_MACHINE, strExpKeyPath, arrEntryNames(i), strValue
                                             If strValue <> "" AND NOT IsNull(strValue) Then
                                                  strDisplayName = strValue
                                                  strDisplayName = Replace(strDisplayName, "[", "(")
                                                  strDisplayName = Replace(strDisplayName, "]", ")")
                                                  strDisplayName = Replace(strDisplayName, Chr(160), " ")
                                             End If
                                        End If
                                    End If
                                End If
                            Next
                        End If
                        If strDisplayName <> "" Then
                            ' Si ce logiciel n'est pas déjà vu dans la boucle
                            If Not dicInstalledSoftwares.Exists(strDisplayName) Then
                                ' Ajout au dictionnaire des logiciels installés
                                dicInstalledSoftwares.Add strDisplayName, strDisplayName
                                ' Si ce logiciel est un produit SQL Server
                                Set matchesSQLProduct = regexpSQLProduct.Execute(strDisplayName)
                                If matchesSQLProduct.Count > 0 Then
                                    ' Ecrit les données de sortie en XML dans la table Dbinstances
                                    Wscript.Echo "<DBINSTANCES>"
                                    Wscript.Echo "<PUBLISHER>Microsoft Corporation</PUBLISHER>"
                                    Wscript.Echo "<NAME>" & strDisplayName & "</NAME>"
                                    Wscript.Echo "<INSTANCE>Aucun service</INSTANCE>"
                                    Wscript.Echo "</DBINSTANCES>"
                                End If
                            End If
                        End If
                    Next
                End If
            Next
            Set dicInstalledSoftwares = Nothing
        End if
    Else
        WriteError()
    End If
Else
    WriteError()
End If

On Error Goto 0

WScript.Quit

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

    '            ' On écrit l'erreur dans le fichier

    Err.Clear
End Sub


Function TestFileEncoding(strFilePath)
' Retourne
'  0 si le fichier testé est en AscII
' -1 si le fichier testé est en Unicode

    ' Init
    TestFileEncoding = 0
    
    ' Ouvre le fichier et récupère les 3 premiers caractères
    Set testFile = objFSO.OpenTextFile(strFilePath)
    char1 = testFile.read(1)
    char2 = testFile.read(1)
    char3 = testFile.read(1)
    testFile.Close
    
    ' Teste les 3 premiers caractères pour voir si c'est de l'Unicode
    If (Asc(char1) = 255 And Asc(char2) = 254) Then
      TestFileEncoding = -1
    ElseIf (Asc(char1) = 254 And Asc(char2) = 255) Then
        TestFileEncoding = -1
    ElseIf (Asc(char1) = 239 And Asc(char2) = 187 And Asc(char3) = 191) Then
        TestFileEncoding = -1
    Else
      TestFileEncoding = 0
    End If

End Function

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")
     Else
          MultilineTrim = ""
     End If
End Function


Sub ReadErrorLog (strFilePath)
' Lit un fichier ErrorLog et récupère les informations de version et d'édition SQL

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

     ' 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|^.* on |^.*"

    ' Ouvre le fichier en fonction de son encodage
    intFileMode = TestFileEncoding(strFilePath)
    Set objTextFile =objFSO.OpenTextFile(strFilePath, ForReading, False, intFileMode)

    ' Lit le fichier
    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)
            Next
            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
                If Right(strEdition,4) = " on " Then strEdition = Left(strEdition,Len(strEdition)-4)
                strEdition = MultilineTrim(strEdition)
            Next
            If strEdition="" Then strEdition = "Errolog"
            
            ' Si on trouve une information de Service pack on en profite pour la remonter
            If InStr(strErrorlogText,"Service Pack") > 0 Then
                strServicePack = " (SP" & Mid(strErrorlogText, InStr(strErrorlogText,"Service Pack") + 13)
            End If
        End If

    Next

    ' Ferme le fichier
    objTextFile.Close

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:

<DBINSTANCES>
  <PUBLISHER>Microsoft Corporation</PUBLISHER>
  <NAME>Microsoft SQL Server 2005</NAME>
  <VERSION>9.00.1399.06</VERSION>
  <EDITION>Standard Edition</EDITION>
  <INSTANCE>MSSQLSERVER</INSTANCE>
</DBINSTANCES>


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,
   `HARDWARE_ID` INT(11)      NOT NULL,
   `PUBLISHER`   VARCHAR(255)     NULL DEFAULT NULL,
   `NAME`        VARCHAR(255)     NULL DEFAULT NULL,
   `VERSION`     VARCHAR(255)     NULL DEFAULT NULL,
   `EDITION`     VARCHAR(255)     NULL DEFAULT NULL,
   `INSTANCE`    VARCHAR(255)     NULL DEFAULT NULL,
   PRIMARY KEY (`HARDWARE_ID`, `ID`),
   INDEX `NAME` (`NAME`),
   INDEX `VERSION` (`VERSION`),
   INDEX `ID` (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT;


Management Server configuration

Engine configuration for Linux

Warning: Since OCS inventory NG 2.1, you do not have to modify Map.pm 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/Map.pm

###############################################################################
## OCSINVENTORY-NG
## Copyleft Guillaume PROTET 2013
## Web : http://www.ocsinventory-ng.org
##
## 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 http://www.gnu.org/ or Licence.txt
################################################################################

package Apache::Ocsinventory::Plugins::DBInstances::Map;

use strict;

use Apache::Ocsinventory::Map;
# Plugin DBINSTANCES
  $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 => {},
    }
  };
1;


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


Engine configuration for Windows (OCS package)

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


In OCS Inventory NG Server configuration file (c:/xampp/apache/conf/extra/ocsinventory-server.conf), Plugins conf and script path are automatically set. By default you have:

===== PLUGINS =====
  PerlSetEnv OCS_PLUGINS_PERL_DIR "c:/xampp/ocsinventory-server/plugins/perl"
  PerlSetEnv OCS_PLUGINS_CONF_DIR "c:/xampp/ocsinventory-server/plugins/conf"

You have to create two files => c:/xampp/ocsinventory-server/Plugins/conf/DBInstances.conf

PerlModule DBInstances::Map

=> c:/xampp/ocsinventory-server/Plugins/perl/DBInstances/Map.pm

###############################################################################
## OCSINVENTORY-NG
## Copyleft Guillaume PROTET 2013
## Web : http://www.ocsinventory-ng.org
##
## 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 http://www.gnu.org/ or Licence.txt
################################################################################

package DBInstances::Map;

use strict;

use Apache::Ocsinventory::Map;
# Plugin DBINSTANCES
  $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 => {},
    }
  },
1;
Warning: Don't forget to restart apache, otherwise your plugin is not available.


Warning: When you upgrade your OCS Inventory Server, Map.pm 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

/plugins/computer_detail

The new folder will be named

cd_dbinstances 

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:

/plugins/computer_detail/img/

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

/plugins/computer_detail/cd_dbinstances/

folder we created above, and name it

cd_dbinstances.php
<?php
//====================================================================================
// OCS INVENTORY REPORTS
// Copyleft Sylvie Grimonpont 2012
// Web: http://www.ocsinventory-ng.org
//
// 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 http://www.gnu.org/ or Licence.txt
//====================================================================================
 
    print_item_header($l->g(6500));
    if (!isset($protectedPost['SHOW']))
        $protectedPost['SHOW'] = 'NOSHOW';
    $form_name="dbinstances";
    $table_name=$form_name;
    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',);
    if($show_all_column)
        $list_col_cant_del=$list_fields;
    else
        $list_col_cant_del=array($l->g(6503) => $l->g(6503));
 
    $default_fields= $list_fields;
    $queryDetails  = "SELECT * FROM dbinstances WHERE (hardware_id = $systemid)";
    tab_req($table_name,$list_fields,$default_fields,$list_col_cant_del,$queryDetails,$form_name,80,$tab_options);
    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

/plugins/language/french/french.txt

Add the following lines:

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

In file

/plugins/language/english/english.txt

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:

/plugins/computer_detail/cd_config.txt

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.

<ORDER>
.......
.......
23:cd_dbinstances
</ORDER>

<LBL>
.......
.......
cd_dbinstances:g(6500)
</LBL>

<ISAVAIL>
.......
.......
cd_dbinstances:dbinstances
</ISAVAIL>

<URL>
.......
.......
cd_dbinstances:23
</URL>

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