Trace: » Introduction

Introduction

As a lot of IT guys are sometimes a bit limited by policies when it comes to installing “open source” agents on companies equipment, like the OCS one, but usually have access to the AD and SMS environment, we came up with the idea to code a small php script to import data from SMS server into the OCS database.

Before I begin with the installation steps, I would like to thank my collegue Eathen for all the hard work as he was the one, who finally put my ideas and first approaches into code, as I my coding knowledge is just not sufficient to accomplish this kind of task, so once again thanks Eathen.

Important Note: I had to modify the original script we are using as it was fully tailored to our existing AD and SMS collection structure and the chances that you are using the same one is probably around zero. What this more generic script is doing now, is looking into the AD sites and services structure and tries to gather information from there, so please don't be frustrated when the script is not running out of the box. There are also small parts in the script that are filtering out all the SMS virtual keyboard and mouse entries and also most of the security and bug fixes as we do not wanted to have them written to the OCS database. If you would like to have them imported as well, you need to modify the script and uncomment those filters.

Environment

The systems that the script has been tested and is running on is a RedHat Enterprise Linux Server 5 with GLPI 0.71.3 and OCS 1.01 installed. The SMS is a Windows 2003 Server with SMS 2003 based on MSSQL 2000.

Requirements

  • an Active Directory environment
  • a running SMS 2003 installation integrated into AD
  • a running OCS NG installation
  • a running GLPI installation
  • a MSSQL database account to access the SMS database
  • a OCS NG database account to access the OCS database
  • access to the SMS .mof file as it may have to be modified a bit
  • freetds 0.64
  • php-mssql

Notice: Users who would like to start the script from Windows based systems, do not have to install the freetds and php-mssql as the PHP version already has the mssql integration included.

Installation

Linux:

First of all install the freetds and php-mssql packages that can usually be found on the popular repositories. It might be that you need to install some additonal packages to satisfy rpm dependencies.

Windows:

Windows users should be able to go straight ahead without installing any additonal software as long as PHP is installed on the system. FIXME

Testing: The following is a small php script that could be executed through a webbrowser to ensure that the connection to the MSSQL database is working properly.

<?php

  $hostname = "";         // MSSQL Server IP
  $port = "";		// port the sql server is listening
  $dbname = "";           // db name
  $username = "";       // username like 'sa'
  $pw = "";             // password for the user
  $db_conn = mssql_connect("$hostname:$port","$username","$pw")
  or die( "<strong>ERROR: Connection to $hostname failed</strong>" );
  mssql_select_db( "$dbname", $db_conn )
  or die( "<strong>ERROR: Selecting database failed</strong>" );
  $query = "SELECT ColName1,ColName2 FROM Tablename";
  $result = mssql_query($query);
  while ( $record = mssql_fetch_array($result) )
  {
  echo $record["ColName1"] . ", " . $record["ColName2"] . "<br />";}

?>

Notice: For making this script work, please insert the data for the first five variables and change “Tablename” into a table you would like to query and also “ColName1” “ColName2” into existing column names of the table you are connecting to.

If the script is displaying you the data from those two chosen columns your connection to the MSSQL database is working and the first step is completed.

If you are running into problems, like “could not connect to server”, do not continue with the installation of the import script as it won't work.

SMSToOCS script: The script contains out of three files which just need to be copied to the server which is initiating the connection to the SMS MSSQL server. You also need to adjust the database connection parameters within “OCSSQL.php” and “SMSSQL.php” files to match your environment.

As soon as you have made those adjustments, you can try to start the script by just typing “php SMSToOCSImport.php”

Important Note: I would definately start the testing of this script in a test environment to avoid any kind of problems that may occur especially in very large SMS installations. Also it would be a good idea not to start the script on a life server during daytime, as I am not absolutely sure about the full impact. The script I am using is running via cron job every night at 2 am. So please watch out what you are doing. If you are unsure, please ask people with the certain knowledge when it would be the a good time for testing, so be warned. Simple way would be to make maybe a smaller extract from the SMS database and copy that to a test machine.

Last Comments

Problems

There is the possiblity that the import script is not capturing all the data that you expect to get and here are the reasons why this can happen.

SMSToOCS script not modified As mentioned at the beginning the script filters out certian security and bug fixes from the software section to minimize junk. If you would like to have that included into OCS, you would need to uncomment those sections.

SMS .mof file not configured accordingly It might be that you need to enable some collection settings in the SMS .mof file. There is a tool called MOF Manager (mofman.exe) which allows you to easily modify your .mof file for the SMS Server. It might be that you need to set some sections like “serial number” to “true”, so that the SMS Agent collects that kind of data. But again talk to your Admins before you are just modifying that file, as it could have a big impact on database growth and performance.

AD setup differences The script published here has been modified for public use, nevertheless it could happen that it is just not matching with the setup of your AD structure.

Other occurances

Custom fields created via OCS If you have any custom fields created in OCS you need to be aware that the script does not take care about them, means when machines are imported this is usually done by deleting and reinserting those entries, so your data in the custom fields will be gone. This happens just to the machines which are imported from SMS and has no impact on the data collected with OCS Agent or any other collection tool you may use.

Duplicates One of the issues I usually step into when using the script is that I get quite a lot of duplcates after I have imported into GLPI. The main reason we found out is that it is quite difficult to track which systems have been reinstalled, as they get sometimes the same name like before or another one, depending in our automated installer system. To avoid to many duplicates the script also looks for the date and removes machines not being active within 60 days. I am still ending up with quite some duplicates, but you can use the “Clean links between OCS and GLPI” function included in GLPI to get that under control. You may also use the functionality that comes with OCS to get that sorted. FIXME

Future Outlook

We are thinking about modifying the script, so that it becomes fully AD independent and just retrieving the data from SMS server out of predefined “collections”.

Download