Alan's Blog

"Yeah. I wrote a script that will do that."

Convert data from Get-ADUser and Get-ADComputer for Export-CSV

Posted on July 27th, 2013

If you have been following my blog, you know that I am an experienced vbscripter, but am relatively new to PowerShell.  I was excited that my long vbscripts to export data from Active Directory would be now one liners which I could send to a CSV file with the Export-CSV cmdlet.   Unfortunately, I found that getting the data I wanted was not as easy as I expected.  There is no automatic way of handling the different date strings in Active Directory.  And, although some of the data looked okay when output to the screen, values stored other than a simple string often gave me results like “Microsoft.ActiveDirectory.Management.ADPropertyValueCollection”.

Because of these issues, I set out to create my first PowerShell function.  My goals were to write something that I could use every day which would take the output of Get-ADUser,  Get-ADComputer and other Get-AD* cmdlets within the ActiveDirectory module and convert the data to strings which would properly output to Export-CSV.  As far as I can tell from my internet search, no one has written such a PowerShell function.   And, of course, the best way to learn a new programming language is to have a real project instead of just exercises.

I had some help with direction by the Scripting Guy himself, Ed Wilson.  I have been truly fortunate to be a member of a PowerShell user group with him as a member and regular speaker.  (For more about Ed and the group, read my previous post.)  Some of my discussion with Ed led to Scripting Guy articles about Active Directory and Export-CSV. Some of the code he sent me prior to the publication of these articles sent me on the right track to create Convert-ADValues.ps1.

Convert-ADValues pre-processes the output of the AD cmdlets, such as Get-ADUser and Get-ADComputer so that the output works with Export-CSV.  All dates appear properly.  ProxyAddresses and PostalAddress appear properly.  Binary data appears a a comma delimited string.

Example 1:
$u = Get-ADUser -Filter {surname -eq “smith”} -properties *
Convert-ADValues $u

Example 2:
$u = Get-ADUser -Filter {surname -eq “smith”} -properties *
$u | Convert-ADValues | Export-CSV -notypeInformation -Path $env:userprofile\desktop\ADInfo.csv

Example 3:
Get-ADComputer -Identity $env:COMPUTERNAME -Properties * |Convert-ADValues

To learn about running scripts and “dot sourcing”, examine the basics here.  I hope the comments within the script are adequate to explain what is being done.  Don’t hesitate to comment or email me with questions.

Changelog:
Updated 8-6-13. I fixed handling of binary, null and empty values, arrays in item, and changed delimiter to semi-colon for values with commas.  And yes, it isn’t pretty.
Updated 1/1/14. I added a switch for enumerating ACLs for the nTSecurityDescriptor attribute, changed names of variables away from users, improved comment based help.
Updated 1/2/14. Removed write-host of Security type added while debugging. Added Online Help.  Moved Load-Module ActiveDirectory to Begin statement, clarified Help to make clear that you must return nTSecurityDescriptor for it to be expanded with -GetSecurity. Removed return of PropertyNames and PropertyCount unless you use ReportPropertyNamesAndCount parameter. Changed output type to PSObject from Array. Added Requires statement for Version 3.
Updated 5/16/17. Added support for Microsoft.GroupPolicy.WmiFilter, Added new switch for getting expanded certificate information – Subject, Serial Number, Date Effective, Expires, Handle and Issuer, added switch TranslateGroups, simplified and improved get-security.

Script Text

Filed under Active Directory, Alan's Favorites, My Best, PowerShell, Windows Administration |

Leave a Reply

You must be logged in to post a comment.

Please Note

All the scripts are saved as .txt files. Newer files have a "View Script" button which will let you save or open a script in notepad. For earlier posts, the easiest way to download with IE is to right click on the link and use "Save Target As". Rename file from Name_ext.txt to Name.ext.

To see a full post after searching, please click on the title.

PowerShell Scripts were written with Version 3 or 4.

https connections are supported.

All new users accounts must be approved, as are comments. Please be patient. It is pretty easy to figure out my email address from the scripts, and you are welcome to contact me that way.

Site Search

Categories

Archives

SQL Site

Bad Behavior has blocked 262 access attempts in the last 7 days.