How to create an OC group that includes robots using SQL filter?

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

 How to create an OC group that includes robots using SQL filter?

Environment

Any UIM version

Resolution

All robots:

select distinct cs.cs_id from cm_computer_system cs 
inner join cm_device cd on cs.cs_id = cd.cs_id 

All robots with a specific version, example below for robot version 9.40:

select distinct cs.cs_id from cm_computer_system cs
inner join cm_device cd on cs.cs_id = cd.cs_id
inner join cm_nimbus_robot cr on cd.dev_id = cr.dev_id where cr.version like '%9.40%'

All robots for a given hub:

select distinct cs.cs_id from cm_computer_system cs 
inner join cm_device cd on cs.cs_id = cd.cs_id 
inner join cm_nimbus_robot cr on cd.dev_id = cr.dev_id where cr.hub = <HUB NAME>

Only active robots for a given hub:

select cd.cs_id
from cm_nimbus_robot cr
join cm_device cd on cd.dev_id = cr.dev_id
and robot_active = 1
and hub = '<hub name>'

Note: Change <hub name> with the target hub