User & Group Audit Script

Question

I am looking for help writing a powershell script that will query Active Directory and output a CSV.

This script will list all groups and all users and signify with a character when a user belongs to that group.

The output will look like this: https://i.sstatic.net/tySmL.jpg

I've tried using dsquery and various other powershell methods, but none seem to work.

I'm hoping someone here will have a different perspective on this and be able to help out.

Thank you!

Update 1: As requested, here's my code that I was trying to work with previously.

#Get a list of the groups
$groups = Get-ADGroup -filter * -Properties Name | Select Name

#iterate through groups array and append each with a comma
$output = ForEach ($g in $groups){
$topgroups.Add($g)
$topgroups.Add(",")
}

#for each group, find out if the user is part of that group
$output = ForEach ($g in $groups) {
 $results = Get-ADGroupMember -Identity $g.name -Recursive | Get-ADUser -Properties enabled, SamAccountName, givenname, surname,physicalDeliveryOfficeName

ForEach ($r in $results){
 New-Object PSObject -Property @{
    GroupName = $g.Name
    Username = $r.name
    DisplayName = $r.displayname
  }
 }
} 
$output | Export-Csv -path c:\temp\output.csv -NoTypeInformation

Update 2:

Added FTP Upload and some more information. Thanks again TheMadTechnician!

My goal is to get this information from each of my clients, import this into SQL with SSIS with a timestamp, and then I can do can do comparison through sql reporting.

Here's my script where it is currently:

New-Item c:\temp\audit -type directory

$Domain = (gwmi WIN32_ComputerSystem).Domain
$filename = $Domain + "_ADExport.csv"
$fileoutput = "c:\temp\audit\" + $filename

Remove-Item $fileoutput

$GroupRef = @{}
Get-ADGroup -filter * | ForEach{$GroupRef.Add($_.DistinguishedName,$_.Name)}

$Users = Get-ADUser -Filter * -Prop MemberOf, passwordlastset, LastLogonDate
ForEach($User in $Users){
 $LineItem = [PSCustomObject]@{'Enabled'=$User.Enabled;'First Name'=$User.givenname;'Last Name'=$User.surname;'Location'=$User.physicalDeliveryOfficeName;'Domain'=$Domain;'SAMAccountName'=$User.samaccountname;'LastLoggedOn'=$User.lastlogonDate;'PasswordLastSet'=$User.passwordlastset}
    $GroupRef.Values | ForEach{Add-Member -InputObject $LineItem -NotePropertyName $_ -NotePropertyValue ""}
    $User.MemberOf | ForEach{$LineItem.$($GroupRef["$_"]) = "X"}
[Array]$Results += $LineItem
}
$Results|export-csv $fileoutput -notype


#we specify the directory where all files that we want to upload  
$Dir="C:/temp/audit/"

#ftp server 
$ftp = "ftp://8.8.8.8/"
$user = "test" 
$pass = "ThisIsARea11yL0NgPa33Word"  

$webclient = New-Object System.Net.WebClient 

$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)  

#list every file
foreach($item in (dir $Dir "*.csv")){ 
    "Uploading $item..." 
    $uri = New-Object System.Uri($ftp+$item.Name) 
    $webclient.UploadFile($uri, $item.FullName) 
 }

Update 3:

Good afternoon:

I've run into an issue where I am trying to restrict which OU this searches through:

$GroupRef = @{}
$OUPATH = (Get-ADOrganizationalUnit -Filter 'Name -like "CLIENT_GROUPS"' | FT DistinguishedName -HideTableHeaders | Out-String).Trim()
Get-ADGroup -SearchBase "$OUPATH" -Filter * | ForEach{$GroupRef.Add($_.DistinguishedName,$_.Name)}

The error is:

Exception setting "": "Cannot process argument because the value of argument "name" is not valid. Change the value of
the "name" argument and run the operation again."
At C:\Users\f12admin\Desktop\test.ps1:23 char:42
+     $User.MemberOf | ForEach{$LineItem.$($GroupRef["$_"]) = "X"}
+                                          ~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
+ FullyQualifiedErrorId : ExceptionWhenSetting

Answer 1

All you need are Get-ADUser, Get-ADGroup, New-Object, Add-Member, and Export-CSV. I'd build a hashtable of groups linking their distinguishedname and their displayname. Then I'd get a list of all users, create a custom object for each user, loop through the list of groups and add a property to the custom object for each group. Then loop through the user's MemberOf property and set the associated property on the custom object to "X" for everything there. Collect all of the custom objects in an array, and export it to a csv.

This isn't tested, but here's the theory...

$GroupRef = @{}
Get-ADGroup -filter * | ForEach{$GroupRef.Add($_.DistinguishedName,$_.Name)}
$Users = Get-ADUser -Filter * -Prop MemberOf
ForEach($User in $Users){
    $LineItem = [PSCustomObject]@{'DisplayName'=$User.DisplayName;'SAMAccountName'=$User.samaccountname}
    $GroupRef.Values | ForEach{Add-Member -InputObject $LineItem -NotePropertyName $_ -NotePropertyValue ""}
    $User.MemberOf | ForEach{$LineItem.$($GroupRef["$_"]) = "X"}
    [Array]$Results += $LineItem
}
$Results|export-csv c:\temp\output.csv -notype