SQL Query to display a specific Inventory for all computers

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

How to display a Specific Hardware Inventory with a SQL Query for all Computers ?

Environment

CA Client Automation - Any versions

Resolution

If Inventory is a string like System/Model following query could be used :

DECLARE @INVNAME AS NVARCHAR(384)
DECLARE @TREENAME AS NVARCHAR(384)

SET @INVNAME='Model'
SET @TREENAME='$System$'

SELECT h.host_name, i.item_value_text
FROM ca_discovered_hardware h
LEFT JOIN inv_generalinventory_item i ON h.dis_hw_uuid=i.object_uuid
LEFT JOIN inv_tree_name_id t ON i.item_parent_name_id=t.tree_name_id
LEFT JOIN inv_item_name_id n ON i.item_name_id=n.item_name_id
LEFT JOIN ca_settings s ON s.set_id=1 AND s.set_val_uuid=t.domain_uuid and s.set_val_uuid=n.domain_uuid
WHERE t.tree_name=@TREENAME AND n.item_name=@INVNAME
ORDER BY host_name

If Inventory is an integer like Operating System/Windows Update Count following query could be used :

DECLARE @INVNAME AS NVARCHAR(384)
DECLARE @TREENAME AS NVARCHAR(384)

SET @INVNAME='Windows Update Count'
SET @TREENAME='$Operating System$'

SELECT h.host_name, i.item_value_long
FROM ca_discovered_hardware h
LEFT JOIN inv_generalinventory_item i ON h.dis_hw_uuid=i.object_uuid
LEFT JOIN inv_tree_name_id t ON i.item_parent_name_id=t.tree_name_id
LEFT JOIN inv_item_name_id n ON i.item_name_id=n.item_name_id
LEFT JOIN ca_settings s ON s.set_id=1 AND s.set_val_uuid=t.domain_uuid and s.set_val_uuid=n.domain_uuid
WHERE t.tree_name=@TREENAME AND n.item_name=@INVNAME
ORDER BY host_name

If Inventory is a very long integer like Storage/Total Disk Space following query could be used :

DECLARE @INVNAME AS NVARCHAR(384)
DECLARE @TREENAME AS NVARCHAR(384)

SET @INVNAME='Total Disk Space'
SET @TREENAME='$Storage$'

SELECT h.host_name, i.item_value_double
FROM ca_discovered_hardware h
LEFT JOIN inv_generalinventory_item i ON h.dis_hw_uuid=i.object_uuid
LEFT JOIN inv_tree_name_id t ON i.item_parent_name_id=t.tree_name_id
LEFT JOIN inv_item_name_id n ON i.item_name_id=n.item_name_id
LEFT JOIN ca_settings s ON s.set_id=1 AND s.set_val_uuid=t.domain_uuid and s.set_val_uuid=n.domain_uuid
WHERE t.tree_name=@TREENAME AND n.item_name=@INVNAME
ORDER BY host_name