Skip to content
  • View menu
  • View sidebar

smsagent

Scripts, tools and tips, mostly around Microsoft Endpoint Manager

  • Home
  • About
  • Books / Guides
    • Deploying Custom Microsoft Office Templates with System Center Configuration Manager
    • Managing Java with Configuration Manager and PowerShell
  • Free ConfigMgr Reports
    • Instructions
    • Client Health Summary Report
    • Client Health Report
  • POSH 5 Custom Classes
    • Creating a Custom Class Library
    • Power Ping
    • Background Job
    • SQL Query
  • Privacy Policy
  • Tools
    • ConfigMgr Add2Collection
    • ConfigMgr Client Notification
    • ConfigMgr Deployment Reporter
    • ConfigMgr PXE Boot Log
    • ConfigMgr Remote Compliance
    • ConfigMgr Task Sequence Monitor
    • Reliability Viewer for Windows
    • System Explorer for Windows
    • WPF Control Explorer

Latest Free Tool: ConfigMgr Client TCP Port Tester

Recent Posts

  • Deploying HP BIOS Updates – a real world example
  • Calculating the Offline Time for a Windows 10 Upgrade
  • Windows 10 Upgrades – Dealing with Safeguard ID 25178825 (Conexant ISST Driver)
  • Getting Creative: a Bespoke Solution for Feature Update Deployments
  • Windows 10 Feature Update Readiness PowerBI Report (MEMCM version)
  • PowerBI Reports for Windows 10 Feature Update Compliance
  • Using a LiteDB portable database with your PowerShell project
  • Prevent Users from Disabling Toast Notifications – Can it be Done?
  • Adding Your Own Caller App for Custom Windows 10 Toast Notifications
  • Real world notes: In-place OS upgrade on Server 2012 R2 ConfigMgr distribution points

Top Posts & Pages

  • Find the Full Windows Build Number with PowerShell
    Find the Full Windows Build Number with PowerShell
  • Intune Client-Side Logs in Windows 10
    Intune Client-Side Logs in Windows 10
  • Querying for Devices in Azure AD and Intune with PowerShell and Microsoft Graph
    Querying for Devices in Azure AD and Intune with PowerShell and Microsoft Graph
  • A Customisable WPF MessageBox for PowerShell
    A Customisable WPF MessageBox for PowerShell
  • ConfigMgr Task Sequence Monitor
    ConfigMgr Task Sequence Monitor
  • Client Health Report
    Client Health Report
  • ConfigMgr Client TCP Port Tester
    ConfigMgr Client TCP Port Tester
  • Installing and Configuring Additional Languages during Windows Autopilot
    Installing and Configuring Additional Languages during Windows Autopilot
  • The Cost of Running a Personal Windows 10 VM in Azure
    The Cost of Running a Personal Windows 10 VM in Azure
  • Create a Custom Splash Screen for a Windows 10 In-Place Upgrade
    Create a Custom Splash Screen for a Windows 10 In-Place Upgrade

Category Cloud

Active Directory Applications Azure Compliance ConfigMgr configmgr client health Drivers Intune MDT Microsoft Office Network Orchestrator OS Deployment Packages PowerBI Powershell Project Server PXE Boot Reporting SCCM Software Updates SQL Uncategorized VMWare Windows Server Windows Troubleshooting WPF WSUS

Recent Comments

Chase R on Find the Full Windows Build Nu…
Henrik on Windows 10 Upgrade Splash Scre…
Ramon Jiménez on Export / Backup Compliance Set…
Tim on Find the Full Windows Build Nu…
Trevor Jones on Adding Your Own Caller App for…

Archives

  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • May 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • November 2016
  • September 2016
  • August 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014

Blog Stats

  • 954,568 hits

Categories

  • Active Directory
  • Applications
  • Azure
  • Compliance
  • ConfigMgr
  • configmgr client health
  • Drivers
  • Intune
  • MDT
  • Microsoft Office
  • Network
  • Orchestrator
  • OS Deployment
  • Packages
  • PowerBI
  • Powershell
  • Project Server
  • PXE Boot
  • Reporting
  • SCCM
  • Software Updates
  • SQL
  • Uncategorized
  • VMWare
  • Windows Server
  • Windows Troubleshooting
  • WPF
  • WSUS

Follow me on Twitter

My Tweets

Meta

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com

Enter your email address to follow this blog and receive notifications of new posts by email.

Follow smsagent on WordPress.com

Month / April 2018

April 5, 2018 by Trevor Jones

PowerShell Template for HTML-Style Email Report with SQL Data

  • ConfigMgr, Powershell, Reporting, SCCM, SQL
  • ConfigMgr SQL report, powershell html email, PowerShell SQL email, SCCM SQL report
  • 2 Comments

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:

htmlemail

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
}

view raw
New-HtmlSQLEmailReport.ps1
hosted with ❤ by GitHub

Blog at WordPress.com.
Cancel

 
Loading Comments...
Comment
    ×