Using PowerShell to find all groups with members in a different domain

Yesterday I was asked by the Security Team to help them out with a project they were working on.  There is an OU with over 2,000 groups in it and they needed to find the names of all the groups whose members where in a different domain. 

#This script requires the Microsoft 'ActiveDirectory' module to be loaded.  
#The module is part of the Win7 RSAT and comes standard on Server 2008 R2
$Erroractionpreference = "SilentlyContinue" # For whatever reason, there are groups that can't be bound to.  
											# I will guess these are orphaned. In either case this line prevents errors from being displayed
foreach ($group in Get-ADGroup -SearchBase 'OU=SOMETHING,OU=GROUPS,OU=SOMETHING,OU=BUSINESSUNIT,DC=EmptyGarden,DC=info' -Filter *)
	{
	foreach ($user in Get-ADGroupMember -Identity $Group.Name)
		{
		if ($user.distinguishedName -notlike '*AnotherDomain,*') 
			{
			$group.name
			break # We only care if one user is a member of a different domain.  Thus, once we find one, we get out of the IF loop and grab another group.
			}
		}
	}

Powershell–Getting information from AD into the SCCM database

Recently I was asked to deliver a report on the count of machines in AD and their location.  In our environment we use a computer name identifier to designate location.  But, you could use OU information or eve AD Site information.  So, I wrote a PowerShell script that grabs all three and puts the information into SQL.  You can then merge the tables together and create a view.  You may be asking, “doesn’t SCCM System Discovery provide this information?”  Well, yes and no.  My PowerShell script returns more computers than SCCM is discovering.  Plus, the PowerShell script also returns information from AD Sites and Services as well as OU Description that can’t be obtained using SCCM discovery.

Get-ADComptuerInformation

Import-Module[DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
# Get all of the domain names
# Connect to RootDSE
$rootDSE = [ADSI]"LDAP://RootDSE"
# Connect to the Configuration Naming Context
$configSearchRoot = [ADSI]("LDAP://" + `
$rootDSE.Get("configurationNamingContext"))
# Configure the filter
$filter = "(NETBIOSName=*)"
# Search for all partitions where the NetBIOSName is set
$configSearch = New-Object DirectoryServices.DirectorySearcher($configSearchRoot, $filter)
# Configure search to return dnsroot and ncname attributes
$retVal = $configSearch.PropertiesToLoad.Add("dnsroot")
$retVal = $configSearch.PropertiesToLoad.Add("ncname")
#$configSearch.FindAll() | Select-Object @{n="dnsroot";e={$_.Properties.dnsroot}},  @{n="ncname";e={$_.Properties.ncname}}
#$DomainNames = foreach ($i in $configSearch.FindAll()) {$i.Properties.ncname}
$DomainNames = foreach ($i in $configSearch.FindAll()) {@{[string]$i.Properties.ncname = [string]$i.Properties.dnsroot}}
# Get computer information from AD into SQL
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SQL SERVER NAME]; Initial Catalog=[SCCM DATABASE NAME]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADComputerInformation"
$cmd.ExecuteNonQuery()

foreach ($domain in $DomainNames) {
	#$LDAPDomainName = $domain.Keys
	#$LDAPDomainName = "`'$LDAPDomainName'"
	#[string]$DNSDomainName = $domain.Values
	$ADService = Connect-QADService -Service $domain.Values
	$ComputersInDomain = Get-QADComputer -SearchRoot $ADService.DefaultNamingContextDN -service $ADService.Domain.Name -SearchScope Subtree -Inactive:$false |
	Select OperatingSystem, ParentContainerDN, Name, domain
	# Write to SQL

	foreach ($i in $ComputersInDomain) {
		$cmd = $conn.CreateCommand()
		$OperatingSystem = $i.OperatingSystem.ToString()
		$ParentContainerDN = $i.ParentContainerDN.ToString()
		$Name = $i.Name.ToString()
		$domain = $i.domain.ToString()
		$cmd.CommandText ="INSERT ADComputerInformation VALUES ('$OperatingSystem','$ParentContainerDN', '$Name', '$domain')"
		$cmd.ExecuteNonQuery()
		}
	Clear-Variable computersindomain
	}
$conn.Close()


# Old working area
#$var = Get-QADComputer -SearchRoot['LDAP DOMAIN] -SearchScope Subtree -SizeLimit 1 -Inactive:$false |
#	group operatingsystem | sort count -Descending 
#
#$var = $var.Group

# SQL
#USE [SCCM DATABASE]
#GO
#
#/****** Object:  Table [dbo].[ADComputerInformation]    Script Date: 07/29/2011 09:09:15 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADComputerInformation](
#	[OperatingSystem] [nvarchar](256) NULL,
#	[ParentContainerDN] [nvarchar](max) NULL,
#	[Name] [nvarchar](256) NULL,
#	[Domain] [nvarchar](256) NULL
#) ON [PRIMARY]
#
#GO
Get-ADOUInfo
Import-Module [DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
$var = Get-QADObject -Type 'organizationalUnit' | select DN, Description, ParentContainerDN
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SQL Servers]; Initial Catalog=[SCCM Database]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADOUInformation"
$cmd.ExecuteNonQuery()
foreach ($i in $var) {
	if ($i.Description -eq $Null) {$description = $Null}
	else {$description = $i.Description.ToString()}
	if ($i.DN -eq $null) {$DN = $null}
	else {$DN = $i.DN.ToString()}
	if ($i.ParentcontainerDN -eq $null) {$ParentContainerDN = $Null}
	else {$ParentContainerDN = $i.ParentContainerDN.tostring()}
$cmd.CommandText ="INSERT ADOUInformation VALUES ('$DN','$description', '$ParentContainerDN' )"
$cmd.ExecuteNonQuery()
}
$conn.Close()

#SQL
#
#USE [SCCM DATABASE]
#GO
#
#/****** Object:  Table [dbo].[ADOUInformation]    Script Date: 07/29/2011 10:44:46 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADOUInformation](
#	[DN] [nvarchar](max) NULL,
#	[Description] [nvarchar](256) NULL,
#	[ParentContainerDN] [nvarchar] (max) NULL
#) ON [PRIMARY]
#
#GO


Get-ADSiteInfo
Import-Module [DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
$var = Get-QADObject -Searchroot "CN=Subnets,CN=Sites,CN=Configuration,DC=[FOREST ROOT],DC=[FOREST ROOT]" -includedproperties "Description", "Location" | select description, location
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SCCM SERVER]; Initial Catalog=[SCCM Database]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADSiteInformation"
$cmd.ExecuteNonQuery()
foreach ($i in $var) {
	if ($i.Description -eq $null) {$description = $null}
	else {$description = $i.Description.ToString()}
	if ($i.location -eq $null) {$location = $null}
	else {$location = $i.location.ToString()}
$cmd = $conn.CreateCommand()
$cmd.CommandText ="INSERT ADSiteInformation VALUES ('$location','$description' )"
$cmd.ExecuteNonQuery()
}
$conn.Close()

#SQL Code
#
#USE [SCCM DATABASE]
#GO
#
#/****** Object:  Table [dbo].[ADSiteInformation]    Script Date: 07/28/2011 13:18:14 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADSiteInformation](
#	[LocationCode] [nchar](10) NULL,
#	[LocationDescription] [nvarchar](max) NULL
#) ON [PRIMARY]
#
#GO

Delete-SCCMCollectionRule : Addition to the SCCM-Commands.psm1

Did you know that there is SCCM-Commands powershell module?  You can get them here:  http://www.snowland.se/sccm-posh/

Today I had a need to delete a collection member.  But there isn’t a function in the SCCM-Commands powershell module that can do that.

Well, there is now!!  Just copy this function into the SCCM-Commands.psm1 file

 

Function Delete-SCCMCollectionRule { 

    [CmdletBinding()] 

    PARAM ( 

        [Parameter(Mandatory=$true,  HelpMessage="SCCM Server")][Alias("Server","SmsServer")][System.Object] $SccmServer, 

        [Parameter(Mandatory=$true,  HelpMessage="CollectionID", ValueFromPipelineByPropertyName=$true)] $collectionID, 

        [Parameter(Mandatory=$true,  HelpMessage="Rule Name", ValueFromPipeline=$true)] [String] $queryRuleName

                ) 

  

    PROCESS { 

                #$CollectionID = CEN00247

                #$SCCMServer = $SCCM

                #$QueryRuleName = LWMN00x2

        $coll = [wmi]"$($SccmServer.SccmProvider.NamespacePath):SMS_Collection.CollectionID='$collectionID'"

             # Find each computer 

                        foreach ($i in $queryRuleName) 

                            {$computer = Get-SCCMComputer -sccmServer $SccmServer -NetbiosName $i

                   # See if the computer is already a member 

                $found = $false

                if ($coll.CollectionRules -ne $null) { 

                foreach ($member in $coll.CollectionRules)

                                    { 

                  if ($member.ResourceID -eq $computer.ResourceID) {$found = $true} 

                    } 

                } 

            if ($found) { 

                                $ResourceToDelete = $coll.CollectionRules | where {$_.rulename -eq $i}

                                $coll.DeleteMembershipRule($ResourceToDelete)

                           } else { 

                Write-Verbose "Computer $queryRuleName is not in the collection"

                    }     

                    } 

    } 

        }