Long time since I wrote a blog – been uber busy – but found time to slip this one in! Working with ConfigMgr a lot I have a number of html-style email reports written in PowerShell that get data from the SCCM database and send it to me and other SCCM admins. Personally, I much prefer getting a regular email containing useful information than to use the canned SSRS reports, or checking the SCCM console.
Below is a template script that can be used to send regular reports containing SQL data with a simple SQL query as an example. Simply create a scheduled task on an admin server to run the report regularly, using an action like:
PowerShell.exe -ExecutionPolicy Bypass -WindowStyle Hidden – File MyScript.ps1
The email from this example looks like:
The script uses a custom html style and a publically available css file to provide the formatting (this does mean internet access is required).
To use the script, you need the relevant permissions to query your SQL database and enter the SQL details at the top of the script, as well as your email parameters. Add your SQL query, update the headers in the ConvertTo-Html cmdlet and you’re away.
Ciao for now!
| ################################################################################### | |
| ## Script to send an html-formatted email report containing data from SQL Server ## | |
| ## ## | |
| ## Author: Trevor Jones ## | |
| ## Version 1.0 (5th Apr 2018) ## | |
| ################################################################################### | |
| # Database info | |
| $script:dataSource = 'SQLSERVER\INSTANCE' | |
| $script:database = 'DATABASE' | |
| # Email params | |
| $EmailParams = @{ | |
| To = 'Trevor.Jones@contoso.com' | |
| From = 'Reporting@contoso.com' | |
| Smtpserver = 'smtpserver' | |
| Subject = "SCCM Report: Windows 10 Systems" | |
| } | |
| # Function to get data from SQL server | |
| function Get-SQLData { | |
| param($Query) | |
| $connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;" | |
| $connection = New-Object –TypeName System.Data.SqlClient.SqlConnection | |
| $connection.ConnectionString = $connectionString | |
| $connection.Open() | |
| $command = $connection.CreateCommand() | |
| $command.CommandText = $Query | |
| $reader = $command.ExecuteReader() | |
| $table = New-Object –TypeName 'System.Data.DataTable' | |
| $table.Load($reader) | |
| $connection.Close() | |
| return $Table | |
| } | |
| # Define the SQL Query | |
| $Query = " | |
| select top 10 | |
| sys.Name0 as 'ComputerName', | |
| cs.Manufacturer0 as 'Manufacturer', | |
| cs.Model0 as 'Model', | |
| os.Caption0 as 'OS', | |
| sys.Client_Version0 as 'Client Version', | |
| sys.Creation_Date0 as 'SCCM Record Created', | |
| ch.LastActiveTime as 'Last Active' | |
| from v_R_System sys | |
| left join v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID | |
| left join v_GS_Computer_System cs on sys.ResourceID = cs.ResourceID | |
| left join v_GS_Operating_System os on sys.ResourceID = os.ResourceID | |
| where os.Caption0 like '%10%' | |
| Order by sys.Name0 | |
| " | |
| # Html CSS style | |
| $Style = @" | |
| <style> | |
| table { | |
| border-collapse: collapse; | |
| } | |
| td, th { | |
| border: 1px solid #ddd; | |
| padding: 8px; | |
| } | |
| th { | |
| padding-top: 12px; | |
| padding-bottom: 12px; | |
| text-align: left; | |
| background-color: #4286f4; | |
| color: white; | |
| } | |
| </style> | |
| "@ | |
| # Run the SQL Query | |
| $Results = Get-SQLData $Query | |
| # If results are returned | |
| If ($Results.count -ne 0) { | |
| # Convert results into html format | |
| $Html = $Results | | |
| ConvertTo-Html –Property 'ComputerName','Manufacturer','Model','OS','Client Version','SCCM Record Created','Last Active' –Head $style –Body "<h2>Top 10 Windows 10 Computers</h2>" –CssUri "http://www.w3schools.com/lib/w3.css" | | |
| Out-String | |
| # Send the email | |
| Send-MailMessage @EmailParams –Body $Html –BodyAsHtml | |
| } |
