locked
Getting users that have a blank description attribute RRS feed

  • Question

  • Hello everyone, I'm trying to update the description attribute for all the user's on our domain. Using a do while loop I'm reading in each username and description from a spreadsheet. During the loop after I get the info about the user then I'm run a filter to get the user via their username. However, some user's already have a description. These were done manually in the past and I want to ignore them. I've tried to account for that in the line below however on the 5 accounts I'm testing against it's only altering the same user each time. It's ignoring the other 4 users. Only 1 of these user's already have a description and it's one of the 4 that's not being altered. Any ideas what I might be doing wrong?

    Get-ADUser -Filter {(userPrincipalName -Like $userPrincipalName) -and (description -notlike "*")} | Set-ADUser -Description $description

    Thursday, August 7, 2014 1:37 AM

Answers

  • It looks like you may only be returning one user.  I'd suspect $userPrincipalName, but can't say for sure without knowing what's in that variable.

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    • Marked as answer by ScriptingWife Tuesday, September 16, 2014 7:33 PM
    Thursday, August 7, 2014 2:07 AM
  • You are over qualifying the query and not checking for not found.

    if($user=Get-ADUser -Filter "userPrincipalName -eq '$userPrincipalName'" ){
        # found user
        $user | Set-AdUser -description $description
    }else{
       Write-Host "User not found - $userprincipalname" -fore red
    }

    UPN must be quoted and single quoted.  "like" is not useful here.

    No error is generated when using a filter so you need to check the results.


    ¯\_(ツ)_/¯

    • Proposed as answer by jrv Thursday, August 7, 2014 11:01 AM
    • Marked as answer by ScriptingWife Tuesday, September 16, 2014 7:33 PM
    Thursday, August 7, 2014 2:10 AM

All replies

  • It looks like you may only be returning one user.  I'd suspect $userPrincipalName, but can't say for sure without knowing what's in that variable.

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    • Marked as answer by ScriptingWife Tuesday, September 16, 2014 7:33 PM
    Thursday, August 7, 2014 2:07 AM
  • You are over qualifying the query and not checking for not found.

    if($user=Get-ADUser -Filter "userPrincipalName -eq '$userPrincipalName'" ){
        # found user
        $user | Set-AdUser -description $description
    }else{
       Write-Host "User not found - $userprincipalname" -fore red
    }

    UPN must be quoted and single quoted.  "like" is not useful here.

    No error is generated when using a filter so you need to check the results.


    ¯\_(ツ)_/¯

    • Proposed as answer by jrv Thursday, August 7, 2014 11:01 AM
    • Marked as answer by ScriptingWife Tuesday, September 16, 2014 7:33 PM
    Thursday, August 7, 2014 2:10 AM
  • You were both right. Once I created the if statement and generated my own error sure enough it couldn't find the user. So now to just figure out why it can't find those 4 users.
    Thursday, August 7, 2014 2:24 AM
  • Hi,

    Assuming your input CSV has headers of UserPrincipalName and Description, try this:

    Import-Csv .\userList.csv | ForEach {
    
        $upn = $_.UserPrincipalName
    
        $props = @{
            UPN = $upn
            Description = 'USER NOT FOUND'
        }
    
        $user = Get-ADUser -Filter "UserPrincipalName -eq '$upn'" -Properties Description
    
        If ($user) {
    
            $props.Description = 'Description already set'
    
            If (!($user.Description)) {
    
                Set-ADUser $user.SamAccountName -Description $_.Description -WhatIf
                $props.Description = "Description set to $($_.Description)"
    
            }
    
        }
    
        New-Object PsObject -Property $props
    
    } | Select UPN,Description | Sort UPN | Export-Csv .\userDescriptions.csv -NoTypeInformation

    Remove -WhatIf and rerun if you're happy with the output to actually make the changes.


    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Thursday, August 7, 2014 4:40 AM
  • Actually I'm using an actual Excel spreadsheet instead of a CSV file. Here's how I'm loading the users.

    # Import any modules that will be used later
    Import-Module ActiveDirectory
    
    # ----------------------------------------------------- 
        function Release-Ref ($ref)
        { 
            ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
             [System.__ComObject]$ref) -gt 0) 
             [System.GC]::Collect() 
             [System.GC]::WaitForPendingFinalizers() 
        } 
    # ----------------------------------------------------- 
    
    # Create and open an Excel object to work in the background
    $objExcel = new-object -comobject excel.application  
    $objExcel.Visible = $false 
    
    # This path will need to be changed in order to point to your spreadsheet.
    $objWorkbook = $objExcel.Workbooks.Open("C:\Users\tbhughes.DCSS\Desktop\Users.xlsx")
    
    <#
     #  In my case, I was given a spreadsheet with 2 worksheets. Worksheet 1 had users
     #  that were to go into OU A and Worksheet 2 users were to go into OU B. If you
     #  only have a signle worksheet then the Item number will stay 1, however, if you
     #  have multile worksheets then you will have to run this script for each worksheet
     #  and change the Item number to match whichever worksheet you wish to pull from next.
     #>
    $objWorksheet = $objWorkbook.Worksheets.Item(1) 
    
    # We start on Row 2, because Row 1 is our headings and we want to ignore them.
    $intRow = 2
    
    $num_of_users_altered = 0
    $num_of_users_not_altered = 0
     
    do
    {
        <#
         #  When reading the lines below keep in mind that when selecting a cell you will
         #  have to call it by row then column. For example: $objWorksheet.Cells.Item($intRow,1)
         #  where $intRow represents the current row and 1 represents the column where the data is
         #  located.
         #>
    
        # Get the user's login name
        $userPrincipalName = $objWorksheet.Cells.Item($intRow, 1).Value()
    
        # Get the user description
        $description = $objWorksheet.Cells.Item($intRow, 2).Value()
    
        $intRow++ 
    
        <#
         # Now that we have pulled all of the information for the current user from
         # the spreadsheet we will now check to see if this user has already been added
         # to Active Directory. If they are not in Active Directory then we will add them.
        #>
        
        $user = Get-ADUser -Filter {userPrincipalName -Like $userPrincipalName}
        
        if ($user)
        {
            $user | Set-ADUser -Description $description
            Write-Host "User" $userPrincipalName "was altered."
    
            $num_of_users_altered++
    
        }
        else
        {
            Write-Host "User" $userPrincipalName "not found"
            $num_of_users_not_altered++
        }
        
        
    } 
    
    <#
     #  This while loop allows us to loop through the spreadsheet so long as Cell 1 in our
     #  current row is not equal to null.
     #>
    
    while ($objWorksheet.Cells.Item($intRow, 1).Value() -ne $null) 
    
    # Close the spreadsheet as well as the background instand of Excel
    $objExcel.Quit() 
     
    $a = Release-Ref($objWorksheet) 
    $a = Release-Ref($objWorkbook) 
    $a = Release-Ref($objExcel)
    
    Write-Host $num_of_users_altered "users were successfully altered." 
    Write-Host $num_of_users_not_altered "users were not altered."
    
    My spreadsheet does have headers, which is why $intRow starts at 2.
    Thursday, August 7, 2014 11:32 AM
  • Okay, then take the logic from my script and plug it into yours.

    This is why I always just save any input Excel files as CSV. So much easier to work with (IMHO).


    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Thursday, August 7, 2014 12:57 PM