How to use SQL to Query Computer Inventory from the Database

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>

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>

 

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>

 

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>

 

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>