Export generic fields from AD to CSV

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 7 months ago.

  • Author
  • #6180

    by farquaad at 2012-09-19 07:11:58


    In the following, is it possible to make the section marked in red more generic so that I only have to type in fieldnames in the $objProperties array?

    $objProperties = @("cn", "givenName", "sn", "mail", "mobile", "telephoneNumber", "title")
    $objOU = "LDAP://OU=Users,DC=Company,DC=com"

    $objDomain = New-Object System.DirectoryServices.DirectoryEntry($objOU)
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    $objSearcher.SearchRoot = $objDomain
    $objSearcher.SearchScope = "OneLevel"

    $Result = $objSearcher.FindAll() | foreach{
    $ResultProperties = $_.Properties

    $cn = @{ name="cn";expression={$ResultProperties["cn"]} }
    $givenName = @{ name="givenName";expression={$ResultProperties["givenName"]} }
    $sn = @{ name="sn";expression={$ResultProperties["sn"]} }
    $mail = @{ name="mail";expression={$ResultProperties["mail"]} }
    $mobile = @{ name="mobile";expression={$ResultProperties["mobile"]} }
    $telephoneNumber = @{ name="telephoneNumber";expression={$ResultProperties["telephoneNumber"]} }
    $title = @{ name="title";expression={$ResultProperties["title"]} }

    $ResultProperties | select $cn,$givenName,$sn,$mail,$mobile,$telephoneNumber,$title
    } | Export-Csv C:\tmp\ADUsers.csv -NoTypeInformation -Delimiter ";" -Encoding Unicode

    by DonJ at 2012-09-19 07:37:54

    I can think of DIFFERENT ways to do that, but not necessarily more convenient or concise. The problem stems from the fact that you're relying on ADSI, which is a relatively low-level, COM-based interface. You'd find it more convenient if you were using something more PowerShell native, like the ActiveDirectory module or the quest AD cmdlets. Either of those would expose the properties directly, without requiring you to access them from an array.

    by poshoholic at 2012-09-19 07:59:40

    I can't test this right now as I am not domain joined, but something like this should make it easier for you to modify the list of properties you want to retrieve (note: I removed the "obj" prefixes because in PowerShell, everything is an object so that prefix is redundant):
    $properties = @("cn", "givenName", "sn", "mail", "mobile", "telephoneNumber", "title")
    $OU = "LDAP://OU=Users,DC=Company,DC=com"

    $domain = New-Object System.DirectoryServices.DirectoryEntry($OU)
    $searcher = New-Object System.DirectoryServices.DirectorySearcher
    $searcher.SearchRoot = $domain
    $searcher.SearchScope = "OneLevel"

    # This line is the key, creating the hash tables that identify how you want the properties to be generated for the selected object
    $selectedProperties = $properties | ForEach-Object {@{name="$_";expression=$ExecutionContext.InvokeCommand.NewScriptBlock("`$_['$_']")}}

    if ($results = $searcher.FindAll()) {
    $results.Properties | Select-Object -Property $selectedProperties | Export-Csv C:\tmp\ADUsers.csv -NoTypeInformation -Delimiter ';' -Encoding Unicode

    Also, just to call it out again, this is untested script. I just put this together thinking it might help you with your goal of having a single list of properties that you want to manage in the script (which could then be made a parameter if you converted it into a function). You'll have to test it out in your environment to see if it works.

    by farquaad at 2012-09-20 02:59:46

    I appretiate the replies, muchos gratias

    [quote="DonJ"]something more PowerShell native[/quote]

    The script will be deployed in customer domains and sync user information into our CMDB. I'd rather not have to install Quest components or anything else on the machine in their domain that will run the script on a regular basis. I tried the Get-ADUser cmdlet, but that includes about 10 default lines pr. object (which I couldnt' seem to exclude), and with customer domains with 8000-10000 users that's alot of unnecessary data. Thought I'd see if I could optimize it a bit.

    @poshoholic : The looks like what I'm aiming for, doesn't work but I'll have to play around with it a bit more 🙂

    by Steve at 2012-10-02 17:42:25

    An option to clean up the results from get-aduser....

    get-aduser -Identity UserName -Properties cn, givenname, sn, mail, mobile, telephonenumber, title | Select-Object cn, givenname, sn, mail, mobile, telephonenumber, title

    That seems to return just the properties you want.

You must be logged in to reply to this topic.