How to get the list of computers with its link to assigned OS Image using a SQL Query ?
Client Automation - All Versions
Following SQL Query could be used :
SELECT comp.name 'Computer Name', prop1.value 'OS Image Name',
CASE prop2.value
WHEN 1000 THEN 'CURRENT'
WHEN 2000 THEN 'STOPPED'
WHEN 3000 THEN 'CANCEL PENDING'
WHEN 4000 THEN 'ERROR'
WHEN 5000 THEN 'DELETED'
WHEN 6000 THEN 'NO RESPONSE'
WHEN 7000 THEN 'UNMANAGED'
WHEN 8000 THEN 'ADS MANAGED'
WHEN 10000 THEN 'PLANNED'
WHEN 11000 THEN 'ACTIVATED'
WHEN 20000 THEN 'ANALZYING'
WHEN 21000 THEN 'PENDING'
WHEN 22000 THEN 'INSTALLING'
ELSE STR(prop2.value)
END 'Boot Status', SUBSTRING(prop3.value,14,23 ) 'Status Date'
FROM csm_object boot_conf
INNER JOIN csm_link l1 ON l1.parent=boot_conf.id
INNER JOIN csm_object param_val ON l1.child=param_val.id and param_val.name='osimage'
INNER JOIN csm_property prop1 ON prop1.object=param_val.id and prop1.name='value'
INNER JOIN csm_link l2 ON l2.child=boot_conf.id
INNER JOIN csm_object comp ON l2.parent=comp.id
INNER JOIN csm_property prop2 ON prop2.object=boot_conf.id and prop2.name='configstate'
INNER JOIN csm_property prop3 ON prop3.object=boot_conf.id and prop3.name='configstatetime'
WHERE boot_conf.class=1004 and param_val.class=106 and comp.class=102
Example :