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