How to use SQL to Query Computer Inventory from the Database

book

Article ID: 74315

calendar_today

Updated On:

Products

CA Automation Suite for Data Centers - Configuration Automation CA Client Automation - Asset Management CA Client Automation - IT Client Manager CA Client Automation CA Client Automation - Remote Control CA Client Automation - Asset Intelligence CA Client Automation - Desktop Migration Manager CA Client Automation - Patch Manager

Issue/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:

<Please see attached file for image>

User-added image


How do I query computer inventory directly from the database?

Environment

Client Automation (ITCM) -- any version.

Resolution

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:

<Please see attached file for image>

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

<Please see attached file for image>

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

<Please see attached file for image>

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

<Please see attached file for image>

User-added image


 

Attachments

1558694214004000074315_sktwi1f5rjvs16miy.png get_app
1558694212132000074315_sktwi1f5rjvs16mix.png get_app
1558694210235000074315_sktwi1f5rjvs16miw.png get_app
1558694208253000074315_sktwi1f5rjvs16miv.png get_app
1558694206198000074315_sktwi1f5rjvs16miu.png get_app