Reference Material | 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:

ColumnDescription
IDThe 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.
NameThe name of the computer or user. This is the NETBios name of the object, the domain name is not included.
DomainThe name of the computer’s or user’s domain. This is the NETBios name of the domain. Such as “Specopssoft” or “OurEnterprise.”
GuidThe unique identifer for the object in Active Directory.
TypeThis 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:

IDNameDomainGuidType
1computer17specopssoft9802ab9a-d331–431b-9621–5a995f05fe520
3computer06specopssoftf85091c9-cac6–4c39–9e17–0ad7e656d2de0
4Bobspecopssoft649a191f-a240–4a33–9b5c-f6e1a69be3d51
6computer21specopssoft2121f3e2–8f3f-4b14–88e6-ffd9d61c95ca0

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:

PrincipalIDManufacturerModel
1DellLatitude xyz
2DellInspiron abc
3HPHP 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 nameData type
PrincipalIDint
TimeOfPurchasedatetime

Note: 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.

PrincipalIdTimeOfPurchase
17/8/2006
32/9/2006
64/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:

ComputerNameTimeOfPurchase
Computer 177/8/2006
Computer 062/9/2006
Computer 214/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:

IdNameJoin TypeDisplay Name
(generated)soInvComputerPurchaseDatePrincipalComputer purchase date

Note: You should not enter a number in the Id field, this value will be generated automatically.

You can now edit the existing reports to add the new information or create new reports combining the new information with any other inventory data.

  • Was this Helpful ?
  • Yes   No