Database table computer - The computer table contains basic client computer data. The primary key for this table, and for most other associated tables where a join would be done, is computer_id. This table contains most basic computer information such as computer and domain name, operating system, serial number, UUID, asset tag. Most likely all reports created from the Deployment Solution database will use this table in their queries.
Example: Create a report of all computers names, serial numbers, and operating systems, grouped together by operating system.
SELECT [name], [serial_num], [os] FROM [computer] ORDER BY [os] ASC
Database table hardware - The hardware table contains basic hardware information, such as processor type, speed, number of processor cores. It also contains total RAM and free RAM, or how much was not in use when the client last reported inventory, and screen resolution. This table also uses the computer_id primary key that the computer table uses.
Example: Create a report of all computer model types that have Windows XP installed and have more than 1024 MB of RAM.
SELECT c.[model_num] FROM [computer] c
INNER JOIN [hardware] h ON c.[computer_id] = h.[computer_id]
WHERE c.[os] LIKE '%Windows XP%' AND h.[ram_total] >= 1024
Database table device - The device table contains all hardware devices that are attached to the client system. This includes all devices that would show up when you view Windows Device Manager on a system. You can access Windows Device Manager on a client system by right clicking on the "My Computer" icon, and select "Manage" and then select "Device Manager".
The data columns contained in this table are device name, class type, description, manufacturer, and driver. Class and driver do not use friendly names, but are instead GUIDs. These GUIDs do not link to any other table or other data in the Deployment Server database. These GUIDs are only useful if you have an understanding of them outside of the database. An example of this is if you are looking for all devices with a certain driver, you would need to find out in Windows what that driver's GUID is, and then use that directly in the query.
Example: Generate a list of all computers names that do not have a CD-ROM drive
SELECT [name] FROM [computer] WHERE [computer_id] NOT IN
(SELECT [computer_id] FROM [device] WHERE [name] LIKE '%CD-ROM%')
Database table nics - The nics table contains all network interface cards on a client computer. While some of this data is contained in the device table, there is more detailed information in the nics table. The data that is available in this table are items such as MAC address, if you are using DHCP or static IP, if the NIC is enabled or disabled, and the vendor and device IDs.
The vendor and device ID in Microsoft Windows will display as a hexadecimal number. These numbers are stored in the Deployment Server database as a numerical value, and as such when it is queried it will show up as a decimal number. It might be necessary to convert either into our out of hexadecimal when using this data from the nics table.
Example: Create a list of all Intel network cards device names, and device ID
SELECT [nic_desc], [nic_device_id] FROM [nics] WHERE [nic_vendor_id = 32902]
Note: The device ID for Intel devices is 0x8086. This hexadecimal number converts to 32902
Database table application - The application table contains all applications that are installed on a system that report in Windows "Add/Remove Programs" in the Control Panel. If an application installation adds a key to the following registry key it will be reported in this table:
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
Most of the data that is contained in this registry key on each client is reported up in the application inventory and stored in this table. That includes application name, description, publisher, version, product ID. If the application developer reports this data to the registry on the client system then it will be reported in this database table. Many applications do not have complete data because they do not always populate that registry key completely. The column name will always be populated, but will sometimes contain a GUID, which isn't very reader friendly. In those cases they will usually have a description that gives the friendly name, but this is not always the case as description is an optional field for application developers.
This table will almost always contain multiple rows for each computer record since client computer will usually have multiple applications installed. The primary look up key in this table is a combination of computer_id and app_id. Most joins to this table will be done once again with the computer_id column however.
Example: Create a report of all computer names and the currently logged on user of the systems that are running Windows 7 and are using the software "Secure Endpoint" version 5.0
SELECT c.[computer_name], c.[logged_on_user] FROM [computer] c
INNER JOIN [application] a ON c.[computer_id] = a.[computer_id]
WHERE c.[os] LIKE '%Windows 7%' AND a.[description] LIKE 'Secure Endpoint'
AND a.[version] = '5.0'
Return to: Windows 7 Migration: Introduction