SCCM: Device Collection Based On a Local Group Membership

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:

SELECT distinct
  Computer.Name0 AS [Computer Name]
FROM
  v_GS_LocalGroupMembers0 AS LGMs
  INNER JOIN v_R_System AS Computer
    ON LGMs.ResourceID = Computer.ResourceID
INNER JOIN v_RA_System_SystemOUName AS OUs ON Computer.ResourceID = OUs.ResourceID
WHERE (
(OUs.System_OU_Name0 IN (@CompOU))

AND	(LGMs.Name0 = N'Administrators')
	AND NOT (
		LGMs.Account0 = N'Domain Admins' AND LGMs.Category0 = N'Group' AND LGMs.Type0 = N'Domain'
	)
	AND NOT (
		LGMs.Account0 = N'Administrator' AND LGMs.Category0 = N'UserAccount' AND LGMs.Type0 = N'Local' AND LGMs.Name0 = N'Administrators'
	)
	AND NOT (
		LGMs.Account0 = N'ServiceDesk Workstations Administrators' AND LGMs.Category0 = N'Group' AND LGMs.Type0 = N'Domain' AND LGMs.Domain0 = N'EXAMPLE'
	)
  )

SELECT distinct

Computer.Name0 AS [Computer Name]

FROM

v_GS_LocalGroupMembers0 AS LGMs

INNER JOIN v_R_System AS Computer

ON LGMs.ResourceID = Computer.ResourceID

INNER JOIN v_RA_System_SystemOUName AS OUs ON Computer.ResourceID = OUs.ResourceID

WHERE (

(OUs.System_OU_Name0 IN (@CompOU))

AND (LGMs.Name0 = N'Administrators')

AND NOT (

LGMs.Account0 = N'Domain Admins' AND LGMs.Category0 = N'Group' AND LGMs.Type0 = N'Domain'

)

AND NOT (

LGMs.Account0 = N'Administrator' AND LGMs.Category0 = N'UserAccount' AND LGMs.Type0 = N'Local' AND LGMs.Name0 = N'Administrators'

)

AND NOT (

LGMs.Account0 = N'ServiceDesk Workstations Administrators' AND LGMs.Category0 = N'Group' AND LGMs.Type0 = N'Domain' AND LGMs.Domain0 = N'EXAMPLE'

)

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:

SELECT DISTINCT
v_RA_System_SystemOUName.System_OU_Name0
FROM
  v_RA_System_SystemOUName
ORDER BY v_RA_System_SystemOUName.System_OU_Name0 ASC

SELECT DISTINCT

v_RA_System_SystemOUName.System_OU_Name0

FROM

v_RA_System_SystemOUName

ORDER BY v_RA_System_SystemOUName.System_OU_Name0 ASC

I modified RenderSQLReportFromPosh.v1.000.ps1 so it could populate AD DS group in addition to get data from reports. Here’s its code:

#requires -version 2.0
###############################################################################
# Render SQL Reports using PowerShell
# This script is using the new Posh v 2.0 cmdlet New-WebServiceProxy
# FileName: RenderSQLReportFromPosh.v1.000.ps1
# Authors: Stefan Stranger (Microsoft)
# Help from: Jin Chen (Microsoft)
# Example of Rendering the OpsMgr Licenses Report from the Generic Report Library
#
# v1.000 – 15/05/2010 - stefstr - initial sstranger's release
# 10/29/2013 - Modified by Kirill 'kf' Nikolaev to satisfy SCCM needs
###############################################################################

#Log file path
$ScriptPath = Split-Path $MyInvocation.MyCommand.Path
$ScriptName = ($MyInvocation.MyCommand.Name).Substring(0,($MyInvocation.MyCommand.Name).Length-4)
$Log = Join-Path $ScriptPath "$ScriptName.txt"

Add-Content $Log (Get-Date)

#Clear-Content $Log -ErrorAction SilentlyContinue
$Error.Clear()
			
#Define Variables            
#Enter URI to asmx file on Report Server            
$URI  = 'https://reportserver.example.com//ReportServer//ReportExecution2005.asmx?wsdl'
#Enter Report Path
$ReportPath = '/ConfigMgr_CAS/Compliance and Settings Management/Workstations with non-default local administrators'
#Enter group name to fill with workstations
$GroupName = 'Self-Managed Workstations'

$format = 'csv'
$deviceinfo = ''
$extention = ''
$mimeType = ''
$Result = ''
$render = ''
$encoding = 'UTF-8'
$warnings = $null            
$streamIDs = $null            
$Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential -namespace 'ReportExecution2005'
            
            
$rsExec = new-object ReportExecution2005.ReportExecutionService            
$rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials             

$execInfo = @($ReportPath, $null)             

#Load the selected report.            
$rsExec.GetType().GetMethod('LoadReport').Invoke($rsExec, $execInfo) | out-null              

#Report Parameters            
#Depending on the number of Parameters being used in the Report you need to add more Parameters.            
#Search the rdl file for the correct parameter names.
$param1 = new-object ReportExecution2005.ParameterValue
$param1.Name = 'CompOU'
$param1.Value = 'EXAMPLE.COM/WORKSTATIONS'

$param2 = new-object ReportExecution2005.ParameterValue
$param2.Name = 'CompOU'
$param2.Value = 'EXAMPLE.COM/WORKSTATIONS-OLD'

$parameters = [ReportExecution2005.ParameterValue[]] ($param1, $param2)             
$ExecParams = $rsExec.SetExecutionParameters($parameters, 'en-us');
$render = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)             
$Result = [text.encoding]::ascii.getString($render)             

$ComputerNames = @()
$SplittedResult = $Result.Split("`n")
for ($i = 1; $i -le $SplittedResult.Count-3) { #For unknown reason, last 3 rows of SSRS answer are blank, so we have to cut them and a title too.
	$ComputerNames += ($SplittedResult[$i]).Substring(0,($SplittedResult[$i]).Length-1) #Again, for unknown reason, there is an invisible line-feed symbol, we remove it.
	$i++
}
$ComputerObjects = @()
foreach ($Computer in $ComputerNames) {
	$ComputerObjects += Get-ADComputer $Computer
}
$CurrentMembers = @()
$CurrentMembers = Get-ADGroupMember -Identity $GroupName

$ToAdd = @()
$ToRemove = @()
if ($CurrentMembers) {
	if ($ComputerObjects) {
		$CompareResult = Compare-Object -ReferenceObject $ComputerObjects -DifferenceObject $CurrentMembers
		foreach ($Item in $CompareResult) {
			$DN = $Item.InputObject.DistinguishedName
			if ($Item.SideIndicator -eq '<=') {
				$ToAdd += $Item.InputObject
				Add-Content $Log "$DN - ToAdd"
			}
			elseif ($Item.SideIndicator -eq '=>') {
				$ToRemove += $Item.InputObject
				Add-Content $Log "$DN - ToRemove"
			}
		}
	}
	else {
		foreach ($Item in $CompareResult) {
			$DN = $Item.DistinguishedName
			$ToRemove += $Item.DistinguishedName
			Add-Content $Log "$DN - ToRemove"
		}
	}
}
else {
	foreach ($Item in $ComputerObjects) {
		$DN = $Item.DistinguishedName
		$ToAdd += $Item.DistinguishedName
		Add-Content $Log "$DN - ToAdd"
	}
}
if ($ToAdd) {
	try {
		Add-ADGroupMember -Identity $GroupName -Members $ToAdd -ErrorAction SilentlyContinue
	}
	catch {
		Add-Content $Log 'Cannot add'
		Add-Content $Log $Error[0]
	}
}
if ($ToRemove) {
	try {
		Remove-ADGroupMember -Identity $GroupName -Members $ToRemove -ErrorAction SilentlyContinue -Confirm:$false
		}
	catch {
		Add-Content $Log 'Cannot remove'
		Add-Content $Log $Error[0]
	}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

#requires -version 2.0

###############################################################################

# Render SQL Reports using PowerShell

# This script is using the new Posh v 2.0 cmdlet New-WebServiceProxy

# FileName: RenderSQLReportFromPosh.v1.000.ps1

# Authors: Stefan Stranger (Microsoft)

# Help from: Jin Chen (Microsoft)

# Example of Rendering the OpsMgr Licenses Report from the Generic Report Library

# v1.000 – 15/05/2010 - stefstr - initial sstranger's release

# 10/29/2013 - Modified by Kirill 'kf' Nikolaev to satisfy SCCM needs

###############################################################################

#Log file path

$ScriptPath = Split-Path $MyInvocation.MyCommand.Path

$ScriptName = ($MyInvocation.MyCommand.Name).Substring(0,($MyInvocation.MyCommand.Name).Length-4)

$Log = Join-Path $ScriptPath "$ScriptName.txt"

Add-Content $Log (Get-Date)

#Clear-Content $Log -ErrorAction SilentlyContinue

$Error.Clear()

#Define Variables

#Enter URI to asmx file on Report Server

$URI = 'https://reportserver.example.com//ReportServer//ReportExecution2005.asmx?wsdl'

#Enter Report Path

$ReportPath = '/ConfigMgr_CAS/Compliance and Settings Management/Workstations with non-default local administrators'

#Enter group name to fill with workstations

$GroupName = 'Self-Managed Workstations'

$format = 'csv'

$deviceinfo = ''

$extention = ''

$mimeType = ''

$Result = ''

$render = ''

$encoding = 'UTF-8'

$warnings = $null

$streamIDs = $null

$Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential -namespace 'ReportExecution2005'

$rsExec = new-object ReportExecution2005.ReportExecutionService

$rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials

$execInfo = @($ReportPath, $null)

#Load the selected report.

$rsExec.GetType().GetMethod('LoadReport').Invoke($rsExec, $execInfo) | out-null

#Report Parameters

#Depending on the number of Parameters being used in the Report you need to add more Parameters.

#Search the rdl file for the correct parameter names.

$param1 = new-object ReportExecution2005.ParameterValue

$param1.Name = 'CompOU'

$param1.Value = 'EXAMPLE.COM/WORKSTATIONS'

$param2 = new-object ReportExecution2005.ParameterValue

$param2.Name = 'CompOU'

$param2.Value = 'EXAMPLE.COM/WORKSTATIONS-OLD'

$parameters = [ReportExecution2005.ParameterValue[]] ($param1, $param2)

$ExecParams = $rsExec.SetExecutionParameters($parameters, 'en-us');

$render = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)

$Result = [text.encoding]::ascii.getString($render)

$ComputerNames = @()

$SplittedResult = $Result.Split("`n")

for ($i = 1; $i -le $SplittedResult.Count-3) { #For unknown reason, last 3 rows of SSRS answer are blank, so we have to cut them and a title too.

$ComputerNames += ($SplittedResult[$i]).Substring(0,($SplittedResult[$i]).Length-1) #Again, for unknown reason, there is an invisible line-feed symbol, we remove it.

$i++

}

$ComputerObjects = @()

foreach ($Computer in $ComputerNames) {

$ComputerObjects += Get-ADComputer $Computer

}

$CurrentMembers = @()

$CurrentMembers = Get-ADGroupMember -Identity $GroupName

$ToAdd = @()

$ToRemove = @()

if ($CurrentMembers) {

if ($ComputerObjects) {

$CompareResult = Compare-Object -ReferenceObject $ComputerObjects -DifferenceObject $CurrentMembers

foreach ($Item in $CompareResult) {

$DN = $Item.InputObject.DistinguishedName

if ($Item.SideIndicator -eq '<=') {

$ToAdd += $Item.InputObject

Add-Content $Log "$DN - ToAdd"

}

elseif ($Item.SideIndicator -eq '=>') {

$ToRemove += $Item.InputObject

Add-Content $Log "$DN - ToRemove"

}

else {

foreach ($Item in $CompareResult) {

$DN = $Item.DistinguishedName

$ToRemove += $Item.DistinguishedName

Add-Content $Log "$DN - ToRemove"

}

else {

foreach ($Item in $ComputerObjects) {

$DN = $Item.DistinguishedName

$ToAdd += $Item.DistinguishedName

Add-Content $Log "$DN - ToAdd"

}

if ($ToAdd) {

try {

Add-ADGroupMember -Identity $GroupName -Members $ToAdd -ErrorAction SilentlyContinue

}

catch {

Add-Content $Log 'Cannot add'

Add-Content $Log $Error[0]

}

if ($ToRemove) {

try {

Remove-ADGroupMember -Identity $GroupName -Members $ToRemove -ErrorAction SilentlyContinue -Confirm:$false

}

catch {

Add-Content $Log 'Cannot remove'

Add-Content $Log $Error[0]

}

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.

2 thoughts on “SCCM: Device Collection Based On a Local Group Membership”

Syed says:

November 10, 2016 at 23:36

Hi ,

Its very handy blog. Thanks for sharing. Can you pls let me know, what if i want to apply this Report Builder SQL query for only for specific collections instead of All Systems. Do I need to add some others commands in the ends of this .

I will be thankful to your help.

Thanks

Syed

1. Kirill Nikolaev says:
  
  November 11, 2016 at 02:45
  Hi Syed,
  
  To filter additionally by a collection, you need to modify your SQL-report as follows:
  1. Join v_FullCollectionMembership view into the request.
  2. Add collection filtering condition.
  Your report should look like this:
  SELECT distinct Computer.Name0 AS [Computer Name] FROM v_GS_LocalGroupMembers0 AS LGMs INNER JOIN v_R_System AS Computer ON LGMs.ResourceID = Computer.ResourceID INNER JOIN v_RA_System_SystemOUName AS OUs ON Computer.ResourceID = OUs.ResourceID INNER JOIN v_FullCollectionMembership AS FCM ON Computer.ResourceID = FCM.ResourceID
  WHERE ( (OUs.System_OU_Name0 IN (@CompOU)) AND FCM.CollectionID = 'SMS00001'
  AND (LGMs.Name0 = N'Administrators') AND NOT ( LGMs.Account0 = N'Domain Admins' AND LGMs.Category0 = N'Group' AND LGMs.Type0 = N'Domain' ) AND NOT ( LGMs.Account0 = N'Administrator' AND LGMs.Category0 = N'UserAccount' AND LGMs.Type0 = N'Local' AND LGMs.Name0 = N'Administrators' ) AND NOT ( LGMs.Account0 = N'ServiceDesk Workstations Administrators' AND LGMs.Category0 = N'Group' AND LGMs.Type0 = N'Domain' AND LGMs.Domain0 = N'EXAMPLE' ) )
  
  I suggest you to create a new variable in the report to set FCM.CollectionID’s value dynamically.
  
  Hope this helps!

Microsoft Technologies and Stuff

SCCM: Device Collection Based On a Local Group Membership

2 thoughts on “SCCM: Device Collection Based On a Local Group Membership”

Leave a Reply Cancel reply