need help to list users with second owner in a csv file

Hello.

My Problem is, i have to list all Users in a specified OU, with entries in the Second Owner field.

How i can do this and export in a CSV File?

Would be great if someone could help.

Thanks and best regard

Eddi

Parents
  • If you show us your script, we can suggest how to update it to get by the issue.  Mostly likely you will need to do an '-ExpandProperty' on edsvaSecondaryOwners but we need to see your existing code in order to suggest the appropriate modifications.

  • Hi. 

    Sorry, here is my script. 

    Connect-QADService -Proxy -Credential (Get-Credential)
    Get-QADUser -Sizelimit 0 -searchroot 'OU=example_Accounts_Users_external,OU=Accounts_Users_external,OU=external,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com' -IncludedProperties edsvaSecondaryOwners | Select-Object -Property Name,edsvaSecondaryOwners | Export-CSV -Path c:\temp\UserExport2.csv

    This is my output:

    #TYPE Selected.ActiveRoles.ManagementShell.Data.ArsUserObject
    Name,"edsvasecondaryowners"
    EXT Lukas,"CN=Matthias,OU=Accounts_Users_internal,OU=Accounts_Users_internal,OU=internal,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Leonhard,"CN=Matthias,OU=Accounts_Users_internal,OU=Accounts_Users_internal,OU=internal,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Richard,"CN=Matthias,OU=Accounts_Users_internal,OU=Accounts_Users_internal,OU=internal,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Klaus,"CN=Matthias,OU=Accounts_Users_internal,OU=Accounts_Users_internal,OU=internal,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Marian,"CN=Florian - Depro 2020-01-22,OU=Accounts_Disabled,OU=_Disabled,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Dmytro,"CN=Florian - Depro 2020-01-22,OU=Accounts_Disabled,OU=_Disabled,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Robert,"System.Object[]"
    EXT Scrum,"System.Object[]"
    EXT BASE,"CN=Juergen,OU=Accounts_Users_internal,OU=Accounts_Users_internal,OU=internal,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com"
    EXT Eva,"System.Object[]"
    EXT Gabriel,"System.Object[]"
    EXT Johannes,"System.Object[]"

    Thanks and BR

    Eduard

  • Quick fixes to your select and export CSV:

    Select-Object -Property Name,@{Name="edsvaSecondaryOwners";Expression={[string]::join(";",($_.edsvaSecondaryOwners))}} | Export-CSV -Path c:\temp\UserExport2.csv -NoTypeInformation

    NOTES

    1.  The Name / Expression allows you to take a returned property and do some transformation on it (described below)

    2.  The string/join takes the ...SecondaryOwners list and converts it to a string delimited by semi-colons.

    Reference

    3.  The "-notypeinformation" gets rid of the message at the top of your output file.

  • Had to edit the above...please re-check online version

  • Hi Johnny

    Thanks for your help! 

    It works for me Smiley  

    Thanks a lot, you saved my day. 

    Cheers Eduard

  • Hi Johnny.

    Short question. 

    What i have to change, when i would get the SamAccountName, Name and DistinguishedName from the Secondary Owners and removing the User OU Paths.

    EXT Dmytro,"CN=Florian - Depro 2020-01-22,OU=Accounts_Disabled,OU=_Disabled,OU=Accounts,DC=corp,DC=example,DC=com"   <-should removed.

    Thank You and cheers 

    Eduard

Reply
  • Hi Johnny.

    Short question. 

    What i have to change, when i would get the SamAccountName, Name and DistinguishedName from the Secondary Owners and removing the User OU Paths.

    EXT Dmytro,"CN=Florian - Depro 2020-01-22,OU=Accounts_Disabled,OU=_Disabled,OU=Accounts,DC=corp,DC=example,DC=com"   <-should removed.

    Thank You and cheers 

    Eduard

Children
  • I'm not sure I follow 100% - for each Secondary Owner, you would like to show the samaccountname,Name and Distinguished name.  What is confusing me though is that you want to remove the User OU path - this is part of the distinguished name so do you only want the "CN=" part of the distinguishedname shown for SecondaryOwners?

    Something like this?




  • Hi Johnny.

    Thats it!! Exactly like that!! 

    How did you do that? 

    I try some changes, but nothing helps. Would be nice, if you can help me to get the same results.

    Thanks and BR 

    Eduard

  • The above is just an example I put together in Excel.

    The value you want to show as 'SecondaryOwnerName' - what Active Directory user name property(ies) you really want to show?  (See below)

    My point is that 'Name' and 'CN" are always the same value because Active Directory constructs the distinguished name from the combination of the 'Name' and the OU where the object lives.  I showed you the displayname below because as you can see from my example, sometimes its format is different (click the image to make it bigger).

  • Hi Johnny.

    Yes, i know what you mean. 

    My Teamleader, want's to see exactly as you show in your post before.

    He want to see, the User and than the SecondaryOwnerNameSamAccount, SecondaryOwner and SecondaryOwnerDistinguishedName in separeted Row, for all Users. 

    I have tried to get the same results as you have, but i'm not a ps pro.

    For example: (this is handmade in Excel)

    Name SecondaryOwnerSamAccountName SecondaryOwnerName SecondaryOwnerDistinguishedName (CN)
    EXT Cabe Robert Ejanda Eduard Janda CN=Eduard Janda
    EXT Hans Muster Ejanda, Cmuster Eduard Janda, Claudia Muster CN=Eduard Janda, CN=Claudia Muster

    That's what i need.

    The User and their SecOwners. In my example, one User has one SecOwner.

    The other User, has more SecOwners (2 or 3 SecOwners). How i can do that? 

    In your post above, you have the perfect results. Slight smile

    Thanks and Cheers 

    Eduard

  • Here's what I think should work...

    Get-QADUser -Sizelimit 0 -searchroot 'OU=example_Accounts_Users_external,OU=Accounts_Users_external,OU=external,OU=Users,OU=Accounts,DC=corp,DC=example,DC=com' -IncludedProperties edsvaSecondaryOwners | foreach {

    # Store the user we are working on

    $CurrentADUserDetails = $_

    # Process the secondary owners and build the additional output fields

    $CurrentSecondaryOwners = $CurrentADUserDetails.edsvaSecondaryOwners

    $CurrentSecondaryOwners | foreach

    {

    $CurrentSecondaryOwnersItem = $_

    # Get the samaccountname of the current Secondary Owner from the list of SecondaryOwners

    $CurrentSecondaryOwnerSam = $(Get-QADUser -Identity $CurrentSecondaryOwnersItem).samaccountame # Results in 'JSmith'

    $SecondaryOwnerSamList += "$CurrentSecondaryOwnerSam;" # Results in 'JSmith;FJones;'

    # Format the CN of the Secondary Owner

    $SecondaryOwnerCN = $_.split(",")[0] # Results in 'CN=John Smith'

    $SecondaryOwnerCNList += "$SecondaryOwnerCN;" # Results in 'CN=John Smith;CN=Fred Jones;'

    # Strip 'CN=' off the CN to get the 'Name'

    $SecondaryOwnerName = $SecondaryOwnerCN.replace("CN=","") # Results in 'John Smith'

    $SecondaryOwnerNameList += "$SecondaryOwnerName;" # Results in 'John Smith;Fred Jones;'

    } # End of secondary owners processing

    # Export the output

    $CurrentADUserDetails | select Name,@{Name="SecondaryOwnerSamccountName";Expression={$SecondaryOwnerSamList}},`
    # Each item below builds a field name and its contents for the file
    @{Name="SecondaryOwnerSamccountName";Expression={$SecondaryOwnerSamList}},`
    @{Name="SecondaryOwnerName";Expression={$SecondaryOwnerNameList}},`
    @{Name="SecondaryOwnerDistinguishedNameCN";Expression={$SecondaryOwnerCNList}} | Export-CSV -Path c:\temp\UserExport2.csv -NoTypeInformation -Append

    } # End of outer Users loop

  • Small change to the very end of script (adding the 3 lines below):

    $SecondaryOwnerSamList = $null
    $SecondaryOwnerNameList = $null
    $SecondaryOwnerCNList = $null

    } # End of outer Users loop

  • Hi Johnny.

    I think it would work.

    But i get those Errors:

    CN=Active Directory ARS
    cmdlet ForEach-Object at command pipeline position 1
    Supply values for the following parameters:
    Process[0]:


    $CurrentSecondaryOwnersItem = $_

    # Get the samaccountname of the current Secondary Owner from the list of SecondaryOwners

    $CurrentSecondaryOwnerSam = $(Get-QADUser -Identity $CurrentSecondaryOwnersItem).samaccountame # Results in 'JSmith'

    $SecondaryOwnerSamList += "$CurrentSecondaryOwnerSam;" # Results in 'JSmith;FJones;'

    # Format the CN of the Secondary Owner

    $SecondaryOwnerCN = $_.split(",")[0] # Results in 'CN=John Smith'

    $SecondaryOwnerCNList += "$SecondaryOwnerCN;" # Results in 'CN=John Smith;CN=Fred Jones;'

    # Strip 'CN=' off the CN to get the 'Name'

    $SecondaryOwnerName = $SecondaryOwnerCN.replace("CN=","") # Results in 'John Smith'

    $SecondaryOwnerNameList += "$SecondaryOwnerName;" # Results in 'John Smith;Fred Jones;'


    select : The property cannot be processed because the property "SecondaryOwnerSamccountName" already exists.
    At C:\Users\jandae\Documents\Test.ps1:40 char:25
    + ... erDetails | select Name,@{Name="SecondaryOwnerSamccountName";Expressi ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (CORPORATE\ext-landrolu:PSObject) [Select-Object], PSArgumentException
    + FullyQualifiedErrorId : AlreadyExistingUserSpecifiedPropertyNoExpand,Microsoft.PowerShell.Commands.SelectObjectCommand

    cmdlet ForEach-Object at command pipeline position 1
    Supply values for the following parameters:
    Process[0]:

    How i can fix this?

    And second question, PS ISE say's Runtime Error :

    $CurrentADUserDetails | select Name,@{Name (at end of script)

    and

    $CurrentSecondaryOwners | foreach (at beginning from script)

    And next following problem is, in the CSV file only the Users are in. No Secondary Owners.? 

    If i run the Script a second time, the same Users are douple into the CSV file.

    Thank you very much for your great help Slight smile

    Cheers Eduard

  • The fix for the first error is to remove the line I have highlighted below:

  • Two other small fixes:

    The '{' after the 'foreach' must be "pulled up" from the line below.

    Samaccountname is spelled wrong.

  • Good Morning Johnny.

    Thanks for your help. I have done your changes. 

    But now, i got two other issues. 

    The first one:

    Get-QADUser : Cannot validate argument on parameter 'Identity'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.
    At C:\Users\jandae\Documents\Test.ps1:18 char:57
    + ... wnerSam = $(Get-QADUser -Identity $CurrentSecondaryOwnersItem).Samacc ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [Get-QADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,ActiveRoles.ManagementShell.Powershell.Cmdlets.GetUserCmdlet

     

    The second one:

    You cannot call a method on a null-valued expression.
    At C:\Users\jandae\Documents\Test.ps1:24 char:5
    + $SecondaryOwnerCN = $_.split(",")[0] # Results in 'CN=John Smith'
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

     

    And this is the output in CSV file.

    Name,"SecondaryOwnerSamccountName","SecondaryOwnerName","SecondaryOwnerDistinguishedNameCN"
    EXT Landrock Lukas,"","",""
    EXT Landrock Leonhard,"","",""
    EXT Landrock Richard,"","",""

     

     Other question: 

    If i remove the =$null from your Code, than i get an CSV File larger than 1400KB, can' open the CSV file.

    If i do not remove =$null, than the CSV File is 2KB small.

    $CurrentADUserDetails | Select-Object Name,@{Name="SecondaryOwnerSamAccountName";Expression={$SecondaryOwnerSamList=$null}},`
    # Each item below builds a field name and its contents for the file
    @{Name="SecondaryOwnerName";Expression={$SecondaryOwnerNameList=$null}},`
    @{Name="SecondaryOwnerDistinguishedNameCN";Expression={$SecondaryOwnerCNList=$null}} | Export-CSV -Path c:\temp\UserExportTest.csv -NoTypeInformation -Append

    Can you help me again!

    Thanks and BR

    Eduard