How to use SQL to Query Computer Inventory from the Database

Document ID : KB000074315
Last Modified Date : 20/03/2018
Show Technical Document Details
Introduction:
This document will provide an example of how to query computer inventory directly from the database using Microsoft SQL Management Studio.
 
Here's the view of computer inventory from the DSM Explorer:
User-added image
Question:
How do I query computer inventory directly from the database?
Environment:
Client Automation (ITCM) -- any version.
Answer:
Inventory is organized using a parent-child, or category-subcategory relationship.
 
As inventory categories and subcategories can be dynamic, they are indexed in two tables:
select * from inv_tree_name_id (Category)
select * from inv_item_name_id (Subcategory)
 
Here’s a snippet of what they look like, in the order above:
User-added image
 
General inventory is stored in the inv_generalinventory_item table. 
 
There are two fields in inv_generalinventory_item that correspond with both inv_tree_name_id (category) and inv_item_name_id (subcategory):
 
inv_generalinventory_item.item_parent_name_id corresponds with inv_tree_name_id.tree_name_id
inv_generalinventory_item.item_name_id corresponds with inv_item_name_id.item_name_id
 
Let’s illustrate the relationship with some examples…
 
Example #1: Listing all inventory belonging to an asset.
select
    dh.host_name,
    tree.tree_name,
    item.item_name,
    item_value_text,
    item_value_double,
    item_value_long
from
    ca_discovered_hardware dh
    inner join inv_generalinventory_item gi on dh.dis_hw_uuid=gi.object_uuid
    inner join inv_tree_name_id tree on gi.item_parent_name_id=tree.tree_name_id
    inner join inv_item_name_id item on gi.item_name_id=item.item_name_id
where
       dh.host_name='Starbuck'
order by
       tree.tree_name,
       item.item_name_id,
       gi.item_index

User-added image

 
Example #2: Listing a specific category of inventory belonging to a particular asset.
select
    dh.host_name,
    tree.tree_name,
    item.item_name,
    item_value_text,
    item_value_double,
    item_value_long
from
    ca_discovered_hardware dh
    inner join inv_generalinventory_item gi on dh.dis_hw_uuid=gi.object_uuid
    inner join inv_tree_name_id tree on gi.item_parent_name_id=tree.tree_name_id
    inner join inv_item_name_id item on gi.item_name_id=item.item_name_id
where
       dh.host_name='Starbuck'
       and tree.tree_name='$System$'
order by
       tree.tree_name,
       item.item_name_id,
       gi.item_index

User-added image

 
Example #3: Listing a specific inventory field for all assets.
select
    dh.host_name,
    tree.tree_name,
    item.item_name,
    item_value_text,
    item_value_double,
    item_value_long
from
    ca_discovered_hardware dh
    inner join inv_generalinventory_item gi on dh.dis_hw_uuid=gi.object_uuid
    inner join inv_tree_name_id tree on gi.item_parent_name_id=tree.tree_name_id
    inner join inv_item_name_id item on gi.item_name_id=item.item_name_id
where
       tree.tree_name='$System$'
       and item.item_name='Model'
order by
       dh.host_name

User-added image