Collection Summary – Lay of the Land

In this article we will cover, Collection Summary – Lay of the Land.

I’ve seen a few of these pop up lately, but this is my take on a good summary of collections and their use in an environment. It isn’t a fancy report or anything, instead it is some old fashioned SQL and maybe 5 of 6 dashes of PowerShell magic to make things a bit easier to digest. The aim is to provide a large amount of ‘filterable’ fields in the data so that you can manipulate it after the fact. Below is a full list of the fields you will receive from this SQL query turned Frankenstein PowerShell script that heavily relies on DynamicParam.

CollectionID
CollectionName
CountOfAppDeployments
CountOfBaselineDeployments
CountOfExcludedFrom
CountOfExcludes
CountOfIncludedIn
CountOfIncludes
CountOfLimitedBy
CountOfPackageDeployments
CountOfPolicyDeployments
CountOfTSDeployments
CountOfUpdateDeployments
FullRefreshLength
IncrementalRefreshLength
LastMemberChangeTime
LimitToCollectionID
LimitToCollectionName
MemberCount
MW Description
And, MW Duration in Minutes
MW Enabled
MW IsGMT
Also, MW Name
MW Recurrence Type
MW ScheduleString
And, MW StartTime
MW Type
Refresh ScheduleString
RefreshType

Quite a few fields! Here are a few examples of the data this function returns as well.

Very cool stuff! We asked our SQL DB a few simple questions and it answered with all kinds of wonderful information!

I struggled to develop a ‘good’ SQL query to pull all this in (And I still don’t think I’m quite there, but it runs fast and returns accurate info). In all honesty, this project started with a PowerShell script that I was running with PoshRSJobs to query the SMS Provider and do all the ‘joins’ with PowerShell. It was incredibly slow, even running 30+ in parallel. In hindsight I’m not sure why I even started down that road. Bump on over to pure SQL, and I was returning data in under 10 seconds! Turns out, that early SQL query iteration didn’t fare well in a CAS environment. @Christopher83 was kind enough to run this… for over an hour. It never finished. RIP.

Some pointers and think-tanking with @AdamGrossTX has greatly improved the run time in both CAS and non-CAS environments. (Note: He still scoffs at my use of temporary tables) As a result of the SQL optimizations I’m now gathering the same data in 0-2 seconds in my environment, and ~9 seconds with the CAS. Woo! Acceptable!

Reports are cool, but you just kind of… look at them. In order to use the data you see in a report it is always going to require a step or two more. Maybe you pull the SQL query out of the report, or just export the data as a CSV and then import it somewhere. You’ve got options, and they all sound terrible (bit of hyperbole, I’m probably just lazy).

To recap, I started with a really slow PowerShell implementation, I translated it to a SQL query, I made it fast, now we should wrap it in a PowerShell function! Maybe sprinkle that magic on top that I talked about, such as dynamically generating parameters and a WHERE filter! First, a GIF of tab completion on CollectionName, solely because it is pretty satisfying to actually do it. Especially when you can create a brand new collection and it is instantly available.

Fancy! And it sparks some ideas that I’ll be working on. But, back to this. All of this is on GitHub of course, but below is the snippet I’m using to allow for tab completion of the fields @(‘CollectionID‘, ‘CollectionName‘, ‘LimitToCollectionID‘, ‘LimitToCollectionName‘). The SparkNotes summary, we query the database and find unique values for those four fields, and add them to a System.Management.Automation.RuntimeDefinedParameterDictionary. This allows the function to read the output from the query and use it as a ValidateSet for those parameters.
[/av_textblock]

Well, we can use the data! We have a good handful of parameters available to us. In the end, we are generating SQL WHERE filters. In fact, we even have a -GenerateWhereFilter switch for this function. The parameter/’where filter’ I’m going to focus on is -Unused. Running ‘Get-Help -Name Get-CMCollectionSummary -Parameter Unused’ says it best.

Return all collections which have no deployments, and are not included in, excluded from, or limiting other collections.

At this point, I believe the biggest risk might be if someone was using the collection(s) for reporting. And if that is the case… I’m sure they can recreate them, right? Let’s take a look at the filter. Note that I’m excluding the SMS collections regardless of their use. We likely don’t want to delete those, and SCCM would get mad if we did I am sure.

So my lab isn’t very exciting… But, @Christopher83 was able to quickly identify and delete over 150 collections using this! That sounds useful, unlike those collections we just got rid of. I would of course encourage you to double triple check what you are deleting. You have an awesome list of other filters available with this function as well. Many of them are booleans, such as -Empty $true adding to the filter to find empty collections. On the flip side, -Empty $false would only show collections which are not empty. You can combine the filters as you see fit. It is also worth noting that the Collection ID / Name parameters are string arrays. Being of the type [string[]], you can provide multiple values.

CollectionID
CollectionName
Empty
HasAppDeployment
HasBaselineDeployment
HasExcludes
HasIncludes
HasPackageDeployment
HasPolicyDeployment
HasTaskSequenceDeployment
HasUpdateDeployment
LimitToCollectionID
LimitToCollectionName
MWType
MW_Enabled
RefreshType
Unused
UsedAsExclude
UsedAsInclude
UsedAsLimitingCollection
WithNoDeployments

Now, a couple quick notes with all of this.

You WILL see duplicates if you have collections with more than one maintenance window. Despite this, I opted to leave in the maintenance window fields because I feel they provide useful data.
I have used the DBATools module, but you could use the Microsoft SQL module if you’d like. There are only two uses of Invoke-DBAQuery, and they could be directly replaced with Invoke-SQLCMD (Lines 86 and 585 at the time of writing)

That’s it folks! Fun stuff, at least I had fun creating this anyway. If you missed the links above here are the links to the SQL query being used, and the function itself.