Show all Relevant Collection Memberships (SQL)

Purpose: Query all the relevant Collection Memberships, defined by you.

After a while, your workstations might be members of many different Device Collections. If you need to point out which collections are actually relevant in some scenarios, you need to use prefixes in your Collection Names. With these prefixes, you can filter out unrelevant collections and create a report, which only shows the relevant Device Collections.

An example: Show all the relevant Device Collections that install applications

This will require that you have named your application deploying device collections with an APP-prefix, your application uninstalling device collections with an UNINSTALL-prefix and and that the query receives ComputerName as an parameter.

Because the ComputerName-parameter is inside a LIKE-clause, you can use a computer name prefix to get multiple computers with the same subname in the report results – or just one computer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
r.Netbios_Name0,
FCM.CollectionId, 
C.Name 
 
FROM 
dbo.v_R_System r 
join dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID 
join dbo.v_Collection C on C.CollectionID = FCM.CollectionID 
 
WHERE 
R.Name0 LIKE '%' + @ComputerName + '%' AND (
 C.Name LIKE 'APP%' AND NOT (
  C.Name LIKE 'APP UNINSTALL%'
  )
 )
 
ORDER BY r.Netbios_Name0, C.Name ASC
SELECT
r.Netbios_Name0,
FCM.CollectionId, 
C.Name 

FROM 
dbo.v_R_System r 
join dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID 
join dbo.v_Collection C on C.CollectionID = FCM.CollectionID 

WHERE 
R.Name0 LIKE '%' + @ComputerName + '%' AND (
 C.Name LIKE 'APP%' AND NOT (
  C.Name LIKE 'APP UNINSTALL%'
  )
 )

ORDER BY r.Netbios_Name0, C.Name ASC

Leave a Reply

Your email address will not be published. Required fields are marked *


− six = three

Post Navigation