Answer

A computer collection query is a correlated sub-query passing a GUID (unique identifier) to a hard coded query against the vComputer view. Part of the view's definition is that it uses vActiveAsset view; this would mean that the computers need to have an asset status of active (not retired…) to be in the collection. Because of this, you cannot have a collection of retired computers.

The following collection tends to use “IsManaged”, so the collection can be used in a policy action. The “IsManaged” show us computers that have an entry in the Inv_AeX_AC_Client_Agent table.

The collection SQL query needs to pass GUIDs from one of following recognized views or tables. There are other possible views or tables you can use, but not all are recognized. If you use a table or view that a collection query will not recognize, pass the GUID in a correlated sub-query or use a join to connect them, but use the GUID from a recognized table or view.

1. vResource
2. vComputerResource
3. vComputer
4. vItem
5. Item
6. Inv_AeX_AC_Identification

These examples are some of the default collection queries:

-- All Computers collection
select Guid from vComputerResource where IsManaged=1

-- All Platforms
select Guid from vComputer

-- All 32-bit Windows Computers
select r.Guid
from vComputerResource r, Inv_AeX_AC_Identification i
where r.Guid=i.[_ResourceGuid]
and r.IsManaged=1
and lower(i.[System Type]) like '%win32%'

Examples:

-- All Computers without a hotfix installed

select distinct it.[Guid]
from Inv_AeX_OS_Quick_Fix_Engineering qfe
join Item it on it.[Guid] = qfe.[_ResourceGuid]
where qfe.[Hotfix ID] not in ('KB921884')

-- All Computer with IP addresses like ...

select Guid from vComputerResource
where Guid in (
select tcp.[_ResourceGuid]
from [Inv_AeX_AC_TCPIP] tcp
where tcp.[IP Address]like'192.%'
or tcp.[IP Address]like'172.%'
or tcp.[IP Address]like'10.%'
)
and [IsManaged] =1

When you create a new collection SQL query, you default query is …

select Guid from vResource
where ResourceTypeGuid in (
select ResourceTypeGuid
from ResourceTypeHierarchy
where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f'
)

The BaseResourceTypeGuid column in the sub-query is a computer resource, so the default query shows all traditional and virtual computer resources.

Don't use too many correlated sub-queries in the where clause. This will slow down the execution time. Also look for circular references in the queries, so check the definitions of other collections you may use. And last, if you use a collection that filters on IP addresses from the Inv_AeX_AC_TCPIP table, they may not show up in the collection with the expected IP address you are looking for. This is due to the vComputer view.