I am working with a customer that would like to compare users in their PeopleSoft database to users in A.D., and pull any discrepancies (the thought being there are many in PS not in A.D.). My thought was to hit off the EmployeeID property in A.D., and then pull everyone from the table that is NOT in that list, but I seem unable to get the syntax quite right.
The following works just fine for returning everyone from the table where EMPLID is not blank:
$sqlServer = "myServer"
$dBase = "myDB"
$query = "Select * from dbo.PSOPRDEFN Where (NOT EMPLID='') AND (NOT OPRDEFNDESC='')"
$aPeopleSoft = Invoke-Sqlcmd -ServerInstance $sqlServer `
-Database $dBase `
-Query $query `
-Username "svcAccount" `
-Password "PW" `
-EncryptConnection: $false
I then attempted to do something like this:
#Get all users in A.D. where employeeID is not blank
$aUsers = (Get-ADUser -LDAPFilter "(employeeID=*)" -Properties EmployeeID).EmployeeID | Sort
#Convert $aUsers to comma-separated string
$aUsers = $aUsers -join ","
#query PS db for any users in table NOT in $aUsers
$sqlServer = "myServer"
$dBase = "myDB"
$query = "Select * from dbo.PSOPRDEFN Where NOT EMPLID in (" + $aUsers + )"
$aPeopleSoft = Invoke-Sqlcmd -ServerInstance $sqlServer `
-Database $dBase `
-Query $query `
-Username "svcAccount" `
-Password "PW" `
-EncryptConnection: $false
But I get an error “Incorrect syntax near ‘,’”. It does not like the comma delimiter, is seems. I have likewise tried running that against just the $aUsers array, without converting to string, but similarly get an error “Incorrect syntaxt near ‘0’”
Submitting the query like this does not produce an error, but doesn’t produce results either:
$query = "Select * from dbo.PSOPRDEFN Where NOT EMPLID LIKE '" + $aUsers + "'"
I could simply go with the first query, pulling Every record with a valid EMPLID, and then compare the two arrays, but with 60,000+ users this is going to take far too long. Hopefully this is something stupid simple I am missing in the query syntax. Appreciate any suggestions.