param( [System.String]$viewServer = "VdiConnectionServer", [System.String]$VDIClassesPath = "c:\View\VDI-classes.xml", [System.String]$ldfPath = "C:\View\vdiSchema.ldf", [System.String]$xlsPath = "C:\View\VDI-ADAM-Xref.xls" ) function Export-Xls { <# .SYNOPSIS Saves Microsoft .NET Framework objects to a worksheet in an XLS file .DESCRIPTION The Export-Xls function allows you to save Microsoft .NET Framework objects to a named worksheet in an Excel file (type XLS). The position of the worksheet can be specified. .NOTES Author: Luc Dekens .PARAMETER InputObject Specifies the objects to be written to the worksheet. The parameter accepts objects through the pipeline. .PARAMETER Path Specifies the path to the XLS file. .PARAMETER WorksheetName The name for the new worksheet. If not specified the name will be "Sheet" followed by the "Ticks" value .PARAMETER SheetPosition Specifies where the new worksheet will be inserted in the series of existing worksheets. You can specify "begin" or "end". The default is "begin". .PARAMETER ChartType Specifies the type of chart you want add to the worksheet. All types in the [microsoft.Office.Interop.Excel.XlChartType] enumeration are accepted. .PARAMETER NoTypeInformation Omits the type information from the worksheet. The default is to include the "#TYPE" line. .PARAMETER AppendWorksheet Specifies if the worksheet should keep or remove the existing worksheet in the spreadsheet. The default is to append. .EXAMPLE PS> $data = Get-Process | Select-Object Name, Id, WS PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -AppendWorksheet:$false .EXAMPLE PS> $data = Get-Process | Select-Object Name, Id, WS PS> Export-Xls $data C:\Reports\MyWkb.xls -SheetPosition "end" .EXAMPLE PS> $data = Get-Process | Select-Object Name, Id, WS PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -ChartType "xlColumnClustered" #> param( [parameter(ValueFromPipeline = $true,Position=1)] [ValidateNotNullOrEmpty()] $InputObject, [parameter(Position=2)] [ValidateNotNullOrEmpty()] [string]$Path, [string]$WorksheetName = ("Sheet " + (Get-Date).Ticks), [string]$SheetPosition = "begin", [PSObject]$ChartType, [switch]$NoTypeInformation = $true, [switch]$AppendWorksheet = $true ) begin { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel") if($ChartType){ [microsoft.Office.Interop.Excel.XlChartType]$ChartType = $ChartType } function Set-ClipBoard { param( [string]$text ) process{ Add-Type -AssemblyName System.Windows.Forms $tb = New-Object System.Windows.Forms.TextBox $tb.Multiline = $true $tb.Text = $text $tb.SelectAll() $tb.Copy() } } function Add-Array2Clipboard { param ( [PSObject[]]$ConvertObject, [switch]$Header ) process{ $array = @() if ($Header) { $line ="" $ConvertObject | Get-Member -MemberType Property,NoteProperty,CodeProperty | Select -Property Name | %{ $line += ($_.Name.tostring() + "`t") } $array += ($line.TrimEnd("`t") + "`r") } else { foreach($row in $ConvertObject){ $line ="" $row | Get-Member -MemberType Property,NoteProperty | %{ $Name = $_.Name if(!$Row.$Name){$Row.$Name = ""} $line += ([string]$Row.$Name + "`t") } $array += ($line.TrimEnd("`t") + "`r") } } Set-ClipBoard $array } } $excelApp = New-Object -ComObject "Excel.Application" $originalAlerts = $excelApp.DisplayAlerts $excelApp.DisplayAlerts = $false if(Test-Path -Path $Path -PathType "Leaf"){ $workBook = $excelApp.Workbooks.Open($Path) } else{ $workBook = $excelApp.Workbooks.Add() } $sheet = $excelApp.Worksheets.Add($workBook.Worksheets.Item(1)) if(!$AppendWorksheet){ $workBook.Sheets | where {$_ -ne $sheet} | %{$_.Delete()} } $sheet.Name = $WorksheetName if($SheetPosition -eq "end"){ $nrSheets = $workBook.Sheets.Count 2..($nrSheets) |%{ $workbook.Sheets.Item($_).Move($workbook.Sheets.Item($_ - 1)) } } $sheet.Activate() $array = @() } process { $array += $InputObject } end { Add-Array2Clipboard $array -Header:$True $selection = $sheet.Range("A1") $selection.Select() | Out-Null $sheet.Paste() $Sheet.UsedRange.HorizontalAlignment = [microsoft.Office.Interop.Excel.XlHAlign]::xlHAlignCenter Add-Array2Clipboard $array $selection = $sheet.Range("A2") $selection.Select() | Out-Null $sheet.Paste() | Out-Null $selection = $sheet.Range("A1") $selection.Select() | Out-Null $sheet.UsedRange.EntireColumn.AutoFit() | Out-Null $workbook.Sheets.Item(1).Select() if($ChartType){ $sheet.Shapes.AddChart($ChartType) | Out-Null } $workbook.SaveAs($Path) $excelApp.DisplayAlerts = $originalAlerts $excelApp.Quit() Stop-Process -Name "Excel" } } function Get-ADAMTree { <# .SYNOPSIS Reads the content of the View AD-LDS partition .DESCRIPTION The function will read all records in the View AD-LDS partition, based on the schema file that is passed with the VDISchema parameter. The AD-LDS partition is read recursively. The helper function Get-ADAMProperties gets the attributes for each record. .NOTES Author: Luc Dekens .PARAMETER Node The node in the AD-LDS partition where to start reading .PARAMETER VDIClasses The XML file that contains the View schema description .EXAMPLE PS> Get-ADAMTree -Node $node -VDIClasses "C:\schena.xml #> param( [CmdletBinding()] [parameter(Mandatory = $true)] [System.DirectoryServices.DirectoryEntry]$Node, [parameter(Mandatory = $true)] [System.Collections.Hashtable]$VDIClasses ) Begin{ function Get-ADAMProperties { param( [parameter(Mandatory = $true)] [System.DirectoryServices.DirectoryEntry]$Node, [parameter(Mandatory = $true)] [System.Collections.Hashtable]$VDIClasses ) $class = $node.SchemaClassName if($VDIClasses.ContainsKey($class)){ $model = $VDIClasses[$class] $base = New-Object PSObject $model.PSObject.Properties | %{ $base | Add-Member -MemberType $_.MemberType -Name $_.Name -Value $_.Value } $node | Get-Member -Name pae* | %{ $propName = $_.Name if("pae-NameValuePair","pae-LicenseKey2" -contains $propName -and !(Get-Member -InputObject $base -Name $propName)){ $base | Add-Member -Name $propName -Value $null -MemberType NoteProperty } $propValue = $node.($_.Name)[0] $base.$propName = $propValue } $base } } } Process{ if($node.Children){ $node.Children | %{ Get-ADAMTree $_ $VDIClasses } } Get-ADAMProperties $node $VDIClasses } } function Get-VDILDSSchema { <# .SYNOPSIS Reads the attributes from VIEW AD-LDS schema .DESCRIPTION The function will read all entries from the View AD-LDS schema and returns a simplified structure .NOTES Author: Luc Dekens .PARAMETER VDISChema The path to LDIF file that contains the schema .EXAMPLE PS> Get-VDILDSSchema -VDISchema #> param( [CmdletBinding()] [parameter(Mandatory = $true)] [System.String]$VDISchema ) $regAttrMain = [regex]"# objectclasses:.*NAME\s'(?.*)'.*DESC\s'(?.*)'.*SUP\s'(?.*)'(?.*)" $regAttrRequired = [regex]".*MUST\s\(\s(?.*?)\s\)" $regAttrOptional = [regex]".*MAY\s\(\s(?.*?)\s\)" $attrArr = @{} $skipClass = $false Get-Content -Path $VDISchema | where {$_ -match $regAttrMain} | %{ $class = $Matches['ClassName'] if($class -notmatch 'pae-AdminFolderID-' -and $class -notmatch '-a$'){ $description = $Matches['Description'] $subclass = $Matches['Subclass'] $rest = $Matches['Rest'] if($subclass -ne 'top' -and $subclass -match '^pae-'){ $template = $attrArr[$subclass] $template.Name = $class $template.Description = $description } else{ $template = New-Object PSObject -Property @{ Name = $class Description = $description } } if($rest -match $regAttrRequired){ $required = $Matches['Required'] $required.Split('$') | %{ $attrName = $_.Trim(' ') if(!($template | Get-Member -Name $attrName)){ $template | Add-Member -Name $attrName -Value $null -MemberType NoteProperty } } } if($rest -match $regAttrOptional){ $optional = $Matches['Optional'] $optional.Split('$') | %{ $attrName = $_.Trim(' ') if(!($template | Get-Member -Name $attrName)){ $template | Add-Member -Name $attrName -Value $null -MemberType NoteProperty } } } $attrArr[$class] = $template } } $attrArr } $view = "dc=vdi,dc=vmware,dc=int" $domain = "LDAP://$viewServer/$view" if(!(Test-Path -Path $VDIClassesPath)){ Get-VDILDSSchema -vdiSchema $ldfPath | Export-Clixml -Path $VDIClassesPath -Confirm:$false } $VDIClasses = Import-Clixml -Path $VDIClassesPath $root = New-Object System.DirectoryServices.DirectoryEntry $domain $report = Get-ADAMTree -Node $root -VDIClasses $VDIClasses if(Test-Path -Path $xlsPath){ Get-Item -Path $xlsPath | Remove-Item -Confirm:$false } $report | Group-Object -Property Name | %{ Export-Xls -InputObject $_.Group -Path $xlsPath ` -WorksheetName $_.Name -NoTypeInformation }