Client Automation - How to get AM Inventory with SQL Query ?

Document ID : KB000123492
Last Modified Date : 21/12/2018
Show Technical Document Details
Introduction:

Asset Management General Inventory is stored in table inv_generalinventory.

What is the SQL Query to return General Inventory for one computer ?
 
Instructions:

Query 1 : Result in list


Following query could be used to retrieve "Network Adapter" General Inventory for computer JY-R14SP2.
Change the values in Green bold lines to adapt with Inventory tree and computer name to retrieve.

 
USE mdb

DECLARE @HostName NVARCHAR(255)
DECLARE @TreeName NVARCHAR(255)
DECLARE @ItemName NVARCHAR(255)

SET @HostName='JY-R14SP2'
SET @TreeName='%Network Adapter%'
SET @ItemName='%'



SELECT h.host_name, t.tree_name, i.item_parent_id, n.item_name, 
CASE 
   WHEN item_format <300 THEN convert(nvarchar(384),item_value_long,0)
   WHEN item_format >=300 AND item_format<500 THEN  convert(nvarchar(384),item_value_double,0)
   WHEN item_format >=500 THEN item_value_text
END 'value'

FROM dbo.inv_generalinventory_item i 
   INNER JOIN dbo.ca_settings s ON (s.set_id=1 AND s.set_val_uuid=i.domain_uuid)
   INNER JOIN dbo.ca_discovered_hardware h ON (i.object_uuid = h.dis_hw_uuid)
   INNER JOIN dbo.inv_tree_name_id t ON (i.item_parent_name_id = t.tree_name_id AND t.domain_uuid=s.set_val_uuid)
INNER JOIN dbo.inv_item_name_id n ON (i.item_name_id = n.item_name_id AND n.domain_uuid=s.set_val_uuid)

WHERE h.host_name like @HostName AND t.tree_name like @TreeName AND n.item_name like @ItemName
ORDER BY 1,2,3,4


 
Example :
 
User-added image


 

Query 2 : Result in array

If inventory to retrieve is under an array format, following SQL Query could be used :
Change the values in Green bold lines to adapt with Inventory tree and computer name to retrieve.

 
USE mdb

DECLARE @HostName NVARCHAR(255)
DECLARE @TreeName NVARCHAR(255)
DECLARE @ItemName NVARCHAR(255)
DECLARE @columns VARCHAR(max)
DECLARE @convert VARCHAR(max)


SET @HostName='JY-R14SP2'
SET @TreeName='%Network Adapter%'
SET @ItemName='%'


IF OBJECT_ID ('tempdb..#TABLE', 'U') IS NOT NULL DROP TABLE #TABLE

SELECT h.host_name,  item_parent_id, n.item_name, 
CASE 
   WHEN item_format <300 THEN convert(nvarchar(384),item_value_long,0)
   WHEN item_format >=300 AND item_format<500 THEN  convert(nvarchar(384),item_value_double,0)
   WHEN item_format >=500 THEN item_value_text
END 'value'
INTO #TABLE

FROM dbo.inv_generalinventory_item i 
   INNER JOIN dbo.ca_settings s ON (s.set_id=1 AND s.set_val_uuid=i.domain_uuid)
   INNER JOIN dbo.ca_discovered_hardware h ON (i.object_uuid = h.dis_hw_uuid)
   INNER JOIN dbo.inv_tree_name_id t ON (i.item_parent_name_id = t.tree_name_id AND t.domain_uuid=s.set_val_uuid)
   INNER JOIN dbo.inv_item_name_id n ON (i.item_name_id = n.item_name_id AND n.domain_uuid=s.set_val_uuid)

WHERE h.host_name like @HostName AND t.tree_name like @TreeName AND n.item_name like @ItemName
ORDER BY 1,2,3


SELECT @columns = stuff ((SELECT DISTINCT '],[' + item_name FROM #TABLE for xml path('')), 1,2,'')+']'
SET @convert='SELECT * FROM (SELECT * from #TABLE) test pivot (max(value) for item_name in ('+@columns+')) as pivotable order by 1,2'

EXECUTE (@convert)
DROP TABLE #TABLE


 
Example :
 
User-added image