audit_gs_ad.ps1

$CommandText = @"
SELECT
	[t1].[computer_id]
	, [t1].[name]
    , [t1].[serial_num]
    , [t1].[last_inventory]
	, [GS] = (SELECT TOP 1 UPPER([hostname]) FROM mmsettings)
FROM
	[computer] [t1]
"@

$GSSQLData = @()
$GSServers = Get-GSServers
foreach ($GSServer in $GSServers) {
    $GSSQLData += Get-GSSQLData -Instance "$GSServer\SQLEXPRESS" -Database "eXpress" -CommandText $CommandText
}
$GSSQLDataByGS = $GSSQLData | Sort-Object -Property GS
 
if ( ($GSSQLDataByGS | Measure-Object).Count -eq 0 ) {
    Write-Warning "No SQL data returned."
    return
}

$ADData = Get-ADComputer -Filter * -Properties SerialNumber
if ( ($ADData | Measure-Object).Count -eq 0 ) {
    Write-Warning "No AD data found."
    return
}

$ADSerialNumbers = $ADData.SerialNumber | Where-Object { $_ }

$GSSQLDataByGS | Where-Object serial_num -notin $ADSerialNumbers | Format-Table -AutoSize