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!
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ################################################################################### | |
| ## 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 | |
| } |
hey Trevor – thanks for this template. What a life saver – I’m new to powershell and there are a lot of ways to do things but I just couldn’t find a way to email in the list of jobs executed in the last 34 hrs – failed or succeeded then I see your post and it was done in hour and tested
Getting the SQL for the query is fine – even in powershell – but html’ing it into an email – what amission
thanks for the template
Glad to help!