Integrating External Data

The Specops Inventory database and reporting solution is designed with external connections in mind. External data can be imported to the Specops Inventory data and made available to the reporting engine. This information can then be combined with all the other inventory information when creating new or editing the default reports.

The only requirement for the external data is that it can be connected to computer- or user names. Alternatively the Active Directory computer- or user GUIDs can be used to make the connection. Importing external data also requires some SQL server and SQL knowledge and possibly scripting experience.

Note : Many properties from the Active Directory computer and user objects are by default inventoried by Specops Inventory. If you can get your data into one of those fields you are done.

Database design

The most central table in the Specops Inventory database is called soPrinicpal . This table contains all the computers and users that have been inventoried. The important columns for this document are:

Column Description
ID The internal Specops Inventory key for the computer or user. This is a generated value and all connections to computers or users in the database use this key.
Name The name of the computer or user. This is the NETBios name of the object, the domain name is not included.
Domain The name of the computer’s or user’s domain. This is the NETBios name of the domain. Such as “Specopssoft” or “OurEnterprise.”
Guid The unique identifer for the object in Active Directory.
Type This is a flag indicating what type of object the row holds. 0 means its a computer, 1 means its a user.

The following table depicts some sample data from the soPrincipal table:

ID Name Domain Guid Type
1 computer17 specopssoft 9802ab9a-d331–431b-9621–5a995f05fe52 0
3 computer06 specopssoft f85091c9-cac6–4c39–9e17–0ad7e656d2de 0
4 Bob specopssoft 649a191f-a240–4a33–9b5c-f6e1a69be3d5 1
6 computer21 specopssoft 2121f3e2–8f3f-4b14–88e6-ffd9d61c95ca 0

All information used for reporting in the database is connected to the soPrincipal table by the Id column. For example the computer hardware table looks something like this:

PrincipalID Manufacturer Model
1 Dell Latitude xyz
2 Dell Inspiron abc
3 HP HP whatever ABC

For each row in this table the PrincipalId field connects the row to the corresponding computer in the soPrincipal table.

Getting the external data into the database

In order to get the external data into the Specops Inventory database a new table must be created in the database and then populated with the external data. For example we could create the following table:

Column name Data type
PrincipalID int
TimeOfPurchase datetime
There must be a column called PrincipalId in the table. This is the column that will be used to match the rows with the corresponding computer when generating the reports.

It is also recommended to create a foreign key constraint between the PrincipalId column in the new table and the Id column in soPrincipal .

When the table has been created we could then manually fill it with purchase dates, and the corresponding computer in the soPrincipal table. Lets assume we create a table called soInvComputerPurchaseDate .

PrincipalId TimeOfPurchase
1 7/8/2006
3 2/9/2006
6 4/12/2005

We could now add this information to our existing reports. However in the real world this manual lookup is usually not feasible nor recommended. How do we automate this? Well that all depends on what kind of data you are importing and in what format it is available. The following must be done:

Get the data into a table in a SQL server database, this can be in a separate database, doesn’t have to be the Specops Inventory database. This table will serve as a staging table from where the data is moved into the actual Specops Inventory table (the one we created above.) To get the data into the staging table you can set up a data source and use the SQL server data import engine (the datasource could be an Oracle database an Excel document or any other type of ODBC source). You can then set this up as a scheduled job to be performed regularly, if the source data is likely to change. Lets assume the staging table is called ComputerPurchaseDateStaging and looks like this:

ComputerName TimeOfPurchase
Computer 17 7/8/2006
Computer 06 2/9/2006
Computer 21 4/12/2005

In this table we have the computer names which can now be matched to the computer names in the soPrincipal table. This is the crucial part, we must have computer names that can be matched with the computers in the soPrincipal table. Now, to get the data from this staging table we would have to execute the following SQL statement:



INSERT INTO soInvComputerPurchaseDate
SELECT p.Id, s.TimeOfPurchase
FROM soPrincipal p, ComputerPurchaseDateStaging s
WHERE s.ComputerName = p.Name AND p.Type = 0

This will insert data into the soInvComputerPurchase table just like we did in the manual example above. The execution of this SQL statement should also be scheduled to run after the import job runs.

Adding the new table as a report source

Now when we have the data in the Specops Inventory database there is one final step before the data becomes available as a reporting source. There is a table in the database that contains the names of all the tables (or views) that are allowed to use in reports. The table is called soRepReportView . Insert a row like the following into this table:

Id Name Join Type Display Name
(generated) soInvComputerPurchaseDate Principal Computer purchase date
You should not enter a number in the Id field, this value will be generated automatically.