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.
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 andlower(i.[System Type])like'%win32%'
-- All Computers without a hotfix installed
selectdistinct it.[Guid] from Inv_AeX_OS_Quick_Fix_Engineering qfe join Item it on it.[Guid] = qfe.[_ResourceGuid] where qfe.[Hotfix ID] notin('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.
Imported Document ID: HOWTO7674
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe