How do I compare users from SQL query to AD then insert them?

Question

Import-Module ActiveDirectory 
$ADUSER = Get-Aduser -Filter * -Properties SamAccountName | Select-Object SamAccountName
$SQLQUERY = Invoke-Sqlcmd -Query "SELECT username AS SamAccountName FROM test.dbo.ad" -Database "TEST" -Server "DB-1"
$Compare = Compare-Object $ADUSER $SQLQUERY -Property 'SamAccountName' -passthru | ?{.SideIndicator -Eq '=>'} | SELECT SamAccountName
$Insert = "INSERT INTO TEST.dbo.ad SELECT $user" -Database "TEST" -Server "DB-1"
Foreach ($user in $Compare){
    Invoke-Sqlcmd -Query $Insert
}

I am using Powershell 5.0 and when I run this I get an error ".SideIndicator : The term '.SideIndicator' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again." What am I doing wrong? I am trying to pull AD users then a query and then compare AD to SQL and insert in.

It might be just a copy and paste error here but there is a $_ missing in front of the term .SideIndicator — Olaf
– Olaf, Commented Feb 9, 2022 at 21:50
Besides, I don't think that your $Insert string will expand automatically, you will need something like $ExecutionContext.InvokeCommand.ExpandString($Insert) (or put the expandable string inside the loop). — iRon
– iRon, Commented Feb 10, 2022 at 6:49

TheMadTechnician · Accepted Answer · 2022-02-09 22:35:48Z

0

As Olaf pointed out, you are missing the $_ from your Where statement on the fourth line. It should read:

$Compare = Compare-Object $ADUSER $SQLQUERY -Property 'SamAccountName' -passthru | ?{$_.SideIndicator -Eq '=>'} | SELECT SamAccountName

answered Feb 9, 2022 at 22:35

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I compare users from SQL query to AD then insert them?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related