Collection Comparison Tool

Here is the base of a report I’ve written that allows you to quickly identify systems that are members of two collections. This can be useful in identifying a list of systems that someone may be inquiring about being in a very large collection.

What I do is take the system list provided, create a temporary collection with some relevant name and import the list of computers. Then I use this utility to compare one vs. the other.

The base SQL looks like this:

Select *

From vcomputer v

WHERE

v.[guid] in
(
Select ResourceGuid
From CollectionMembership
Where CollectionGuid = '%collectionA%'
)
AND

v.[guid] in
(
Select ResourceGuid
From CollectionMembership
Where CollectionGuid = '%collectionB%'
)

ORDER BY
v.[Name]

Then you need to just create two global parameters named collectionA and collectionB. I use item pickers with collection filters and that’s basically it.

This SQL is also attached below.