PowerShell : Exporting multi-valued attribute via Export-Csv cmdlet

Standard

The attributes that are multi-valued are hard to export to a CSV via the Export-Csv cmdlet as the exported value just shows the string type in Excel/Notepad.

For instance, take a look below when I try to export the proxyAddresses attribute values in PowerShell console and to a CSV later.

image

image

I found out that you can using the join function i.e @{Name=’proxyAddresses’;Expression={[string]::join(“;”, ($_.proxyAddresses))}} can export the multiple values from a multi-valued attribute to a CSV accordingly.

So, this is how it would look for the query I ran above.

Get-QADUser test.user1 -IncludeAllProperties | select name,@{Name='proxyaddresses';Expression={[string]::join(";", ($_.proxyaddresses))}} | Export-Csv .testUser1.csv

To accomplish the export of all values in a spreadsheet/csv.

image

This should come handy also when you are trying to retrieve the ‘memberof’ attribute of users and trying to export all groups that a user is part of to a CSV. Just replace the attribute you are after in the join function above.

Add -notype paramater at the end of the export-csv cmdlet to avoid the #type information in the first row in csv.

Auditing Group Membership changes

Standard

I often get this asked this question, “how do I audit group membership changes”. Whereas a lot of AD Change Monitor Tools (Quest, Netwrix etc.) have nice reports that can be generated to look up this information, this question comes up when a change auditor product for AD is not in picture. Let me cover the highlights here.

1. You need to have the Auditing enabled with Group Policy.

Computer ConfigurationPoliciesWindows SettingsSecurity SettingsLocal PoliciesAudit Policy

 

image

2. In order to see on which DC the change was made, you can lookup the metadata via repadmin.

repadmin /showobjmeta test-dc01 "CN=Test Group,OU=Groups,DC=techevan,DC=lab"

Towards the end of the output you see the “absent” in this example on which DC a particular user was removed from this group.

Type     Attribute     Last Mod Time         Originating DSA         Loc.USN          Org.USN Ver        Distinguished Name
===  ========  ===========      =================   ======= ======= === =========================
ABSENT   member        2010-11-05 16:55:28 TestSiteTEST-DC01  749327  749327   2  CN=Rick Sheikh,OU=Users,DC=techevan,DC=lab

 

3.  You can comb the logs on the said DC using EventComb or Event Viewer. Event ID 4729 is logged when a member is removed from a group.

image

Some other important Event IDs for User and Group Auditing in Windows Server 2008 R2 are these:

4727 – A security-enabled global group was created.

4728 – A member was added to a security-enabled global group.

4730 – A security-enabled global group was deleted.

4731 – A security-enabled local group was created.

4732 – A member was added to a security-enabled local group.

4733 – A member was removed from a security-enabled local group.

4734 – A security-enabled local group was deleted.

4735 – A security-enabled local group was changed.

4737 – A security-enabled global group was changed.

4754 – A security-enabled universal group was created.

4755 – A security-enabled universal group was changed.

4756 – A member was added to a security-enabled universal group.

4757 – A member was removed from a security-enabled universal group.

4758 – A security-enabled universal group was deleted.

 

More reading here : http://www.windowsecurity.com/articles/Event-IDs-Windows-Server-2008-Vista-Revealed.html