Client Automation - How to get AM Inventory with SQL Query ?
Article ID: 123492
Updated On:
Products
CA Client Automation
Issue/Introduction
Asset Management General Inventory is stored in table inv_generalinventory.
What is the SQL Query to return General Inventory for one computer ?
Environment
Client Automation - All versions
Resolution
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
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 :
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
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 :
Feedback
Yes
No
Powered by
