Unable to get SQL Query right

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.

Used to Identity Access Management consulting and build data warehouses to do what you’re asking. With 60k users, it’s surprising that there isn’t a IAM implementation doing what you’re asking. Would recommend you do not tackle this in Powershell, this is better to do in SQL (or any database engine). For the PeopleSoft, ideally that would be Linked Server in SQL so you can do direct queries. Then I would write a PS script to do a bulk insert of AD data in a table. Once you have the PS and AD data in SQL, then you can create a View and LEFT JOIN on the employee ID from PS to AD, making PS authoritative. This would give you the ability to then report on anything for discrepancies. While you CAN do some of this in Powershell, you would need to write code vs just simply filtering the view to get what you’re looking for.

To answer you’re question, first the assumption is the EmployeeId is numeric, not alpha-numeric or you will need single qoutes around each entry for your IN. Been a minute since I’ve been in SQL, but I think the NOT is in the wrong spot:

$ausers = '12345','12356','42452'
$ausers = $ausers -join (',{0}' -f [Environment]::NewLine)

$query = @"
SELECT * 
FROM dbo.PSOPRDEFN 
WHERE EMPLID NOT IN (
$($aUsers)
)
"@

$query

Output:

SELECT *
FROM dbo.PSOPRDEFN
WHERE EMPLID NOT IN (
12345,
12356,
42452
)

Thanks, that put me on the right path. I finally got it with a single statement:

"Select * from PSOPRDEFN Where EMPLID NOT IN ('{0}')" -f (($aUsers) -join "','")

What I was missing was the EMPLID is a CHAR, so needed single quotes around each index.