locked
Split Members to different columns RRS feed

  • Question

  • Hello, 

    I am trying to pull the list of Members for a ADObject Named Contacts , when i am trying to pull the contacts it is showing up in the single column

    I need to split the members alone to different columns and also want to get the managers of the DL . 

    I am using two different scripts to get the information using the following and need to make them into one script to split the members of the DL in to same sheet and members 

    
    
    Get-ADobject -LDAPfilter "objectClass=contact" -Properties objectClass,givenName,cn,displayName,Name,mail,mailNickName,sn,distinguishedName,whenCreated,whenChanged,manager,managedby,memberOf,msExchCoManagedByLink | 
    Select-Object name,objectClass,givenName,cn,displayName,mail,mailNickName,sn,distinguishedName,whenCreated,whenChanged,manager,
    managedby,@{n='memberOf';e={(($_.memberOf).split(",") |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},@{Name="msExchCoManagedByLink";Expression ={(($_.msExchCoManagedByLink).split(",")|
     where-object {$_.contains("CN=")}).replace("CN=""")-join ','}}|Export-Csv C:\Temp\contact.csv -Append -NoTypeInformation

    $csv=Import-Csv C:\Temp\contactsdl.csv
    foreach($users in $csv)
    {
    Get-ADGroup -Identity $users.memberOf -Properties displayname,enabled,GroupCategory,mail,description,info,manager,managedby,members,msExchCoManagedByLink,distinguishedName,kPMG-User-GOAccountType | 
    Select-Object -Property sAMAccountName,displayname,enabled,distinguishedName,kPMG-User-GOAccountType,GroupCategory,mail,description,info,manager,
    managedby,@{Name="Members"; Expression ={(($_.Members).split(",") | where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},@{Name="msExchCoManagedByLink";Expression ={(($_.msExchCoManagedByLink).split(",")| where-object {$_.contains("CN=")}).replace("CN=","")-join ','[5]}}|Export-Csv C:\Temp\contactsdlinfo1.csv -Append -NoTypeInformation
    }
    
    
    


    Sunday, June 14, 2020 8:00 AM

All replies

  • Are you asking for us to rewrite this code that you have found on the Internet?

    Are you asking how to "split" an array?

    You have to ask a more complete question.  Also not that we do not rewrite scripts.  This forum is for scripters to ask questions about scripting.


    \_(ツ)_/

    Sunday, June 14, 2020 5:00 PM
  • Hi 

    I have written the script and used it multiple times, Now i am trying to get the ADObjects like contacts and trying to get the members and need to split the members in to different columns in CSV file. 

    I have got the output like the following 

    sAMAccountName  Members 

    Xyz                         DL-xyx,DL-xyz,DL-rZy 

    and i am looking to get this to split in to different columns and i have tried using the array functionality to get this split in to different columns but still i not able to get and i am trying to split this up into the following 

    sAMAccountName Members1 Members2 Members3 

    Xyz 

    Get-ADobject -LDAPfilter "objectClass=contact" -Properties objectClass,givenName,cn,displayName,Name,mail,mailNickName,sn,distinguishedName,whenCreated,whenChanged,manager,managedby,memberOf,msExchCoManagedByLink | 
    Select-Object name,objectClass,givenName,cn,displayName,mail,mailNickName,sn,distinguishedName,whenCreated,whenChanged,manager,
    managedby,@{n='memberOf';e={(($_.memberOf).Split(' ')[0] |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},
    @{n='memberOf';e={(($_.memberOf).Split(' ')[1] |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},
    @{n='memberOf';e={(($_.memberOf).Split(' ')[2] |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},
    @{n='memberOf';e={(($_.memberOf).Split(' ')[3] |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},
    @{n='memberOf';e={(($_.memberOf).Split(' ')[4] |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}},
    @{n='memberOf';e={(($_.memberOf).Split(' ')[5] |where-object {$_.contains("CN=")}).replace("CN=","")-join ','}}
    ,@{Name="msExchCoManagedByLink";Expression ={(($_.msExchCoManagedByLink).split(",")|
     where-object {$_.contains("CN=")}).replace("CN=""")-join ','}}|Export-Csv C:\xy\Cons.csv -Append -NoTypeInformation

    Sunday, June 14, 2020 7:05 PM
  • You cannot have multiple columns with the same name in a CSV.

    First learn what a CSV is: https://en.wikipedia.org/wiki/Comma-separated_values

    You never need to do a split like you are.  It doesn't make much sense.

    To split an array into multiple variables we can do this:

    $v1,$v2,$v3,$restofarray = $_.MemberOf

    "$_.memberOf" is an array and not a string.

    You cannot split an array and assign it to a CSV field.  CSVs cannot store arrays.

    Start by inspecting the properties you want to work with until you understand what they are. Learn about PowerSHell and variables as well as how to work with "types" in PowerShell.

    Start with this and try to learn enough PowerShell to understand what it is doing.  I do not know if it is what you are asking but it may help you to learn enough to ask an understandable question.

    $properties = 'objectClass','givenName','cn','displayName','Name','mail','mailNickName','sn','distinguishedName','whenCreated','whenChanged','manager','managedby','memberOf','msExchCoManagedByLink'
    $computed = @(
        'managedby','objectClass','givenName',
        'cn','displayName','Name','mail',
        'mailNickName','sn','distinguishedName',
        'whenCreated','whenChanged','manager',
        @{n='memberOf';e={$_.memberOf[0]}},
        @{n='memberOf';e={$_.memberOf[1]}},
        @{n='memberOf';e={$_.memberOf[2]}},
        @{n='memberOf';e={$_.memberOf[3]}},
        @{n='memberOf';e={$_.memberOf[4]}},
        @{n='memberOf';e={$_.memberOf[5]}},
        @{n="msExchCoManagedByLink";e ={$_.msExchCoManagedByLink[0]}}
    )
    Get-ADobject -Filter "objectClass -eq 'contact'" -Properties $properties | 
        Select-Object $computed |
        Export-Csv C:\xy\Cons.csv -NoTypeInformation
    

    Most of your problems come from copying, pasting and editing code that you do not understand.  You must understand code before you try to use it.  What if someone hides a tricky bit of malware in code you have copied.  You would never be able to see it.

    The code I posted should break things down enough so you can begin to see how PowerShell works with code and with data types and structures.


    \_(ツ)_/

    Sunday, June 14, 2020 8:19 PM
  • Hi,
    Was your issue resolved? 
    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
    If no, please reply and tell us the current situation in order to provide further help.
    Best Regards,
    Yang Yang

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, July 3, 2020 5:27 AM