titan90

Does anyone know the correct SQL syntax to add the use of a collection picker to the unmanaged computers report?

I can add the picker but it's chocking on the SQL query to make it use the picker.

Working SQL qUERY:

SELECT distinct va._ResourceGuid, vri.Name,
isnull(PU.Domain, N'') as Domain, isnull(PU.[User], N'') as [User],
isnull(AC.[OS Name], N'') as [OS Name],
isnull(AC.[OS Version], N'') as [OS Version], va.Status,
va.[Serial Number], va.[System Number], va.[Barcode], va.[Manufacturer], va.[Model]
FROM vAsset va
join vResourceItem vri on vri.Guid = va._ResourceGuid
join ResourceTypeHierarchy rth on rth.ResourceTypeGuid = vri.ResourceTypeGuid
left join Inv_AeX_AC_Identification AC on AC._ResourceGuid = va._ResourceGuid
left join Inv_AeX_AC_Primary_User PU on PU._ResourceGuid = va._ResourceGuid
and PU.[_id]=(SELECT TOP 1 p.[_id] FROM [Inv_AeX_AC_Primary_User] p WHERE va._ResourceGuid=p.[_ResourceGuid] order by p.[_id] desc)
inner join %SecurityFilterParameter% t on t._ResourceGuid = va._ResourceGuid
where rth.BaseResourceTypeGuid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F' and vri.IsManaged=0
AND (cast(%Status% as uniqueidentifier) = 0x0 or va._AssetStatusGuid= %Status% )
order by vri.Name

Need to add a call to use the collection picker: ??

INNER JOIN dbo.CollectionMembership cm ON Inv_AeX_AC_Identification
._ResourceGuid = cm.ResourceGuid
Where cm.CollectionGuid ='%Collection%'