Folgendes Script listet rekursiv alle Mitglieder einer Gruppe mit allen ihren Mitgliedschaften auf und stellt die Mitgliedschaften in einer Excel Matrix Benutzer x Gruppe dar. Bei jedem Benutzer werden die Attribute SamAccountName, Givenname, Surname, Country und Department aufgelistet. Das Ganze ergibt dann eine Tabelle nach folgendem Schema:
SamAccountName | Attribute | Gruppe 1 | Gruppe 2 | ... |
---|---|---|---|---|
user1 | ... | x | ||
user2 | ... | x | ||
user3 | ... | x | x |
Für das Script muß die Powershell ActiveDirectory Erweiterung sowie natürlich Excel installiert sein.
###############################################################################
# List-Group-Members-in-Excel-Matrix.ps1 #
# Version 1.5 2014-03-03 by Bjoern Gaul #
# Takes all user members of one or more groups and exports them to #
# an Excel MATRIX document. #
###############################################################################
#Lese übergebene Parameter (Dies muss die erste PS-Zeile sein!)
param (
[string] $SourceGroup = $(Read-Host -prompt "Eingabe Quellgruppe") #Wenn nicht übergeben, fordere Eingabe.
)
#Starte die Ausführung nur, wenn genügend Parameter übergeben wurden.
#Wird noch benötigt, da der Benutzer auch eine "leere" Eingabe machen kann.
if ($SourceGroup -eq "" -or $SourceGroup -eq "--help" -or $SourceGroup -eq "-help" -or $SourceGroup -eq "-h" -or $SourceGroup -eq "-?" -or $SourceGroup -eq "/?")
{
Write-Host;
Write-Host -ForegroundColor red "Usage: Export-Members-Excel.ps1 SourceGroup"
Write-Host "Um die Mitglieder einer Gruppe anzeigen zu können, muss die Quellgruppe angegeben werden."
Write-Host;
exit
}
Function Convert-NumberToA1 {
<#
.SYNOPSIS
This converts any integer into A1 format.
.DESCRIPTION
See synopsis.
.PARAMETER number
Any number between 1 and 2147483647
Source: http://gallery.technet.microsoft.com/office/Powershell-function-that-88f9f690
#>
Param([parameter(Mandatory=$true)]
[int]$number)
$a1Value = $null
While ($number -gt 0) {
$multiplier = [int][system.math]::Floor(($number / 26))
$charNumber = $number - ($multiplier * 26)
If ($charNumber -eq 0) { $multiplier-- ; $charNumber = 26 }
$a1Value = [char]($charNumber + 64) + $a1Value
$number = $multiplier
}
Return $a1Value
}
Function Load-Module #Teste und lade ggfs. benötigte Module
{
Param([string]$name)
if(-not(Get-Module -name $name))
{
if(Get-Module -ListAvailable | Where-Object { $_.name -eq $name })
{
Import-Module -Name $name
#$true #Wurde geladen
}
else { $false } #Ist nicht verfügbar und kann daher nicht geladen werden
}
#else { $true } #Bereits geladen
}
Load-Module -name "ActiveDirectory"
$sg = $SourceGroup + "*"
If (Get-ADGroup -Filter {SamAccountName -like $sg}) #Teste Sourcegr.
{
# Erstelle ein Objekt für die Excel-Anwendung
$xl=New-Object -ComObject "Excel.Application"
# Erstelle ein Arbeitsblatt-Objekt
$wb=$xl.Workbooks.Add()
# Mache Excel sichtbar (Debug)
$xl.Visible=$True
# Lösche unbenutze Tabellen des Arbeitsblatts
# Delete unneeded sheets
$S2 = $wb.sheets.item(2)
$S3 = $wb.sheets.item(3)
$s2.delete()
$s3.delete()
# Setze $ws auf die aktuelle (einizige) Tabelle
$ws=$wb.ActiveSheet
$ws.Name = 'Memberships'
$wsnr = 1; $column = 6 #Setze Startwerte für Zeile und Spalte
$ws.Cells.Item(1, 1) = "SamAccountName"
$ws.Cells.Item(1, 2) = "Givenname"
$ws.Cells.Item(1, 3) = "Surname"
$ws.Cells.Item(1, 4) = "Country"
$ws.Cells.Item(1, 5) = "Department"
Foreach($ADGroup in (Get-ADGroup -Filter {SamAccountName -like $sg}))
{
# Ermittle User-Gruppenmitglieder und prüfe ob >= 1
$Members = Get-ADGroupMember -Identity $ADGroup -Recursive | Where-Object { $_.objectClass -eq 'user' } | Get-ADUser -Properties *
# Nur $Members.count zählt falsch bei nur einem Mitglied!
$count = ($Members | Measure-Object).count
#$ADGroup.SamAccountName + " " + $Count #DEBUG
If ($count -ge 1)
{
# Trage Gruppenname
$ws.cells.item(1,$column).Orientation = 90
$ws.cells.item(1,$column) = <#" " +#> $ADGroup.SamAccountName
# Setze x für Mitglieder
Foreach($Member in $Members)
{
$Givenname = $Member.Givenname
$Surname = $Member.Surname
$Country = $Member.CanonicalName | %{"$($_.Split('/')[2])"}
$Department = $Member.Department
$Member = $Member.SamAccountName
#Selektiere 1. Spalte
$mainRng = $ws.Range("A1").EntireColumn
#Fehler unterdrücken, da Match beim Nichtauffinden ein Fehler ausgibt.
$ErrorActionPreference = "SilentlyContinue"
$row = 0
$row = $xl.worksheetfunction.match("$Member",$mainRng,0)
if($row)
{
# Füge "x" bei gefundenem $Member hinzu
$ws.Cells.Item($row, $column) = "x"
}
Else
{
# Da $Member nicht gefunden, suche letzte Zeile und füge $Member hinzu
$mainRng = $ws.usedRange
$Row = $mainRng.Rows.Count + 1
$ws.Cells.Item($row, 1) = "$Member"
$ws.Cells.Item($row, 2) = "$Givenname"
$ws.Cells.Item($row, 3) = "$Surname"
$ws.Cells.Item($row, 4) = "$Country"
$ws.Cells.Item($row, 5) = "$Department"
$ws.Cells.Item($row, $column) = "x"
}
$ErrorActionPreference = "Continue"
}
# Gehe eine Spalte weiter
$column = $column + 1
}
}
# Führe Formatierungen nur durch, wenn mindestens eine Gruppe aufgelistet wurde
if($column -gt 6)
{
#Formatierungen usw.:
$mainRng = $ws.usedRange
# Neue Excel-2010 Formatierung
$listObject = $ws.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $ws.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.TableStyle = "TableStyleMedium16" # Style Cheat Sheet in French/English: http://msdn.microsoft.com/fr-fr/library/documentformat.openxml.spreadsheet.tablestyle.aspx
<# Erhöhe Höhe Zeile 1 und richte Text am oberen Rand aus.
$LastCol = (Convert-NumberToA1 $mainRng.Columns.Count) + "1"
$headerRange = $ws.Range("C1:$LastCol") #C1 Solange Beschreibung angezeigt wird.
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") | out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
$headerRange.VerticalAlignment = $xlConstants::xlTop
$headerRange.RowHeight = 250
#>
<# Alte Formatierung (funktioniert auch mit Excel kl. 2010)
# Überschriften Filtermöglichkeit setzen
$LastCol = (Convert-NumberToA1 $mainRng.Columns.Count) + "1"
$headerRange = $ws.Range("B1:$LastCol")
$headerRange.AutoFilter() | Out-Null
#$headerRange.AutoFit() | Out-Null
#Ermitteln letzte Zeile
$LastRow = $mainRng.Rows.Count
#Sortieren 1. Spalte A-Z
$ws.Range("A2:($LastCol$LastRow)").Sort($ws.Range("A1"), 1) >$null
#Alternierende Zeilenhinterlegung
For($i = 2; $i -le $LastRow; $i++)
{
if($i % 2) # Modulo
{
# Ungerade
$ColorStart = "A" + "$i"
$ColorEnd = (Convert-NumberToA1 $mainRng.Columns.Count) + "$i"
$ColorRange = $ws.Range("($ColorStart):($ColorEnd)")
$ColorRange.Select() | Out-Null
$ColorRange.Interior.ColorIndex = 15
}
}
#Rahmen
$mainRng.Borders.Color = 0
$mainRng.Borders.Weight = 2
#>
# Autom. anpassen der Spaltenbreiten
$mainRng.EntireColumn.AutoFit() | out-null
}
# Kein Speichern Dialog, da ein Schließen ohne Speichern den Dialog autom. bringt
$wb.Close()
$xl.Quit()
# Trotz Quit beendet Excel nicht sauber. Mit folgendem aber doch.
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
Remove-Variable * -ErrorAction SilentlyContinue
Start-Sleep 1
Stop-Process -name excel #Sollte unnötig sein, ist es aber nicht. Beendet aber alle Excel Prozesse!
}
Else
{
Write-Warning "Sourcegroup $sg not found not in AD."
}