Difference between revisions of "Plugins:DBnames2.1"

From OCS Inventory NG
Jump to: navigation, search
(Created page with "= Retrieve running database instances= {{Waring |message = This plugin use DBInstances plugin for PHP file. }} == Introduction== The goal of this plugin is to enable OCS...")
 
(Retrieve running database instances)
Line 2: Line 2:
  
  
{{Waring
+
{{Note
 
|message = This plugin use DBInstances plugin for PHP file.  
 
|message = This plugin use DBInstances plugin for PHP file.  
 
}}
 
}}

Revision as of 08:31, 28 August 2015

Retrieve running database instances

Note: This plugin use DBInstances plugin for PHP file.


Introduction

The goal of this plugin is to enable OCS to inventory the databases names on a Windows device.

The current version is limited to Microsoft SQL Server databases

The plugin retrieves the following information:

  • Databases names
  • File path of MDF


Compatibility

This plugin was written for and tested on OCS NG 2.1.1

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

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

strComputer = "localhost" 

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open _
    "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
        "Trusted_Connection=Yes;Initial Catalog=Master"

Set objRecordset = objConnection.Execute("Select Name, filename From SysDatabases")
If objRecordset.Recordcount = 0 Then
    Wscript.Echo ""
Else
	
    Do Until objRecordset.EOF
		Wscript.Echo "<DBNAMES>"
        Wscript.Echo "<NAME>" & objRecordset.Fields("Name") & "</NAME>"
		Wscript.Echo "<FILE>" & objRecordset.Fields("filename") &  "</FILE>"
        objRecordset.MoveNext
		rem objFilename.MoveNext
		Wscript.Echo "</DBNAMES>"
    Loop
	

End If


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:

<DBNAMES>
  <NAME>master</NAME>
  <FILE>C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf</NAME>
</DBNAMES>


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 `dbnames` (
   `ID`          INT(11)      NOT NULL AUTO_INCREMENT,
   `HARDWARE_ID` INT(11)      NOT NULL,
   `NAME`        VARCHAR(255)     NULL DEFAULT NULL,
   `FILE`    VARCHAR(255)     NULL DEFAULT NULL,
   PRIMARY KEY (`HARDWARE_ID`, `ID`),
   INDEX `NAME` (`NAME`),
   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/DBNames.conf

PerlModule Apache::Ocsinventory::Plugins::DBNames::Map

Création du fichier /etc/ocsinventory-server/perl/Apache/Ocsinventory/Plugins/DBNames/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::DBNames::Map;

use strict;

use Apache::Ocsinventory::Map;
# Plugin DBNAMES
  $DATA_MAP{dbnames} = {
   mask => 0,
   multi => 1,
   auto => 1,
   delOnReplace => 1,
   sortBy => 'NAME',
   writeDiff => 1,
   cache => 0,
   mandatory => 1,
   fields => {
       NAME => {},
       FILE => {},
    }
  };
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/DBNames.conf

PerlModule DBNames::Map

=> c:/xampp/ocsinventory-server/Plugins/perl/DBNames/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 DBNames::Map;

use strict;

use Apache::Ocsinventory::Map;
# Plugin DBNAMES
  $DATA_MAP{dbnames} = {
   mask => 0,
   multi => 1,
   auto => 1,
   delOnReplace => 1,
   sortBy => 'NAME',
   writeDiff => 1,
   cache => 0,
   mandatory => 1,
   fields => {
       NAME => {},
       FILE => {},
    }
  };
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>";

//Tableau des bases de données de l'instance par défaut
        $form_name="dbnames";
    $table_name=$form_name;
    echo open_form($form_name);
    $list_fields=array($l->g(49) => 'NAME',
                       $l->g(6040) => 'FILE',);
    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 dbnames WHERE (hardware_id = $systemid)";
    tab_req($table_name,$list_fields,$default_fields,$list_col_cant_del,$queryDetails,$form_name,80,$tab_options);
    echo close_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:

6040 Chemin du fichier

In file

/plugins/language/english/english.txt

Add the following lines:

6040 Path file

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