Forum Discussion

dding0919's avatar
dding0919
Copper Contributor
Nov 21, 2024

Not able to export AD attribute departmentnumber

Hi,

I'm trying to generate a report for all AD users with some AD attributes, such as SamAcconutName, department, departmentnumber, etc.  The report is fine with all except departmentnumber.  it shows "Microsoft.ActiveDirectory.Management.ADPropertyValueCollection" instead of actual number.  Here is my PS cmdlet.

get-aduser  -Properties * | select displayname, departmentnumber | Export-Csv c:\temp\users.csv -NoTypeInformation

When tested against a single AD account using the same cmdlet, the screen output shows the value with { }, for example, {123456} instead of 123456.  employeenumber attribute worked fine.  The same cmdlet shows just the number.

Please help,  How can I query and export attribute departmentnumber in a report properly?

 

Thanks,

Dave

Windows Server
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Nov 22, 2024

    Hi dding0919 ,

     

    This is expected for any multivalued attribute.

     

    What you can do is leverage the functionality within Select-Object for creating a custom property on-the-fly to action a join on the multivalued departmentNumber attribute, as demonstrated below.

     

    Command

    Get-ADObject -Filter { (userPrincipalName -eq "email address removed for privacy reasons") } -Properties displayName, departmentNumber | Select-Object -Property displayName, @{ n="departmentNumber"; e = { $_.departmentNumber -join ","; } };

     

    Output

     

    If you were pulling numerous multivalued attributes, I'd likely switch strategies to using a ForEach-Object construct in lieu of Select-Object as it's more readable, but for a single multivalued attribute, there's no need.

     

    Cheers,
    Lain

    • dding0919's avatar
      dding0919
      Copper Contributor
      Nov 22, 2024

      Hi Lain,

        Thanks for the answer.  The cmdlet worked for me too.  But I need additional advice.  I have following script to generate a report for mailbox size along with some AD attributes.  I have to combine the date from different data source.  For department number how can I specify the value format?

      $mailboxes = Get-Mailbox -RecipientTypeDetails usermailbox, roommailbox, sharedmailbox | ForEach-Object {
          $mailboxStats = Get-MailboxStatistics $_.SamAccountName
          $adUser = Get-ADUser -Identity $_.SamAccountName -Properties *
          [PSCustomObject]@{
              'Mailbox' = $_.DisplayName
              'Email Address' = $_.PrimarySMTPAddress
              'MailboxSize (GB)' = [math]::Round($mailboxStats.TotalItemSize.Value.ToGB(), 3)
              'Department' = $adUser.Department
              'Department Number' = $adUser.departmentNumber
              'Employee Number' = $adUser.EmployeeNumber
              'Enabled' = $adUser.Enabled
              'LastLogonDate' = $adUser.LastLogonDate
          }
      } | Export-Csv -Path "C:\temp\mailbox_report.csv" -NoTypeInformation

       

      really appreciate!

      Dave

      • LainRobertson's avatar
        LainRobertson
        Silver Contributor
        Nov 23, 2024

        Hi dding0919 ,

         

        Change this line:

         

        'Department Number' = $adUser.departmentNumber

         

        To this (using whatever separator you like - I've simply defaulted to a comma, which will still work just fine with Export-Csv):

         

        'Department Number' = $adUser.departmentNumber -join ","

         

        The above option is the preferred option as it will gracefully handle all values contained within departmentNumber, however, If you're simply - and unconditionally - after only the first value, you can use this alternative to the above suggestion.

         

          'Department Number' = if ($adUser.departmentNumber.Count -gt 0) { $adUser.departmentNumber[0] }

         

        Cheers,
        Lain

Resources