#######################################################################################
# Get-PrimaryUserDevicesForAnADGroup.ps1
$Script:Version = "21.4.23.3"
# Author(s): Sean Huggans
#######################################################################################
 
$ADGroupName = "GROUPNAME"
$OutFilePath = "C:\Temp"
$global:SiteServer = "SITESERVERNAME"
$global:SiteCode = "FOO"
 
function Invoke-SQL
{
	param (
		[string]$dataSource = "$global:SiteServer",
		[string]$database = "CM_$global:SiteCode",
		[string]$sqlCommand = $(throw "Please specify a query.")
	)
 
	$connectionString = "Data Source=$dataSource; " +
	"Integrated Security=SSPI; " +
	"Initial Catalog=$database"
 
	$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
	$command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)
	$connection.Open()
 
	$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
	$dataset = New-Object System.Data.DataSet
	$adapter.Fill($dataSet) | Out-Null
 
	$connection.Close()
	$dataSet.Tables	
}
 
$GroupMachineList = New-Object System.Collections.ArrayList
#Loop through AD Group Members	
foreach ($UserObject in (Get-ADGroupMember -Identity $ADGroupName)) {
    # Fetch User's Primary devices from COnfigMgr DB
    $UserPrimaryMachines = Invoke-SQL -sqlCommand "select vrs.Name0 as [ComputerName], vru.Name0 as [UserName] from v_UsersPrimaryMachines  upm left join v_R_User AS vru on upm.UserResourceID = vru.ResourceID left join v_R_System vrs on upm.MachineID = vrs.ResourceID where vru.name0 like '%$($UserObject.SamAccountName)%'"
    foreach ($CompName in $UserPrimaryMachines) {
        # Exclude name patterns (IE ConfigMgr DPs)
        if ($CompName.'ComputerName' -notlike "SCCM*") {
            Write-Host "$($CompName.'ComputerName'), $($CompName.'UserName')"
            # Add them to the master machine list
            $GroupMachineList.Add($CompName.'ComputerName') | Out-Null
        }
    }
}
 
$GroupMachineList | Out-File -FilePath "$($OutFilePath)\MachinesofUsers.csv" -Encoding utf8 -NoClobber -Force