Snippet / Tips for Building Reports in PowerShell

At least two or three times per work-week, I’m given a requirement for some bespoke reporting: Active Directory Group/OU membership, SCCM Hardware Data, or APIs for various other management systems. The requests are varied enough that I can’t really anticipate them; the best I can do is develop standardized means of collecting and presenting the results.

After seeing this post, and particularly u/gangstanthony’s reply which is basically the method I’ve settled on for normalizing a string list of source data, I’ve decided to formalize my standard framework for bespoke reporting into an ISE snippet. So, here goes:

$srcObjs = @( @" ### CTRL + V ### "@ -split "`n" | % Trim | ? {$_.Length -gt 0 -and $_ -notlike "#*"} ) <# -- OR -- $srcObjs = @( Get-ADComputer -SearchBase "ou=MyServerOU,dc=MyDomain,dc=MyDomainExt" -Filter * ) #> $foreachScript = { $outObj = [PSCustomObject]@{ PSTypeName = "ForEachScriptTag" "Source.Name" = $_ <# -- OR -- "Source.Name" = $_.Name #> "Example.IPAddresses" = $null # Example. } # Allows quick negation of code block by adding a caret to front of comment. $outObj."Example.IPAddresses" = @( Resolve-DnsName -Name $outObj."Source.Name" -Type A -ErrorAction Ignore | ForEach-Object IPAddress ) #> $outObj # Any code branch that terminates before here must "return $outObj". } $selectProperties = @( "Source.Name" "Example.IPAddresses" @{ Name = "Rufio" Expression = {"Bang-o-Rang!"} } ) Clear-Host $Global:Error.Clear() $resultObjs = @() $fmtObjs = @() $srcObjs | ForEach-Object $foreachScript | Tee-Object -Variable resultObjs | Select-Object $selectProperties | Tee-Object -Variable fmtObjs | Format-Table * | Out-Host Clear-Host $fmtObjs | Format-Table * -AutoSize | Out-Host if ( $srcObjs.Count -ne $resultObjs.Count -or $resultObjs.Where( {$_ -isnot [PSCustomObject] -or $_.pstypenames[0] -ne "ForEachScriptTag"} ).Count -gt 0 ) { Write-Warning "Mismatch between src/resultObjs! Check that all paths emit `$outObj and nothing else!" } #$fmtObjs | Export-Excel -Path $exportPath -TableName table 

$srcObjs as provided in list form or queried from dataset are fed into a ForEach-Object, with the $foreachScript specified as a variable to reset indentation and suggest a logical separation of its internals from wider program flow.

The $foreachScript declares an $outObj [PSCustomObject] at the top, which is emitted at the bottom. The script should always emit this object, and should emit nothing else. The healthiest means of doing so is to not attempt a return anywhere short of this. The object is tagged with a PSTypeName, and a subsequent query will generate a visible warning if the script does not follow this rule.

Once emitted, the $outObj is teed into the $resultObjs collection, and passed to a Select-Object cmdlet that serves as a formatting/presentation layer, with the results teed in turn to $fmtObjs. By doing so, we remove a degree of possible complexity from $foreachScript, and ensure the data collected is available in its most malleable form for additional analysis.

After this final teeing, the object is written to the console in Table form. The pipeline structure ensures objects are written as they are emitted, which is super important for tracking progress if it takes more than a few seconds to generate the full result set.

And finally, the ISE console is cleared and $fmtObjs is re-output to the console. The -AutoSize parameter, which ensures this final output contains no ellipses that might hide important information, could not be specified before because it would have delayed console output.

Once collected in $fmtObjs, the data can be exported using Export-Excel from the ImportExcel module. If I have some inkling why the customer wants the data, I might provide additional spreadsheets with further transformations of $resultObjs (e.g. Group-Object) using one-liners at the ISE console.

submitted by /u/NathanielArnoldR2
[link] [comments]

Leave a Reply