Create Excel Reports in Powershell

Create Excel Reports with nice Graphs in Powershell.

ConfigMgr has plenty of scope in the Reporting Point to produce nice reports on everything SCCM.
You can also publish these reports in Sharepoint or other dashboard.
Or you can use Reporting Services for SQL.

All of which are great but managers are all different, and all the same. What they want are graphics and charts, an overview of status, not too much detail, but they want to see that everything is going well and that you have put in a bit of effort to produce something presentable. This last point is universally accepted as an existence-justification and plays sadly a large part in any job today. So how to demonstrate just how industrious you are?

Answer: Make lovely looking charts and graphs and present them…

At the moment we don’t have SQL Reporting Services, and I want to focus on setting it up, making some cool dashboards. This means I need some time away from producing charts and graphs and powerpoints. And SCCM Reporting only does 2D graphs, not as pretty as managers expect.

Answer 2: build a script to produce nice looking excel-based reports and charts for me. I’ll still need to present, I haven’t found a way of scripting that yet, but I’ll have more time to focus on my work.

This is one variation, based on using what is available. That is, Excel. Visio is also possible, as is using SMTP to email the report to your boss, or automatically print out before a status review meeting.

Here is the script (in Powershell of course):

clear
#————————————————————————–
$script:SQLServer = “”
$script:SQLDBName = “”
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.add()
$chartType = “Microsoft.Office.Interop.Excel.xlChartType” -as [type]
$j = 1
$outputFile = ‘c:\Temp\PatchReview\PatchReview.xlsx’
$filter = ‘%Patch-Windows-Updates%’
#————————————————————————–
if(!(test-path c:\Temp\PatchReview\TablesCache)){
New-Item c:\Temp\PatchReview\TablesCache -ItemType Directory
}
#————————————————————————–
function RunSQLQuery($SQLQuery,$TableName){
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = “Server = $script:SQLServer; Database = $script:SQLDBName; Integrated Security = True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
BuildTable $DataSet $TableName
}
#————————————————————————–
function BuildTable($DataSetIn,$TableName){
$TableObject = New-Object System.Data.DataTable $TableName
foreach($Column in $DataSetIn.Tables[0].Columns){
$TableObject.Columns.Add((New-Object system.Data.DataColumn $Column.ColumnName,([string])))
}
foreach($Row in $DataSetIn.Tables[0].Rows){
$newRow = $TableObject.NewRow()
for($i=0;$i -lt $DataSetIn.Tables[0].Columns.Count;$i++){
$newRow.($DataSetIn.Tables[0].Columns[$i].ColumnName) = $Row[$i]
}
$TableObject.Rows.Add($newRow)
}
if($TableName -like “Compliance Summary – Patch-Windows-Updates-*”)
buildReport $TableObject $TableName
}
if($TableName -eq “UpdateList”){
BuildAssignmentsReport $TableObject
}
if($TableName -eq “Insgesamt”){
BuildInsgesamtReport $TableObject ́
}
$TableObject | Export-Clixml “c:\Temp\PatchReview\TablesCache\$TableName.xml” -Force
}
#————————————————————————–
function buildReport($Data,$TableName){
$ChartName = $TableName -replace “Compliance Summary \- Patch\-Windows\-Updates\-”
$ChartName = $ChartName -replace “\ \- \(01\.2012\)”
if($j -gt 3){
$workbook.Worksheets.Add([System.Reflection.Missing]::Value,$excel.Worksheets.Item($excel.Worksheets.count)).Name = $ChartName
}
}else{
$workbook.Worksheets.item($j).Name = $ChartName
}
$sheet = $workbook.Worksheets.item($ChartName)
$x = 2
$sheet.cells.item(1,1)= “LastEnforcementState”
$sheet.cells.item(1,2)= “NumberOfComputers”
$sheet.cells.item(1,3)= “PercentageComputers”
Foreach($row in $Data)
{
$sheet.cells.item($x,1) = $row.LastEnforcementState
$sheet.cells.item($x,2) = $row.NumberOfComputers
$sheet.cells.item($x,3) = $row.PComputers
$x++
}
$range = $sheet.usedRange
$range.EntireColumn.AutoFit()
$chart = $workbook.charts.add()
$chart.chartType = $chartType::xl3DPie
$chart.HasTitle = $true
$chart.ChartTitle.Text = $ChartName
$workbook.ActiveChart.SetSourceData($range)
$workbook.ActiveChart.Location(2,$($sheet.name))
$workbook.ActiveChart.Parent.Height = 300
$workbook.ActiveChart.Parent.Width = 400
$workbook.ActiveChart.Parent.Top = 200
$workbook.ActiveChart.Parent.Left = 0
$j++
}
#————————————————————————–
function BuildAssignmentsReport($Data){
$workbook.Worksheets.Add([System.Reflection.Missing]::Value,$excel.Worksheets.Item($excel.Worksheets.count)).Name = “ApplicableUpdates”
$sheet = $workbook.Worksheets.item(“ApplicableUpdates”)
$x = 2
$sheet.cells.item(1,1) = “BulletinID”
$sheet.cells.item(1,2) = “ArticleID”
$sheet.cells.item(1,3) = “Title”
$sheet.cells.item(1,4) = “Description”
Foreach($row in $Data)
{
$sheet.cells.item($x,1) = $row.BulletinID
$sheet.cells.item($x,2) = $row.ArticleID
$sheet.cells.item($x,3) = $row.Title
$sheet.cells.item($x,4) = $row.Description
$x++
}
$range = $sheet.usedRange
$range.EntireColumn.AutoFit()
}
#————————————————————————–
function BuildInsgesamtReport($Data){
$workbook.Worksheets.Add([System.Reflection.Missing]::Value,$excel.Worksheets.Item($excel.Worksheets.count)).Name = “Insgesamt”
$sheet = $workbook.Worksheets.item(“Insgesamt”)
$ChartName = “Insgesamt”
$x = 2
$sheet.cells.item(1,1) = “Compliance State”
$sheet.cells.item(1,2) = “Hosts”
Foreach($row in $Data)
{
$sheet.cells.item($x,1) = $row.StateName

$sheet.cells.item($x,2) = $row.Hosts
$x++
}
$range = $sheet.usedRange
$range.EntireColumn.AutoFit()
$chart = $workbook.charts.add()
$chart.chartType = $chartType::xl3DPie

$chart.HasTitle = $true
$chart.ChartTitle.Text = $ChartName
$workbook.ActiveChart.SetSourceData($range)

$workbook.ActiveChart.Location(2,$($sheet.name))
$workbook.ActiveChart.Parent.Height = 300
$workbook.ActiveChart.Parent.Width = 400
$workbook.ActiveChart.Parent.Top = 200
$workbook.ActiveChart.Parent.Left = 0
}
#————————————————————————–
RunSQLQuery “SELECT dbo.v_CIAssignment.AssignmentName, dbo.v_CIAssignment.Assignment_UniqueID, dbo.v_CIAssignment.StartTime,
dbo.v_CIAssignment.EnforcementDeadline, dbo.v_Collection.Name, dbo.v_CIAssignment.AssignmentID
FROM dbo.v_CIAssignment INNER JOIN
dbo.v_Collection ON dbo.v_CIAssignment.CollectionID =
dbo.v_Collection.CollectionID
WHERE (dbo.v_CIAssignment.AssignmentName LIKE ‘%$filter%’)” “CIAssignmentTable”
#————————————————————————–
RunSQLQuery “SELECT CollectionID, Name, Comment
FROM dbo.v_Collection” “CollectionTable”
#————————————————————————–
#————————————————————————–
$CIAssignmentTable = Import-Clixml c:\Temp\PatchReview\TablesCache\CIAssignmentTable.xml
foreach($Row in $CIAssignmentTable){
$DeploymentName = $Row.AssignmentName
$DEPLOYMENTID = $Row.Assignment_UniqueID
$strSQL = “declare @DeploymentLocalID as int
declare @COLLCOUNT as int
declare @DEPLOYMENTID as nvarchar(50)
set @DEPLOYMENTID = ‘$DEPLOYMENTID’
select @DeploymentLocalID = AssignmentID from v_CIAssignment where Assignment_UniqueID = @DEPLOYMENTID
select @COLLCOUNT=count(*) from v_CIAssignmentTargetedMachines where AssignmentID=@DeploymentLocalID
select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers,
PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif(@COLLCOUNT, 0), 1))),
sn.TopicType*10000 + sn.StateID as DeploymentStateID from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where (@DEPLOYMENTID is null or a.Assignment_UniqueID = @DEPLOYMENTID)
and assc.StateType in (300,301)
group by a.Assignment_UniqueID,a.AssignmentName, a.StartTime, a.EnforcementDeadline, sn.StateName, sn.TopicType, sn.StateID
order by sn.StateName”
RunSQLQuery $strSQL “Compliance Summary – $DeploymentName”
}
#————————————————————————–
$AssignmentList = $null
foreach($Row in $CIAssignmentTable){
$AssignmentList = $AssignmentList + $Row.AssignmentID + ‘,’
}
$AssignmentList = $AssignmentList.TrimEnd(‘,’)
RunSQLQuery “SELECT sn.StateName,(ac.StateType*10000 + isnull(ac.StateID,0)) AS [StateID],COUNT(ac.ResourceID) AS [Hosts] FROM
v_AssignmentState_Combined ac
JOIN v_StateNames sn ON (sn.TopicType*10000 + sn.StateID) = (ac.StateType*10000 + ISNULL(ac.StateID,0))
WHERE ac.AssignmentID in ($AssignmentList)
GROUP BY sn.StateName,(ac.StateType*10000 + ISNULL(ac.StateID,0))
ORDER BY sn.StateName” “Insgesamt”
#————————————————————————–
RunSQLQuery “SELECT DISTINCT dbo.v_CIAssignmentToCI.CI_ID, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Title,
dbo.v_UpdateInfo.Description
FROM dbo.v_CIAssignment INNER JOIN
dbo.v_CIAssignmentToCI ON dbo.v_CIAssignment.AssignmentID = dbo.v_CIAssignmentToCI.AssignmentID INNER
JOIN
dbo.v_UpdateInfo ON dbo.v_CIAssignmentToCI.CI_ID =
dbo.v_UpdateInfo.CI_ID
WHERE (dbo.v_CIAssignment.AssignmentName LIKE ‘%$filter%’)” “UpdateList”
#————————————————————————–
#————————————————————————–
$excel.visible = $true
$hwnd = $excel.Hwnd
#————————————————————————–
$excel.DisplayAlerts = $false
$workbook.SaveAs($outputFile)
#————————————————————————–
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Get-Process -Name EXCEL | Where-Object {$_.MainWindowHandle -eq $hwnd} | Stop-Process
#————————————————————————–

Example Report: PatchReview

Advertisement