Hello Jonathan,
The SQL Query is complex as some information is stored in General Inventory table (inv_generalinventory_item).
Here is an example of SQL Query :
SELECT
host_name [Name],
d.class_name [Class Name],
OS_Name.item_value_text [OS Name],
OS_SP.item_value_text [Service Pack],
a.ip_address [IP Address],
primary_mac_address [MAC Address],
System_Vendor.item_value_text [Vendor Name],
System_Model.item_value_text [Model Name],
System_Type.item_value_text [Type],
System_TotalMemory.item_value_double/(1024*1024*1024) [Total Memory Gb],
dateadd ( ss, a.last_run_date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) [Agent Last Run],
serial_number [Serial Number],
t.label [Domain Name],
CASE a.derived_status_am
WHEN -4 THEN 'Preregistered'
WHEN -3 THEN 'Agentless'
WHEN -2 THEN 'Not Installed'
WHEN -1 THEN 'Legacy'
WHEN 0 THEN 'Operationnal'
WHEN 1 THEN 'Alert'
WHEN 2 THEN 'Warning'
WHEN 3 THEN 'Information'
ELSE ''
END [AM Status],
CASE
WHEN a.derived_status_sd=0 THEN 'Operationnal'
WHEN a.derived_status_sd & 0x00000001 = 0x00000001 THEN 'Locked by RAC'
WHEN a.derived_status_sd & 0x00000002 = 0x00000002 THEN 'Locked by Move'
WHEN a.derived_status_sd & 0x00000004 = 0x00000004 THEN 'Locked by Roam'
WHEN a.derived_status_sd & 0x00000001 = 0x00000080 THEN 'Locked by Migration'
WHEN a.derived_status_sd & 0x10000000 = 0x10000000 THEN 'Not Installed'
WHEN a.derived_status_sd & 0x20000000 = 0x20000000 THEN 'Preregistered'
ELSE ''
END [SD Status],
CASE a.derived_status_rc
WHEN 7 THEN 'Not Installed'
WHEN 9 THEN 'Preregistered'
WHEN 401 THEN 'Locked'
WHEN 402 THEN 'Active Session'
WHEN 403 THEN 'Listening'
WHEN 404 THEN 'Not Listening'
WHEN 405 THEN 'Migrated'
WHEN 406 THEN 'Stopped'
ELSE ''
END [RC Status]
FROM ca_discovered_hardware h LEFT JOIN ca_agent a ON h.dis_hw_uuid=a.object_uuid
LEFT JOIN ca_n_tier t ON a.domain_id=t.domain_id
LEFT JOIN ca_class_def d ON h.class_id=d.class_id
LEFT JOIN (
SELECT gi.object_uuid, gi.item_value_text
FROM inv_generalinventory_item gi(NOLOCK)
JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
AND gi.domain_uuid = iname.domain_uuid
AND gi.item_name_id = iname.item_name_id
AND iname.item_name = 'Operating System'
WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$Operating System$' and domain_uuid=gi.domain_uuid)
) OS_Name ON OS_Name.object_uuid = h.dis_hw_uuid
LEFT JOIN (
SELECT gi.object_uuid, gi.item_value_text
FROM inv_generalinventory_item gi(NOLOCK)
JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
AND gi.domain_uuid = iname.domain_uuid
AND gi.item_name_id = iname.item_name_id
AND iname.item_name = 'Service Pack'
WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$Operating System$' and domain_uuid=gi.domain_uuid)
) OS_SP ON OS_SP.object_uuid = h.dis_hw_uuid
LEFT JOIN (
SELECT gi.object_uuid, gi.item_value_text
FROM inv_generalinventory_item gi(NOLOCK)
JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
AND gi.domain_uuid = iname.domain_uuid
AND gi.item_name_id = iname.item_name_id
AND iname.item_name = 'Vendor'
WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
) System_Vendor ON System_Vendor.object_uuid = h.dis_hw_uuid
LEFT JOIN (
SELECT gi.object_uuid, gi.item_value_text
FROM inv_generalinventory_item gi(NOLOCK)
JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
AND gi.domain_uuid = iname.domain_uuid
AND gi.item_name_id = iname.item_name_id
AND iname.item_name = 'Model'
WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
) System_Model ON System_Model.object_uuid = h.dis_hw_uuid
LEFT JOIN (
SELECT gi.object_uuid, gi.item_value_text
FROM inv_generalinventory_item gi(NOLOCK)
JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
AND gi.domain_uuid = iname.domain_uuid
AND gi.item_name_id = iname.item_name_id
AND iname.item_name = 'Type'
WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
) System_Type ON System_Type.object_uuid = h.dis_hw_uuid
LEFT JOIN (
SELECT gi.object_uuid, gi.item_value_double
FROM inv_generalinventory_item gi(NOLOCK)
JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
AND gi.domain_uuid = iname.domain_uuid
AND gi.item_name_id = iname.item_name_id
AND iname.item_name = 'Total Memory'
WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
) System_TotalMemory ON System_TotalMemory.object_uuid = h.dis_hw_uuid
Regards,
Jean-Yves