Data from 2 sources

PowerShell Help
1

Trying to get a list of users but pulling data from Get-aduser and a SQL DB at the same time. We have the Supervisor and Division in a separate SQL DB and while I will be merging that data with AD at some point I was curious how to get what I need now, for learning purposes.

I have a function to query the SQL db and save everything in $policytable, then I want to do a get-aduser and select the name from AD but the Supervisor from SQL ($policytable) and I am just not sure how to do it.

function connPolicy
{
	$PolicydataSource = "DATASOURCEIP"
	$Policyuser = "USER"
	$Policypwd = "PASSWORD"
	$Policydatabase = "NetworkPolicy"
	$PolicyconnectionString = "Server=$PolicydataSource;uid=$Policyuser; pwd=$Policypwd;Database=$Policydatabase;Integrated Security=False;"
	$Policyquery = "SELECT UserName,Division,Supervisor FROM tblUsers"
	$Policyconnection = New-Object System.Data.SqlClient.SqlConnection
	$Policyconnection.ConnectionString = $PolicyconnectionString
	$Policyconnection.Open()
	$Policycommand = $Policyconnection.CreateCommand()
	$Policycommand.CommandText = $Policyquery
	$Policyresult = $Policycommand.ExecuteReader()
	$Policytable = new-object "System.Data.DataTable"
	$Policytable.Load($Policyresult)
	$Policyconnection.Close()
}

connPolicy
get-aduser -filter * -searchbase "MYSEARCHBASE" | Select Name, @{Name='Supervisor';E={$policytable | where {$_.UserName -eq $_.SamaccountName} | Select -expand Division}}

I get the username but no supervisor and I know why, I just don’t know how to fix it.
I know my problem is with:

where {$_.UserName -eq $_.SamaccountName}

but I don’t know how to reference “SamAccountName” from the Get-AdUser.

Hopefully all of this makes sense!

Thanks,
Scott

2

Ok, so I was able to get what I needed with what is below, although I probably over complicated it:

connPolicy
$ADUser = get-aduser -filter * -searchbase "MYSEARCHBASE"
$results = foreach ($User in $ADUser) {
$Supervisor = $policytable | where {$_.UserName -eq $User.SamAccountName} | Select -expand Division
[PSCustomObject]@{
Name = $User.Name
Supervisor = $Supervisor
}
}
$results
3

backwards. yes it is more complicated than creating a custom object. The thing is you’re only focused on the data from sql not all AD users.

$policytable | Select username, @{N=‘Supervisor’;E={$un = $_.username;(get-aduser -filter {SamaccountName -eq $un}).name}}

4

Got it! Thanks.

I was not thinking of it that way. That makes perfect sense.

Scott

5

When you wrap a function around your code, you change the scope of the variables. Anything declared in the function, is only visible to the function. First, you need to return the data from the function. Next, you are returning a DataTable, not a PSObject, so you need to use Select-Object to generate a PSObject. The last part of the issue is in your calculated expression, which is context of $_. If you’re going to pipe to where in the calc expression, you need to store the variable and then try your query. Not sure if this will work like you expect and you may need to still do a loop and build a final object.

function Get-ConnPolicy
{
	$PolicydataSource = "DATASOURCEIP"
	$Policyuser = "USER"
	$Policypwd = "PASSWORD"
	$Policydatabase = "NetworkPolicy"
	$PolicyconnectionString = "Server=$PolicydataSource;uid=$Policyuser; pwd=$Policypwd;Database=$Policydatabase;Integrated Security=False;"
	$Policyquery = "SELECT UserName,Division,Supervisor FROM tblUsers"
	$Policyconnection = New-Object System.Data.SqlClient.SqlConnection
	$Policyconnection.ConnectionString = $PolicyconnectionString
	$Policyconnection.Open()
	$Policycommand = $Policyconnection.CreateCommand()
	$Policycommand.CommandText = $Policyquery
	$Policyresult = $Policycommand.ExecuteReader()
	$Policytable = new-object "System.Data.DataTable"
	$Policytable.Load($Policyresult)
	$Policyconnection.Close()
        #return the table from the function
        $PolicyTable 
}

$policyTable = Get-ConnPolicy | Select UserName, Division, Supervisor

Get-ADUser -filter * -searchbase "MYSEARCHBASE" | 
Select Name, @{Name='Supervisor';E={$user = $_.SamAccountName; $policytable | where {$_.UserName -eq $user} | Select -expand Division}}
6

Thanks!