none
Getting output from a custom psobject

    Question

  • Hi,

    I am running the script below retrieving data from an Oracle SQL db. I am intending on putting the values into AD attributes for users. However, I cannot figure out how to read/get the variables for the set-aduser command.

    I thought I could go through using foreach, or foreach-object $result.username for example, but it is asking me for the process[0] which I understand it is looking for which object I want in the array...

    I would really appreciate some help, I have been trying various things for hours, none of which is working and I have a deadline of course.

    Thanks for any help!

    $result = @{}
    
    $result = $SelectDataTable | foreach {
        $row = $_;  
        new-object psObject -Property @{ 
            UserName = $_.GetString(2)
            Title = $_.GetString(3)
            Department = $_.GetString(4)
        }
    }

    Wednesday, April 17, 2019 8:44 PM

Answers

  • If you intend to send the objects you're creating through the pipeline to the Set-AdUser cmdlet you'll need a property named "Identity", not "UserName".

    Also, you want "$result" to be an array, not a hashtable.

    See if this works any better:

    $result = @()
    
    $SelectDataTable | foreach {
        $result += new-object psObject -Property @{ 
            Identity = $_.GetString(2)
            Title = $_.GetString(3)
            Department = $_.GetString(4)
        }
    }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Wednesday, April 17, 2019 9:06 PM
  • Hi,

    Thanks for your question.

    $selectdatatable | foreach { Set-ADUser -Identity $_.getstring(2) -Title $_.getstring(3) -Department $_.getstring(4) }
    

    Best regards,

    Lee


    Just do it.

    • Marked as answer by bvi1998 Tuesday, April 30, 2019 12:47 PM
    Thursday, April 18, 2019 3:22 AM
    Moderator
  • Hope this helps someone, this is what works for me, reading from an Oracle db:

    while ($SelectDataTable.Read()) {
    
        if ($SelectDataTable.IsDBNUll(2)) {
            $Identity = "none"
        }
    
        else {
            $Identity = $SelectDataTable.GetString(2)
        }
    
        if ($SelectDataTable.IsDBNUll(3)) {
            $Title = "none"
        }
     
        else {
            $Title = $SelectDataTable.GetString(3)
        }
        if ($SelectDataTable.IsDBNUll(4)) {
            $Department = "none"
        }
        
        else {
            $Department = $SelectDataTable.GetString(4)
        }
           
        if ($SelectDataTable.IsDBNUll(5)) {
            $MSC = "none"
        }
    
        else {
            $MSC = $SelectDataTable.GetString(5)
        }
    
        if ($SelectDataTable.IsDBNUll(6)) {
            $Location = "none"
        }
        else {
            $Location = $SelectDataTable.GetString(6)
        }
    
        if ($SelectDataTable.IsDBNUll(7)) {
            $Phone = "none"
        }
    
        else {
            $Phone = $SelectDataTable.GetString(7)
        }
        
        if ($SelectDataTable.IsDBNUll(10)) {
            $Manager = "none"
        }
        else {
            $Manager = $SelectDataTable.GetString(10)
        }
    

    • Marked as answer by bvi1998 Tuesday, April 30, 2019 1:00 PM
    Tuesday, April 30, 2019 1:00 PM

All replies

  • There are several examples how to change or update AD attributes in loops here or in Powershell.org or in the PowershellGallery or Microsoft Technet Script Gallery.

    Instead of creating new objects in your loop you can use Set-ADUser and use the properties you use at the moment to create object to update the according AD accounts.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, April 17, 2019 8:53 PM
  • If you intend to send the objects you're creating through the pipeline to the Set-AdUser cmdlet you'll need a property named "Identity", not "UserName".

    Also, you want "$result" to be an array, not a hashtable.

    See if this works any better:

    $result = @()
    
    $SelectDataTable | foreach {
        $result += new-object psObject -Property @{ 
            Identity = $_.GetString(2)
            Title = $_.GetString(3)
            Department = $_.GetString(4)
        }
    }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Wednesday, April 17, 2019 9:06 PM
  • Hi,

    Thanks for your question.

    $selectdatatable | foreach { Set-ADUser -Identity $_.getstring(2) -Title $_.getstring(3) -Department $_.getstring(4) }
    

    Best regards,

    Lee


    Just do it.

    • Marked as answer by bvi1998 Tuesday, April 30, 2019 12:47 PM
    Thursday, April 18, 2019 3:22 AM
    Moderator
  • Thanks Rich.

    Now, at least I have columns which clearly show the data - the content of $results.

    Title                     Department                               Identity       
    -----                    ----------                                --------       
    Professor               Education, Democracy & Social Change       joe         
    Associate Professor     Arts & Technologies                        john           
    Tutor Coordinator          Academic Support Services                  kim     
    Professor               Exploration & Discovery                    jax

    However, when trying to write out to the host the identity column, it is blank. I am still unclear as to what the variable name would be. I have tried $identity, and $results.identity. This is where my confusion is, how the data is actually stored, to be accessed for the next step.

    Thanks for your help!

    Thursday, April 18, 2019 1:50 PM
  • In a dataset the result fields can be accessed like this:

    $SelectDataTable  | Select Identity, Title, Department

    Assuming those are the field names in the database.

    "GetString" can also use a field name.  "GetString('Title')" to select a field.


    \_(ツ)_/


    Thursday, April 18, 2019 1:54 PM
    Moderator
  • Thanks Lee, I think this will do it. Simple. :)

    Thanks!

    Thursday, April 18, 2019 1:56 PM
  • Assuming you'll continue using a custom object (I don't know what you're going to do with the information other than what you've posted in you example), you'd do something like this:

    $results | foreach {
      Write-Host $_.Identity
      Write-Host $_.Title
      Write-Host $_.Dept
    }

    OTOH, using the information directly from the $SelectDataTable variable as the values used by your Set-AdUser cmdlet (as demonstrated by others) make more sense unless you plan on further modifications to the custom object by, say, adding additional information gathered from other sources.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, April 18, 2019 2:32 PM
  • The most successful command for me is:

    $selectdatatable | foreach { Set-ADUser -Identity $_.getstring(2) -Title $_.getstring(3) -Department $_.getstring(4) }

    However, the Title and other fields have spaces in them so the set-aduser will not accept it. I have tried single and double quotes, and a ` which I thought might work.

    So far I have not had much success getting it to accept spaces.

    Thanks for any help!

    Thursday, April 18, 2019 4:10 PM
  • Try "$($_.getstring(2))".

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, April 18, 2019 5:52 PM
  • Try "$($_.getstring(2))".

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    ?????

    Think about what "GetString()" does.


    \_(ツ)_/

    Thursday, April 18, 2019 6:30 PM
    Moderator
  • The most successful command for me is:

    $selectdatatable | foreach { Set-ADUser -Identity $_.getstring(2) -Title $_.getstring(3) -Department $_.getstring(4) }

    However, the Title and other fields have spaces in them so the set-aduser will not accept it. I have tried single and double quotes, and a ` which I thought might work.

    So far I have not had much success getting it to accept spaces.

    Thanks for any help!

    Spaces in a variable are never an issue when passing as an argument.  The system manages that.

    \_(ツ)_/

    Thursday, April 18, 2019 6:33 PM
    Moderator
  • $result = $SelectDataTable | foreach-object {
      [pscustomobject]@{
            Identity = $_.GetString(2)
            Title = $_.GetString(3)
            Department = $_.GetString(4)
            MSC = $_.GetString(5)
            Office_Location = $_.GetString(6)
            Office_Phone = $_.GetString(7)
            Supr_username = $_.GetString(10)
        
      }
    }
    
    $result2 = $result | select -last 2
    
    $result2 | foreach-object {
            $ID = $_.identity
            $usertitle = $_.title
            $dept = $_.department
            $mailstop = $_.msc
            $location = $_.Office_Location
            $phone = $_.Office_Phone
            $manager = $_.Supr_username
    
    set-aduser -Identity $id -Title $usertitle -Department $dept -office $location -streetaddress
    My last issue is combining some of the variables to create the streetaddress which I want to be on separate lines. Could be -streetaddress = $location $mailstop  (etc)... I am working on this, I will share if I find and answer lol ... thanks Everyone!

    Thursday, April 18, 2019 7:20 PM
  • I combined all of the variables like this, surrounded by quotes and it worked:

    -streetaddress "$all`r`n$mailstop`r`n$location`r`n$dept`r`n$comp"

    Thursday, April 18, 2019 8:04 PM
  • DOH!

    Do I have to think about it? It's embarrassing. :-(


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, April 18, 2019 9:12 PM
  • Hope this helps someone, this is what works for me, reading from an Oracle db:

    while ($SelectDataTable.Read()) {
    
        if ($SelectDataTable.IsDBNUll(2)) {
            $Identity = "none"
        }
    
        else {
            $Identity = $SelectDataTable.GetString(2)
        }
    
        if ($SelectDataTable.IsDBNUll(3)) {
            $Title = "none"
        }
     
        else {
            $Title = $SelectDataTable.GetString(3)
        }
        if ($SelectDataTable.IsDBNUll(4)) {
            $Department = "none"
        }
        
        else {
            $Department = $SelectDataTable.GetString(4)
        }
           
        if ($SelectDataTable.IsDBNUll(5)) {
            $MSC = "none"
        }
    
        else {
            $MSC = $SelectDataTable.GetString(5)
        }
    
        if ($SelectDataTable.IsDBNUll(6)) {
            $Location = "none"
        }
        else {
            $Location = $SelectDataTable.GetString(6)
        }
    
        if ($SelectDataTable.IsDBNUll(7)) {
            $Phone = "none"
        }
    
        else {
            $Phone = $SelectDataTable.GetString(7)
        }
        
        if ($SelectDataTable.IsDBNUll(10)) {
            $Manager = "none"
        }
        else {
            $Manager = $SelectDataTable.GetString(10)
        }
    

    • Marked as answer by bvi1998 Tuesday, April 30, 2019 1:00 PM
    Tuesday, April 30, 2019 1:00 PM