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

Advertisements

5 thoughts on “Create Excel Reports in Powershell

  1. Isaac March 7, 2012 / 3:05 am

    Couple ob bugs/typos:
    There are a couple of lines where ISNULL became IS- NULL like this one:
    PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / is- null(nullif(@COLLCOUNT, 0), 1))),

    For some reason when copying the dash (-) in this line becomes an unknown character:
    RunSQLQuery $strSQL “Compliance Summary – $DeploymentName”

    Looks like another work hyphenation in this one also, updatelist should be one word for the If statement in the BuildTable function to work:
    WHERE (dbo.v_CIAssignment.AssignmentName LIKE ‘%$filter%’)” “Update- List”

    In BuildReport these lines generate errors again due to word wrap or lack thereof:
    $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)

    • andrewdcraig March 7, 2012 / 11:11 am

      Fixed the typos, I hope. I blame spellchecker on iPad.
      For the unknown character error, can you post the error message? Could this character be in the deployment name?
      Thanks
      Andy

  2. Isaac March 8, 2012 / 3:09 pm

    Probably was reformatted on the iPad from a – to an extended ascii dash or maybe a double byte character, the dashes in your comment lines are the same way. They show up as ? in the powershell ise or powergui.

    No special characters in my deployment names, they are all letter and numbers only.

    I got it working after making the changes above, made a couple other changes so it would be easier to use in my environment (multiple update deployments).

  3. allensmith2 January 19, 2015 / 4:27 am

    When I run the PowerSheel script manually – through a .bat file that executes it with the PowerShell interpreter – it correctly executes an SQL query and creates an Excel file and e-mails it.
    If I automate it – run the .bat file that runs the PowerShell file with the interpreter through the Windows job scheduler every night – then the e-mail is sent without the file.
    It is difficulty to copy the code here because I made a nice framework many functions deep with lambda-functions and all, but generally:
    $ExcelWorkBook.SaveAs($Filename)
    $ExcelWorkBook.Close()
    $ExcelApp.Quit()
    (…)
    Sleep -Seconds 500
    (…)
    $MailMessage.Attachments.Add($Filename)
    $SMTP.Send($MailMessage)
    I have heard that Excel does not actually support GUI-less automation (windows task scheduler) but that actually worked for me through a different framework.
    What can I do to make it work, if cannot how can I generate Excel files from PowerShell in a different way, or how can I just easily generate and e-mail reports in PowerShell?
    2d barcode in excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s