SQL Query to get the OS Image assigned to computers

Products

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

Issue/Introduction

How to get the list of computers with its link to assigned OS Image using a SQL Query ?

Environment

Client Automation - All Versions

Resolution

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 :