New task came up recently – I need to separate in SCCM self-managed workstations from IT-managed ones. We define following criteria for IT-managed workstations: no other accounts are in local Administrators group except for built-in Administrator, Domain Admins group and a group for Service Desk administrators. All workstations are located in the same OU, so I cannot use OU-based collections.
As you may know, SCCM 2012 doesn’t have built-in tools to get local groups membership. Thanks to Sherry Kissinger who solved this problem for us using Compliance Settings. After you install her package, you’ll get a new Configuration Baseline and Configuration Item in SCCM console named as “WMI Framework For Local Groups with Logging” and “Local Group Members into WMI with Logging”. This package also creates 2 new tables and 1 view into SCCM database: LocalGroupMembers_DATA, LocalGroupMembers_HIST and v_GS_LocalGroupMembers0.
After creating and deploying baseline, you can use v_GS_LocalGroupMembers0 view to create reports based on local groups membership.
Don’t forget: you must not deploy that baseline to domain controllers! For example, you can create a collection which includes all your systems except domain controllers: create new device collection using All Systems as limiting collection and add it with include rule, then add All Domain Controllers collection with exclude rule. You can download MOF-file for such collection here.
Unfortunately, neither LocalGroupMembers_DATA, nor v_GS_LocalGroupMembers0 can be used in WQL-queries when you create a collection.
Am I stuck? Let’s review what do I had for now:
- I have all data about local groups membership in custom table.
- I can create any reports using that data.
- I can create collections using data from standard tables in SCCM DB.
- But I cannot create collections based on a data from custom SQL-tables.
I need a way to put data from table LocalGroupMembers_DATA into standard SCCM tables and PowerShell is here to save the day.
There are at least two ways to get data from SQL with PowerShell:
- Connect to DB directly and use T-SQL queries with SQL cmdlets.
- Connect to SQL Server Reporting Services using New-WebServiceProxy cmdlet. Stefan Stranger and Jin Chen wrote an example script to achieve it.
With PowerShell we can do anything with that SQL-data. Our goal is to populate device collections with workstations and here we go again with two different options:
- We can add computers into group in AD DS and then create a device collection using this group. For this method to work you need to activate Active Directory Group Discovery discovery method for site and domain where AD group will reside.
- Add computers into collection directly using Add-CMDeviceCollectionDirectMembershipRule cmdlet.
Since both group and a report will be useful for me in the future, I’m stick with them.
Now our scenario looks like this:
- Activate Active Directory Group Discovery.
- Collect local group membership using Compliance Settings.
- Create a report with gathered data an any SSRS.
- Get names of computers from this report with New-WebServiceProxy cmdlet.
- Add these computers into an AD group.
- Create a device collection by that AD group.
I build a report where I list all computers don’t comply with conditions discussed earlier.
Here is what first DataSet query looks like:
It can be easily expanded to include another set of groups to ignore.
Mind CompOU parameter: in web-interface you can select multiple OUs where to search computers.
To get a full list of OUs from a forest, you can use another query:
I modified RenderSQLReportFromPosh.v1.000.ps1 so it could populate AD DS group in addition to get data from reports. Here’s its code:
My modified script receives a report from $URL and $ReportPath locations, compares a list from it with members of $GroupName AD DS group and adds/removes computers from that group until it and the report would be the same.
You can find a path for log of actions in $Log variable. Here, script records all computers which were added or removed from the group.
OUs to search are defined into $param1 and $param2 variables. If you need more OUs, create a new parameter variables and do not forget to add them into $parameters.
As last, I created standard device collection based on AD group $GroupName.
You can download report as an RDL-file and a script here. Do not forget to create DataSource in the report to connect to your SSRS instance.