Data from 2 sources

This topic contains 5 replies, has 3 voices, and was last updated by Profile photo of Scott Windmiller Scott Windmiller 5 months ago.

  • Author
    Posts
  • #65103
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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

  • #65118
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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
  • #65131
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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}}

  • #65134
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    Got it! Thanks.

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

    Scott

  • #65146
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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}} 
    
  • #65151
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    Thanks!

You must be logged in to reply to this topic.