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."
}

Kommentare

Kommentare durch Disqus