Export LDAP/GC query to CSV

Tagged: , , , , ,

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Peter Jurgens Peter Jurgens 3 years ago.

  • Author
    Posts
  • #15664
    Profile photo of deiandrei
    deiandrei
    Participant

    Hi guys,

    I can't figure out how to export the results of a global catalog query to csv.
    This is my code:

    $root = [ADSI]"GC://DC=domain,DC=forest,DC=com"
    $search = [ADSISearcher]$root
    $search.Filter = "(&(objectclass=computer))"
    $computers = $search.FindAll()
    
    foreach ($computer in $computers)
    {
    $PC = $computer.getdirectoryentry()
    $PC.name
    $PC.distinguishedname
    }
    

    But this generates the name and below the distinguishedname, but no table or list.

    Is there a way to export this to csv?

  • #15667
    Profile photo of Joakim
    Joakim
    Participant

    Here's a version of the same thing using custom objects:

    $root = [ADSI]"GC://DC=domain,DC=forest,DC=com"
    $search = [ADSISearcher]$root
    $search.Filter = "(&(objectclass=computer))"
    $computers = $search.FindAll()
    $output = @()
    foreach ($computer in $computers)
    {
    $PC = $computer.getdirectoryentry()
    $props =@{'name'=$PC.name.value;'distinguishedname'=$PC.distinguishedname.value}
    $obj = New-Object -Type PSObject -Prop $props
    $result = $result + $obj
    }
    $result | Export-Csv D:\export.csv -append

  • #15788
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    Joakim, did you run your code and check the output? Neither of your examples seem to work.

    In the first example you output a hash-table to csv. This works but it only gives you the information about the hash-table, not the actual information that is in the hash-table. You should only export objects to CSV. In your second example you tried this, however where you went wrong was trying to add the objects together. Objects cannot be added together. You were almost there however, and you can see below a simple change to your last example returns the expected output.

    $root = [ADSI]"GC://DC=domain,DC=forest,DC=com"
    $search = [ADSISearcher]$root
    $search.Filter = "objectclass=computer"
    $computers = $search.FindAll()
    foreach ($computer in $computers)
    {
       $PC = $computer.getdirectoryentry()
       $props =@{'name'=$PC.name.value;'distinguishedname'=$PC.distinguishedname.value}
       New-Object -Type PSObject -Prop $props
    } | Export-Csv export.csv -append
    

    Also note that it is unnecessary to use the ampersand and brackets in the LDAP filter if you are only filtering by a single property. I also removed the $Output variable as it was not being used anywhere.

    This could even be done from the console in a single line:

    $([adsisearcher]"objectclass=computer").FindAll() | select @{L="Name";E={$_.properties["cn"]}},@{L="DistinguishedName";E={$_.properties["distinguishedname"]}} | epcsv C:\results.csv -NoTy
    

    Not the prettiest but just wanted to show that it is possible (I used aliases and shortened parameter names on purpose, that's what one-liners in the console is all about!). This syntax assumes you're searching a single domain that your computer is joined to though, so if you need to specify addition subdomains I don't think you'd be able to do that with just one line of code.

  • #15665
    Profile photo of deiandrei
    deiandrei
    Participant

    I found one way, but other ideas are also welcome!

    
    $root = [ADSI]"GC://DC=domain,DC=forest,DC=com"
    $search = [ADSISearcher]$root
    $search.Filter = "(&(objectclass=computer))"
    $computers = $search.FindAll()
    foreach ($computer in $computers)
    {
    $PC = $computer.getdirectoryentry()
    New-Object PSObject @{'name'=$PC.name.value;'distinguishedname'=$PC.distinguishedname.value} | export-csv D:\export.csv -append
    }
    
  • #15666
    Profile photo of Joakim
    Joakim
    Participant

    You're on the right track! f you only need the unique computername and the distinguished name you could use a hash instead.

    $root = [ADSI]"GC://DC=domain,DC=forest,DC=com"
    $search = [ADSISearcher]$root
    $search.Filter = "(&(objectclass=computer))"
    $computers = $search.FindAll()
    $hash = $null
    $hash = @{}
    foreach ($computer in $computers)
    {
       $PC = $computer.getdirectoryentry()
       $hash.Add($PC.name.value, $PC.distinguishedname.value)
    }
    $hash | Export-Csv D:\export.csv -append

    Sorry for the bad formatting but the forum editor sucks when it comes to code... 🙁

You must be logged in to reply to this topic.