© 2018 Amistad Group S.R.L 

Search
  • IT Team AmistadGroup

PowerShell - AD Groups Memberships, Excel Readable way

Recently I had a request to produce an export from Active Directory with all groups that each computer object is member of, and that export should be presented in a Excel readable file.

As first this looks a simple task by invoking simple Power Shell commands you can take that data and export it in a csv or excel file. Working on this task I saw the complexity of this request and that is generated by the fact that a computer object can be member in more than one groups and to handle that data in excel way started to look very complicated.

The end goal was to have something like this:

To achieve this I followed the following steps:

First thing first, let's start our script in a clean environment by


Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host


1. You need a machine on which you need to have excel installed because we will generate an excel file and not an csv file:


$excel= New-Object -ComObject Excel.Application

$excel.visible=$true

$workbook= $excel.workbooks.add()

$workbook.ActiveSheet.name= "MemberOf"

$worksheet=$workbook.worksheets.item(1)

$worksheet.activate()


2. You need to be able to connect to a domain controller and to read data from it, in our case computer objects


$cred = Get-Credential -Credential username


or


$credential = Get-Credential domain\username

$encrypt_string = $credential.Password | ConvertFrom-SecureString | Out-File "C:\Software\password.txt"

$Path = "C:\Software"

$Passwd = Get-Content "$Path\password.txt" | CovertTo-SecureString

$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "domain\username", $Passwd

3. Using power-shell command Get-ADComputer you can produce a list with all machines that are part of your domain


$list = Get-ADComputer -Filter * -Properties * -Credential $cred -Server 'domaincontroller01'


4. For each and every machine from the above list you need take Get-ADPrincipalGroupMembership based on machine guid


$ObjGuid = ($machine.ObjectGUID).Guid

$GMMachine = Get-ADPrincipalGroupMembership -Identity $ObjGuid -Credential $cred -Server 'domaincontroller01' | Select-Object @{Name = 'GroupName'; Expression = {$_.SamAccountName}}


5. At the end all this data needs to be formatted in excel way for a non-technical person to be able to identify fast what exactly he needs to find.The tricky part is step 5 that needs to create that column group(X+1) which is done via a computer object loop and by this line:


for ($j=0; $j -lt $count ; $j++) {

$worksheet.Cells.Item($i,2+$j = $GMMachine[$j].GroupName

}


Now is time to save our data and close the excel file:


[void] $workbook.saveas("C:\Software\pc_groups.xlsx")

$excel.displayalerts=$false

$workbook.close()

[void]$excel.quit()


For more details about this script and the way is working please let's have a private chat.


Thank you.

22 views