HTML Report for SCCM Site Component Warnings and Errors

Just a quick one ūüôā

If you’re like me you are too lazy busy to regularly check the component status of an SCCM Site Server for any issues, so why not get PowerShell to do it for you?

The code below will email an html-formatted report of any site components that are currently in an error or warning status, together with the last few error or warning status messages for each component. Run it as a scheduled task or with your favorite automation tool to keep your eye on any current issues. Whether you get annoyed because you now created more work for yourself, or get happy because you can stay on top of issues in your SCCM environment, I leave to you!

The report will display the components that are marked as either critical or warning with the current number of messages:

It will then display the last x status messages for each component for a quick view of what the current issue/s are:

Run the script either on the site server or somewhere where the SCCM console is installed, and set the required parameters in the script.

## ##
## This script checks for any SCCM Site Server components currently in an error or warning ##
## state and emails it as an html report, including the latest status messages for each component. ##
## ##
# Site server FQDN
$SiteServer = ""
# Site code
$SiteCode = "ABC"
# Location of the resource dlls in the SCCM admin console path
$script:SMSMSGSLocation = ‚Äú$env:SMS_ADMIN_UI_PATH\00000409‚ÄĚ
# SCCM SQL Server / instance
$script:dataSource = 'SCCMServer'
# SCCM SQL database
$script:database = 'CM_ABC'
# Number of Status messages to report
$SMCount = 5
# Tally interval – see
$TallyInterval = '0001128000100008'
# Email params
$EmailParams = @{
To = ''
From = ''
Smtpserver = ''
Port = 25
Subject = "SCCM Site Server Component Status Report | $SiteServer | $SiteCode | $(Get-Date Format ddMMMyyyy)"
# Html CSS 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;
h2 {
color: red;
# Function to get data from SQL server
function Get-SQLData {
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
$connection = New-Object TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object TypeName 'System.Data.DataTable'
# Close the connection
return $Table
# Function to get the status message description
function Get-StatusMessage {
param (
# Set the resources dll
Switch ($DLL)
"srvmsgs.dll" { $stringPathToDLL = "$SMSMSGSLocation\srvmsgs.dll" }
"provmsgs.dll" { $stringPathToDLL = "$SMSMSGSLocation\provmsgs.dll" }
"climsgs.dll" { $stringPathToDLL = "$SMSMSGSLocation\climsgs.dll" }
# Load Status Message Lookup DLL into memory and get pointer to memory
$ptrFoo = $Win32LoadLibrary::LoadLibrary($stringPathToDLL.ToString())
$ptrModule = $Win32GetModuleHandle::GetModuleHandle($stringPathToDLL.ToString())
# Set severity code
Switch ($Severity)
"Informational" { $code = 1073741824 }
"Warning" { $code = 2147483648 }
"Error" { $code = 3221225472 }
# Format the message
$result = $Win32FormatMessage::FormatMessage($flags, $ptrModule, $Code -bor $MessageID, 0, $stringOutput, $sizeOfBuffer, $stringArrayInput)
if ($result -gt 0)
# Add insert strings to message
$objMessage = New-Object System.Object
$objMessage | Add-Member type NoteProperty name MessageString value $stringOutput.ToString().Replace("%11","").Replace("%12","").Replace("%3%4%5%6%7%8%9%10","").Replace("%1",$InsString1).Replace("%2",$InsString2).Replace("%3",$InsString3).Replace("%4",$InsString4).Replace("%5",$InsString5).Replace("%6",$InsString6).Replace("%7",$InsString7).Replace("%8",$InsString8).Replace("%9",$InsString9).Replace("%10",$InsString10)
Return $objMessage
# SQL query for component status
$Query = "
when Status = 0 then 'OK'
when Status = 1 then 'Warning'
when Status = 2 then 'Critical'
End as 'Status',
when State = 0 then 'Stopped'
when State = 1 then 'Started'
when State = 2 then 'Paused'
when State = 3 then 'Installing'
when State = 4 then 'Re-installing'
when State = 5 then 'De-installing'
End as 'State',
When AvailabilityState = 0 then 'Online'
When AvailabilityState = 3 then 'Offline'
When AvailabilityState = 4 then 'Unknown'
End as 'AvailabilityState',
from vSMS_ComponentSummarizer
where TallyInterval = N'$TallyInterval'
and MachineName = '$SiteServer'
and SiteCode = '$SiteCode '
and Status in (1,2)
Order by Status,ComponentName
$Results = Get-SQLData Query $Query
# Convert results to HTML
$HTML = $Results |
ConvertTo-Html Property "ComponentName","ComponentType","Status","State","AvailabilityState","Infos","Warnings","Errors" Head $Style Body "<h2>Components in a Warning or Error State</h2>" CssUri "" |
$HTML = $HTML + "<h2></h2><h2>Last $SMCount Error or Warning Status Messages for…</h2>"
If ($Results)
# Start PInvoke Code
$sigFormatMessage = @'
public static extern uint FormatMessage(uint flags, IntPtr source, uint messageId, uint langId, StringBuilder buffer, uint size, string[] arguments);
$sigGetModuleHandle = @'
public static extern IntPtr GetModuleHandle(string lpModuleName);
$sigLoadLibrary = @'
public static extern IntPtr LoadLibrary(string lpFileName);
$Win32FormatMessage = Add-Type MemberDefinition $sigFormatMessage name "Win32FormatMessage" namespace Win32Functions PassThru Using System.Text
$Win32GetModuleHandle = Add-Type MemberDefinition $sigGetModuleHandle name "Win32GetModuleHandle" namespace Win32Functions PassThru Using System.Text
$Win32LoadLibrary = Add-Type MemberDefinition $sigLoadLibrary name "Win32LoadLibrary" namespace Win32Functions PassThru Using System.Text
#End PInvoke Code
$sizeOfBuffer = [int]16384
$stringArrayInput = {"%1","%2","%3","%4","%5", "%6", "%7", "%8", "%9"}
$flags = 0x00000800 -bor 0x00000200
$stringOutput = New-Object System.Text.StringBuilder $sizeOfBuffer
# Process each resulting component
Foreach ($Result in $Results)
# Query SQL for status messages
$Component = $Result.ComponentName
$SMQuery = "
top $SMCount
CASE smsgs.Severity
WHEN -1073741824 THEN 'Error'
WHEN 1073741824 THEN 'Informational'
WHEN -2147483648 THEN 'Warning'
ELSE 'Unknown'
END As 'SeverityName',
case smsgs.MessageType
WHEN 256 THEN 'Milestone'
WHEN 512 THEN 'Detail'
WHEN 768 THEN 'Audit'
WHEN 1024 THEN 'NT Event'
ELSE 'Unknown'
END AS 'Type',
from v_StatusMessage smsgs
join v_StatMsgWithInsStrings smwis on smsgs.RecordID = smwis.RecordID
join v_StatMsgModuleNames modNames on smsgs.ModuleName = modNames.ModuleName
where smsgs.MachineName = '$SiteServer'
and smsgs.Component = '$Component'
and smsgs.Severity in ('-1073741824','-2147483648')
Order by smsgs.Time DESC
$StatusMsgs = Get-SQLData Query $SMQuery
# Put desired fields into an object for each result
$StatusMessages = @()
foreach ($Row in $StatusMsgs)
$Params = @{
MessageID = $Row.MessageID
DLL = $Row.MsgDLLName
Severity = $Row.SeverityName
InsString1 = $Row.InsString1
InsString2 = $Row.InsString2
InsString3 = $Row.InsString3
InsString4 = $Row.InsString4
InsString5 = $Row.InsString5
InsString6 = $Row.InsString6
InsString7 = $Row.InsString7
InsString8 = $Row.InsString8
InsString9 = $Row.InsString9
InsString10 = $Row.InsString10
$Message = Get-StatusMessage @params
$StatusMessage = New-Object psobject
Add-Member InputObject $StatusMessage Name Severity MemberType NoteProperty Value $Row.SeverityName
Add-Member InputObject $StatusMessage Name Type MemberType NoteProperty Value $Row.Type
Add-Member InputObject $StatusMessage Name SiteCode MemberType NoteProperty Value $Row.SiteCode
Add-Member InputObject $StatusMessage Name "Date / Time" MemberType NoteProperty Value $Row.Time
Add-Member InputObject $StatusMessage Name System MemberType NoteProperty Value $Row.MachineName
Add-Member InputObject $StatusMessage Name Component MemberType NoteProperty Value $Row.Component
Add-Member InputObject $StatusMessage Name Module MemberType NoteProperty Value $Row.ModuleName
Add-Member InputObject $StatusMessage Name MessageID MemberType NoteProperty Value $Row.MessageID
Add-Member InputObject $StatusMessage Name Description MemberType NoteProperty Value $Message.MessageString
$StatusMessages += $StatusMessage
# Add to the HTML code
$HTML = $HTML + (
$StatusMessages |
ConvertTo-Html Property "Severity","Date / Time","MessageID","Description" Head $Style Body "<h2>$Component</h2>" CssUri "" |
# Fire the email
Send-MailMessage @EmailParams Body $Html BodyAsHtml

Create Collections for SCCM Client Installation Failures by Error Code

Ok, so in a perfect SCCM world you would never have any SCCM client installation failures and this post would be totally unnecessary. But in the real world, you are very likely to have a number of systems that fail to install the SCCM client and the reasons can be many.

To identify such systems, it can be helpful to create collections for some of the common client installation failure codes so you can easily see and report on which type of installation failures you are experiencing and the number of systems affected.

To identify the installation failure error codes you have in your environment for Windows systems, run the following SQL query against the SCCM database:

	Count(cdr.Name) as 'Count',
	cdr.CP_LastInstallationError as 'Last Installation Error Code'
from v_CombinedDeviceResources cdr
	cdr.CP_LastInstallationError is not null
	and cdr.IsClient = 0
	and cdr.DeviceOS like '%Windows%'
group by cdr.CP_LastInstallationError
order by 'Count' desc
Client installation error counts

Next simply create a collection for each error code using the following WQL query, changing the LastInstallationError value to the relevant error code:

from SMS_R_System as SYS 
Inner Join SMS_CM_RES_COLL_SMS00001 as COL on SYS.ResourceID = COL.ResourceID  
Where COL.LastInstallationError = 53 
And (SYS.Client = 0  Or SYS.Client is null)

Error codes are all fine and dandy, but unless you have an error code database in your head you’ll want to translate those codes to friendly descriptions. To do that, I use a PowerShell function I created that pulls the description from the SrsResources.dll which you can find in any SCCM console installation. There’s more than one way to translate error codes though – see my blog post here. Better yet, create yourself an error code SQL database which you can join to in your SQL queries and is super useful for reporting purposes – see my post here.

Anyway, once you’ve translated the error codes, you can name your collections with them for easy reference:

Client installation failure collections

Now comes the hard part – figuring out how to fix those errors and working through all the affected systems ūüė¨

Query for 32-bit or 64-bit Versions of Microsoft Office with ConfigMgr

Quick post – I needed to query for 64-bit versions of Microsoft Office installed on our clients. Usually, the 32-bit version gets installed as this is Microsoft’s recommendation due to add-in compatibility etc. But in some cases, the 64-bit version is required to take advantage of additional RAM. I couldn’t find anything useful online to distinguish between the x86 and x64 versions of Office using inventory data from ConfigMgr, but I found that the product code GUID of the Office product actually contains this information (see here).

So I put together the following SQL query which extracts some additional data about Office from the GUID, including the Release Version (ie RTM, SP1 etc), Release Type (ie Volume License, Trial etc) and the Bit Version.

This should work for any version of Office, but only for the MSI installer (ie not Click-to-Run).

Some example results:


  sys.Name0 as 'Device Name',
  sof.ProductName0 as 'Product Name',
  sof.ProductVersion0 as 'Product Version',
  sof.InstallDate0 as 'Installation Date',
  sof.Language0 as 'Language Code',
  sof.SoftwareCode0 as 'Software Code',
  'Release Version' =
    Case substring(sof.SoftwareCode0,2,1)
      When '0' Then 'Prior to Beta 1'
      When '1' Then 'Beta 1'
      When '2' Then 'Beta 2'
      When '3' Then 'RC0'
      When '4' Then 'RC1 / OEM Preview'
      When '9' Then 'RTM'
      When 'A' Then 'SP1'
      When 'B' Then 'SP2'
      When 'C' Then 'SP3'
      Else 'Unknown'
  'Release Type' =
    Case substring(sof.SoftwareCode0,3,1)
      When '0' Then 'Volume License'
      When '1' Then 'Retail / OEM'
      When '2' Then 'Trial'
      When '5' Then 'Download'
      Else 'Unknown'
  substring(sof.SoftwareCode0,4,2) as 'Major Version',
  substring(sof.SoftwareCode0,6,4) as 'Minor Version',
  'Bit Version' =
    Case substring(sof.SoftwareCode0,21,1)
      When '0' Then '32-bit'
      When '1' Then '64-bit'
      Else 'Unknown'
inner join v_R_System sys on sof.ResourceID = sys.ResourceID
where sof.SoftwareCode0 like '%0ff1ce%'
-- Querying for Office Professional Plus --
and sof.ProductName0 like '%Professional Plus%'
-- Querying for 64-bit Office (0 = x86, 1 = x64) --
and substring(sof.SoftwareCode0,21,1) = 1
Order by sys.Name0 

Inventory Local Administrator Privileges with PowerShell and ConfigMgr

Any security-conscious enterprise will want to have visibility of which users have local administrator privilege on any given system, and if you are an SCCM administrator then the job of gathering this information will likely be handed to you!

However, this task may not be as simple as it seems. Gathering the membership of the local administrators group is one thing, but perhaps more important to know is whether the primary user of a system has administrator privileges. If that user is a member of a group that has been added to the local administrators group, then it isn’t immediately obvious whether they actually have administrator rights without also checking the membership of that group. And what if there are further nested groups – ie the user is a member of a group that’s a member of a group that’s a member of the local administrators group?! Obviously things can get complicated here, making reporting and compliance checking a challenge.

Thankfully, PowerShell can handle complication quite nicely, and ConfigMgr is more than capable as a both a delivery vehicle and a reporting mechanism, so the good news is – we can do this!

The following solution uses PowerShell to gather local administrator information and stamp it to the local registry. A Compliance item in SCCM is used as the delivery vehicle for the script and then RegKeyToMof is used to update the hardware inventory classes in SCCM to gather this information from the client’s registry into the SCCM database, where we can query and report on it.

Gathering Local Administrator Information with PowerShell

To start with, let’s have a look at some of the PowerShell code and the information we will gather with it.

First, we need to identify who is the primary user of the system. Since the script is running locally on the client computer, we will not use User Device Affinity. True, UDA information is stored in WMI in the CCM_UserAffinity class, in the¬†ROOT\CCM\Policy\Machine\ActualConfig namespace. ¬†But this class can contain multiple instances so you can’t always determine the primary user that way.

A better way is to use the SMS_SystemConsoleUsage class in the ROOT\cimv2\sms namespace and query the TopConsoleUser property. This will give you the user account who has had the most interactive logons on the system and for the most part will indicate who the primary user is.

$TopConsoleUser = Get-WmiObject -Namespace ROOT\cimv2\sms -Class SMS_SystemConsoleUsage -Property TopConsoleUser -ErrorAction Stop | Select -ExpandProperty TopConsoleUser

Next, to find if the user is a local admin or not, we will not simply query the local administrator group membership and check if the user is in there. Instead we will create a WindowsIdentity object in .Net and run a method called HasClaim(). I describe this more in a previous blog, but using this method we can determine if the user has local administrator privilege whether through direct membership or through a nested group.

$ID = New-Object Security.Principal.WindowsIdentity -ArgumentList $TopConsoleUser
$IsLocalAdmin = $ID.HasClaim('','S-1-5-32-544')

The SID for the local admin group (S-1-5-32-544) is used as this is the same across all systems. This will only work for domain accounts as it uses kerberos to create the identity.

Now we will also get the local administrator group membership using the following code (more .Net stuff), and filter just the SamAccountNames.

Add-Type -AssemblyName System.DirectoryServices.AccountManagement -ErrorAction Stop
$ContextType = [System.DirectoryServices.AccountManagement.ContextType]::Machine
$PrincipalContext = New-Object -TypeName System.DirectoryServices.AccountManagement.PrincipalContext -ArgumentList $ContextType, $($env:COMPUTERNAME) -ErrorAction Stop
$IdentityType = [System.DirectoryServices.AccountManagement.IdentityType]::Name
$GroupPrincipal = [System.DirectoryServices.AccountManagement.GroupPrincipal]::FindByIdentity($PrincipalContext, $IdentityType, ‚ÄúAdministrators‚ÄĚ)
$LocalAdminMembers = $GroupPrincipal.Members | select -ExpandProperty SamAccountName | Sort-Object

Next, if the user is a local admin through nested group membership, I will call a custom function which will check the nested group membership within the local admin group, for the user account. Let’s say that Group B is a member of Group A, which is a member of the local administrators group. We will check the membership of both Groups B and A to see which ones the user is a member of, and therefore which group/s is effectively giving the user administrator privilege. We do this by querying the $GroupPrincipal object created in the previous code. The custom function will query nested membership up to 3 levels deep.

Now I will query the Install Date for the operating system, since in some cases where a machine is newly built, the TopConsoleUser may not yet be the primary user of the system, but the admin who built the machine, for example. This date helps to identify any such systems.

[datetime]$InstallDate = [System.Management.ManagementDateTimeConverter]::ToDateTime($(Get-WmiObject win32_OperatingSystem -Property InstallDate -ErrorAction Stop | Select -ExpandProperty InstallDate)) | Get-date -Format 'yyyy-MM-dd HH:mm:ss'

Now we gather all this information into a datatable, and call another custom function to write it to the local registry. I use the following registry key, but you can change this in the script if you wish:


The script will create the key if it doesn’t exist.

Here’s an example of the kind of data that will be gathered:


You can see in this example, that my user account is a local administrator both by direct membership and through nested groups. The actual groups that grant this right are listed in the NestedGroupMembership property.

Create a Compliance Item

Now lets go ahead and create a compliance item in SCCM to run this script.

In the Console, navigate Assets and Compliance > Compliance Settings > Configuration Items.

Click Create Configuration Item


Click Next and select which OS’s you will target. ¬†Remember the Windows XP and Server 2003 may not have PowerShell installed.

Click Next again, then click New to create a new setting.

Choose Script as the setting type, and String as the data type.


Now we need to add the scripts.  You can download both the discovery and remediation scripts from my Github repo here:

Click Add Script and paste or open the relevant script for each. Make sure Windows Powershell is selected as the script language.

The discovery script simply checks whether the script has been run in the last 15 minutes, and if not returns non-compliant.  This allows the script to run according to the schedule you define for it, ie once a day or once a week etc, to keep the information up-to-date in the registry.

The remediation script does the hard work ūüôā

Click OK to close the Create Setting window.

Click Next, then click New to create a new Compliance Rule as follows:


Click OK to close, then Next, Next and Close to finish.

Create a Configuration Baseline

Click on Configuration Baselines and Create Configuration Baseline to create a new baseline.

Give it a name, click Add and add the Configuration Item you just created.


Click OK to close.

Deploy the Baseline

Right-click the baseline and choose Deploy. Make sure to remediate noncompliance and select the collection you wish to target.


Update SCCM Hardware Inventory

Creating the MOF Files

For this part you will need the excellent RegKeyToMOF utility, which you can download from here:

You will also need to do this on a machine that has either run the remediation script to create the registry keys, or has run the configuration baseline.

Open RegKeyToMOF and browse to the registry key:


You can deselect the ‘Enable 64bits …’ option as the registry key is not located in the WOW6432Node.

Click Save MOF to save the required files.


Copy the SMSDEF.mof and the CM12Import.mof to your SCCM site server.

Update Client Settings

In the SCCM console, navigate Administration > Site Configuration > Client Settings. Open your default client settings and go to the Hardware Inventory page.

Click Set Classes…, then Import…

Browse to the CM12Import.mof and click Import.


Close the Client Settings windows.

Update Configuration.mof

Now open your configuration.mof file at <ConfigMgr Installation Directory> \inboxes\clifiles.src\hinv.

In the section at the bottom for adding extensions, which starts like this…

// Added extensions start

…paste the contents of the SMSDEF.mof file. ¬†Save and close the file.


Now that you’ve deployed the configuration item and updated the SCCM hardware inventory, a new view called dbo.v_GS_LocalAdminInfo0 has been added to the SCCM database. Note that initially there will be no data here until your clients have updated their policies, ran the configuration baseline, and ran the hardware inventory cycle.

You can query using the Queries node in the SCCM console…


…or create yourself a custom SCCM report, create an Excel report with a SQL data connection, query the SCCM database with PowerShell – whatever method you need or prefer.

Here is a sample SQL query that will query the view and add some client health data and the chassis type to help distinguish between desktop, laptops, servers etc.

  ComputerName0 as 'ComputerName',
  Case When enc.ChassisTypes0 = 1 then 'Other'
    when enc.ChassisTypes0 = 2 then 'Unknown'
    when enc.ChassisTypes0 = 3 then 'Desktop'
    when enc.ChassisTypes0 = 4 then 'Low Profile Desktop'
    when enc.ChassisTypes0 = 5 then 'Pizza Box'
    when enc.ChassisTypes0 = 6 then 'Mini Tower'
    when enc.ChassisTypes0 = 7 then 'Tower'
    when enc.ChassisTypes0 = 8 then 'Portable'
    when enc.ChassisTypes0 = 9 then 'Laptop'
    when enc.ChassisTypes0 = 10 then 'Notebook'
    when enc.ChassisTypes0 = 11 then 'Hand Held'
    when enc.ChassisTypes0 = 12 then 'Docking Station'
    when enc.ChassisTypes0 = 13 then 'All in One'
    when enc.ChassisTypes0 = 14 then 'Sub Notebook'
    when enc.ChassisTypes0 = 15 then 'Space-Saving'
    when enc.ChassisTypes0 = 16 then 'Lunch Box'
    when enc.ChassisTypes0 = 17 then 'Main System Chassis'
    when enc.ChassisTypes0 = 18 then 'Expansion Chassis'
    when enc.ChassisTypes0 = 19 then 'SubChassis'
    when enc.ChassisTypes0 = 20 then 'Bus Expansion Chassis'
    when enc.ChassisTypes0 = 21 then 'Peripheral Chassis'
    when enc.ChassisTypes0 = 22 then 'Storage Chassis'
    when enc.ChassisTypes0 = 23 then 'Rack Mount Chassis'
    when enc.ChassisTypes0 = 24 then 'Sealed-Case PC'
    else 'Unknown'
  End as 'Chassis Type',
  TopConsoleUser0 as 'Primary User',
  TopConsoleUserIsAdmin0 as 'Primary User is Admin?',
  AdminGroupMembershipType0 as 'Primary User Local Admin Group Membership Type',
  LocalAdminGroupMembership0 as 'Local Admin Group Membership',
  NestedGroupMembership0 as 'Primary User Local Admin Nested Group Membership',
  OSAgeInDays0 as 'OS Age (days)',
  OSInstallDate0 as 'OS Installation Date',
  LastUpdated0 as 'Last Updated Date',
  la.TimeStamp as 'HW Inventory Date',
from dbo.v_GS_LocalAdminInfo0 la
join v_R_System sys on la.ComputerName0 = sys.Name0
left join v_GS_SYSTEM_ENCLOSURE enc on sys.ResourceID = enc.ResourceID
left join v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID
where ComputerName0 is not null
  and enc.ChassisTypes0 <> 12


PowerShell Custom Class for Querying a SQL Server

Here is a handy custom class I created for PowerShell 5+ that can query an SQL database. The class creates a SQL connection using the System.Data.SqlClient.SqlConnection class in .Net, and gives you full access to this object for information or to manipulate parameters. The class can also query the list of tables and views available in the database to help you prepare your SQL query.

Here’s a quick how-to:

Instantiate the Class

Create an instance of the object. You can create an empty instance like this:

$SQLQuery = [SQLQuery]::new()

You can then specify the SQL Server name and Database name:

$SQLQuery.SQLServer = "SQL-01\Inst_SCCM"
$SQLQuery.Database = "CM_ABC"

And then add a query:

$SQLQuery.Query = "Select top 5 * from v_R_System"

Or you can pass these parameters when you create the object, like:

$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC")


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC","Select top 5 * from v_R_System")

Execute the Query

Now execute the query to return the results:


The result will be displayed in the console window, or you can pipe to GridView for easier viewing:

$SQLQuery.Execute() | Out-GridView


The query results will be stored to the object in the Result parameter so you can retrieve them later.


The results are stored in a DataTable format with rows and columns which is an ideal format for SQL query results.

Load a Query from a File

You can load in a SQL query from a “.sql” file like so, passing the location of the file to the method:


Then execute the query as before.

Change the Connection String

By default, the connection string will be created for you, but you can add your own custom connection string using the ConnectionString parameter:

$SQLQuery.ConnectionString = "Server=SQL-01\inst_sccm;Database=CM_ABC;Integrated Security=SSPI;Connection Timeout=5"

Timeout Values

There are two timeout parameters which can be set:


The ConnectionTimeout parameter is the maximum time in seconds PowerShell will try to open a connection to the SQL server.

The CommandTimeout parameter is the maximum time in seconds PowerShell will wait for the SQL query to execute.

Get a List of Views or Tables in the Database

The following two methods can be used to retrieve a list of views or tables in the database:


The views or tables will be stored to the parameter of the same name in the object, so you can retrieve them again later.

You can filter the list to search for a particular view or table, or group of. On the command line you could do:

$SQLQuery.Views.Rows | where {$_.Name -match "v_Client"}

Or as a one-liner:

$SQLQuery.ListViews().Where({$_.Name -match "v_Client"})

You could also output to GridView for filtering:

$SQLQuery.ListViews() | Out-GridView


Hide the Results

By default, the Execute(), ListViews() and ListTables() methods will return the results to the console after execution. You can turn this off by setting the DisplayResults parameter to $False. This scenario may be useful for scripting where you may not wish to display the results right away but simply have them available in a variable.

$SQLQuery.DisplayResults = $False

Access the SQL Objects

You can access the SQLConnection object, or the SQLCommand object to view or set parameters, or execute any of the methods contained in those objects. These objects are only available once the SQL query has been executed.



You can re-use the same SQLQuery object to run other queries; only the results for the most recent query will be stored in the object itself.

SQLQuery Custom Class

class SQLQuery
# Properties
[int]$ConnectionTimeout = 5
[int]$CommandTimeout = 600
# Connection string keywords:
hidden $SQLReader
[bool]$DisplayResults = $True
# Constructor -empty object
SQLQuery ()
# Constructor – sql server and database
SQLQuery ([String]$SQLServer,[String]$Database)
$This.SQLServer = $SQLServer
$This.Database = $Database
# Constructor – sql server, database and query
SQLQuery ([String]$SQLServer,[String]$Database,[string]$Query)
$This.SQLServer = $SQLServer
$This.Database = $Database
$This.Query = $Query
# Method
If (Test-Path $Path)
If ([IO.Path]::GetExtension($Path) -ne ".sql")
throw [System.IO.FileFormatException] "'$Path' does not have an '.sql' extension'"
[String]$This.Query = Get-Content Path $Path Raw ErrorAction Stop
[String]$This.QueryFile = $Path
throw [System.IO.FileNotFoundException] "'$Path' not found"
# Method
[Object] Execute()
If ($This.SQLConnection)
If ($This.ConnectionString)
$This.ConnectionString = "Server=$($This.SQLServer);Database=$($This.Database);Integrated Security=SSPI;Connection Timeout=$($This.ConnectionTimeout)"
$This.SQLConnection = [System.Data.SqlClient.SqlConnection]::new()
$This.SQLConnection.ConnectionString = $This.ConnectionString
return $(Write-host $_ ForegroundColor Red)
$This.SQLCommand = $This.SQLConnection.CreateCommand()
$This.SQLCommand.CommandText = $This.Query
$This.SQLCommand.CommandTimeout = $This.CommandTimeout
$This.SQLReader = $This.SQLCommand.ExecuteReader()
return $(Write-host $_ ForegroundColor Red)
If ($This.SQLReader)
$This.Result = [System.Data.DataTable]::new()
If ($This.DisplayResults)
Return $This.Result
Return $null
# Method
[Object] ListTables()
If ($This.ConnectionString)
$TableConnectionString = $This.ConnectionString
$TableConnectionString = "Server=$($This.SQLServer);Database=$($This.Database);Integrated Security=SSPI;Connection Timeout=$($This.ConnectionTimeout)"
$TableSQLConnection = [System.Data.SqlClient.SqlConnection]::new()
$TableSQLConnection.ConnectionString = $TableConnectionString
return $(Write-host $_ ForegroundColor Red)
$TableQuery = "Select Name from Sys.Tables Order by Name"
$TableSQLCommand = $TableSQLConnection.CreateCommand()
$TableSQLCommand.CommandText = $TableQuery
$TableSQLCommand.CommandTimeout = $This.CommandTimeout
$TableSQLReader = $TableSQLCommand.ExecuteReader()
return $(Write-host $_ ForegroundColor Red)
If ($TableSQLReader)
$This.Tables = [System.Data.DataTable]::new()
If ($This.DisplayResults)
Return $This.Tables
Return $null
# Method
[Object] ListViews()
If ($This.ConnectionString)
$ViewConnectionString = $This.ConnectionString
$ViewConnectionString = "Server=$($This.SQLServer);Database=$($This.Database);Integrated Security=SSPI;Connection Timeout=$($This.ConnectionTimeout)"
$ViewSQLConnection = [System.Data.SqlClient.SqlConnection]::new()
$ViewSQLConnection.ConnectionString = $ViewConnectionString
return $(Write-host $_ ForegroundColor Red)
$ViewQuery = "Select Name from Sys.Views Order by Name"
$ViewSQLCommand = $ViewSQLConnection.CreateCommand()
$ViewSQLCommand.CommandText = $ViewQuery
$ViewSQLCommand.CommandTimeout = $This.CommandTimeout
$ViewSQLReader = $ViewSQLCommand.ExecuteReader()
return $(Write-host $_ ForegroundColor Red)
If ($ViewSQLReader)
$This.Views = [System.Data.DataTable]::new()
If ($This.DisplayResults)
Return $This.Views
Return $null

view raw
hosted with ❤ by GitHub

Export / Backup Compliance Setting Scripts with PowerShell

In my SCCM environment I have a number of Compliance Settings that use custom scripts for discovery and remediation, and recently it dawned on me that a lot of time has been spent on these and it would be good to create a backup of those scripts. It would also be useful to be able to export the scripts so they could be edited and tested before being updated in the Configuration Item itself. So I put to together this PowerShell script which does just that!

The Configuration Item scripts are stored in an XML definition, and this can be read from the SCCM database directly and parsed with PowerShell, so that’s what this script does. It will load all the Configuration Items into a datatable from a SQL query, then go through each one looking for any settings that have scripts defined. These scripts will be exported in their native file format.

You could then edit those scripts, or add the export location to your file/folder backup for an extra level of protection for your hard work!

Here you can see an example of the output for my “Java Settings” Configuration item. A subdirectory is created for the current package version, then subdirectories under that for each Configuration setting, then the discovery and remediation scripts for that setting.

Exported Configuration Item Scripts

Note that the script will only process Compliance Items with a CIType_ID of 3, which equates to the Operating System type you will see in the SCCM console for the Configuration Item, which is the type that may use a script as the discovery source.


Exports all scripts (discovery and remediation) used in all SCCM Compliance Setting Configuration Items
This script connects to the SCCM database to retrieve all Compliance Setting Configuration Items. It then processes each item looking for
discovery and remediation scripts for the current (latest) version. It will export any script found into a directory structure.
Requirements – 'db_datareader' permission to the SCCM SQL database with the account running this script.
Parameters – set the parameters below as required
# Root directory to export the scripts to
$RootDirectory = "C:\temp"
# Name of the subdirectory to create
$SubDirectory = "Compliance_Settings_CI_Scripts"
# SCCM SQL Server (and instance where applicable)
$SQLServer = 'mysqlserver\inst_sccm'
# SCCM Database name
$Database = 'CM_ABC'
# Create the subdirectory if doesn't exist
If (!(Test-Path "$RootDirectory\$SubDirectory"))
New-Item Path "$RootDirectory" Name "$SubDirectory" ItemType container | Out-Null
# Define the SQL query
$Query = "
Select * from dbo.v_ConfigurationItems
where CIType_ID = 3
and IsLatest = 'true'"
# Run the SQL query
$connectionString = "Server=$SQLServer;Database=$Database;Integrated Security=SSPI"
$connection = New-Object TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$ComplianceItems = New-Object TypeName 'System.Data.DataTable'
# Process each compliance item returned
$ComplianceItems | foreach {
# Set some variables
$PackageVersion = "v $($_.SDMPackageVersion)"
[xml]$Digest = $_.SDMPackageDigest
$CIName = $Digest.ChildNodes.OperatingSystem.Annotation.DisplayName.Text
# Create subdirectory structure if doesn't exist: configuration item name > current package version
If (!(Test-Path "$RootDirectory\$SubDirectory\$CIName"))
New-Item Path "$RootDirectory\$SubDirectory" Name "$CIName" ItemType container | Out-Null
If (!(Test-Path "$RootDirectory\$SubDirectory\$CIName\$PackageVersion"))
New-Item Path "$RootDirectory\$SubDirectory\$CIName" Name "$PackageVersion" ItemType container | Out-Null
# Put each compliance item setting in XML format into an arraylist for quick processing
$Settings = New-Object System.Collections.ArrayList
$Digest.DesiredConfigurationDigest.OperatingSystem.Settings.RootComplexSetting.SimpleSetting | foreach {
# Process each compliance item setting
$Settings | foreach {
# Only process if this setting has a script source
If ($_.SimpleSetting.ScriptDiscoverySource)
# Set some variables
$SettingName = $_.SimpleSetting.Annotation.DisplayName.Text
$DiscoveryScriptType = $_.SimpleSetting.ScriptDiscoverySource.DiscoveryScriptBody.ScriptType
$DiscoveryScript = $_.SimpleSetting.ScriptDiscoverySource.DiscoveryScriptBody.'#text'
$RemediationScriptType = $_.SimpleSetting.ScriptDiscoverySource.RemediationScriptBody.ScriptType
$RemediationScript = $_.SimpleSetting.ScriptDiscoverySource.RemediationScriptBody.'#text'
# Create the subdirectory for this setting if doesn't exist
If (!(Test-Path "$RootDirectory\$SubDirectory\$CIName\$PackageVersion\$SettingName"))
New-Item "$RootDirectory\$SubDirectory\$CIName\$PackageVersion" Name $SettingName ItemType container Force | Out-Null
# If a discovery script is found
If ($DiscoveryScript)
# Set the file extension based on the script type
Switch ($DiscoveryScriptType)
Powershell { $Extension = "ps1" }
JScript { $Extension = "js" }
VBScript { $Extension = "vbs" }
# Export the script to a file
New-Item Path "$RootDirectory\$SubDirectory\$CIName\$PackageVersion\$SettingName" Name "Discovery.$Extension" ItemType file Value $DiscoveryScript Force | Out-Null
# If a remediation script is found
If ($RemediationScript)
# Set the file extension based on the script type
Switch ($RemediationScriptType)
Powershell { $Extension = "ps1" }
JScript { $Extension = "js" }
VBScript { $Extension = "vbs" }
# Export the script to a file
New-Item Path "$RootDirectory\$SubDirectory\$CIName\$PackageVersion\$SettingName" Name "Remediation.$Extension" ItemType file Value $RemediationScript Force | Out-Null
<# For reference: CIType_IDs
1 Software Updates
2 Baseline
3 OS
4 General
5 Application
6 Driver
7 Uninterpreted
8 Software Updates Bundle
9 Update List
10 Application Model
11 Global Settings
13 Global Expression
14 Supported Platform
21 Deployment Type
24 Intend Install Policy
25 DeploymentTechnology
26 HostingTechnology
27 InstallerTechnology
28 AbstractConfigurationItem
60 Virtual Environment

Automatically Set SQL MaxServerMemory on Cluster Failover with PowerShell

On a two-node Windows Failover Cluster, I have two SQL instances installed. Each instance runs on its own node in the cluster, so that it can make maximum use of the available memory on that server. However, when a failover occurs, it is necessary to reduce the maximum server memory setting for both instances, so that they can share the available memory on the one server. Rather than have to do that manually, however, I decided to automate the process using PowerShell and the Windows Task Scheduler, and here’s how.

Note: the script will work for two SQL instances in a cluster. For additional instances, the script will need to be updated accordingly.

  1. Save the PowerShell script (download from Technet Gallery) to each server in the cluster.
  2. Update the PowerShell script setting the required variables in the parameters section, such as the log file location, the SQL instance names, the SQL service names, the path to the SQL SMO dll, the maximum server memory limit you want to set, and the timeout period.
  3. Create a scheduled task on each server, running as an account that has the appropriate permissions on each instance. Add 2 triggers – one for each SQL instance – and use the event trigger. You can use event ID 101, for example, to identify when a SQL instance becomes active on that node. As the source, use the “SQLAgent$<instancename>”.


As the task action, use Start a program:

  • Program: Powershell.exe
  • Arguments:¬†-ExecutionPolicy Bypass -WindowStyle Hidden -File “<PathToScript>\Set-SQLClusterMaximumMemory.ps1”

When a SQL instance fails over to the other node, the script will be triggered and will set the maximum server memory limit for both instances on that node.


The script will also log the process:


Automation. Gotta love it ūüôā

Forcing a ConfigMgr Client to Send a New CCMEval Report

In order to maintain a healthy ConfigMgr environment, it is important to know that your clients have successfully run the Configuration Manager Health Evaluation task and reported the results to the Site server. ¬†Sometimes you will find a number of systems that have not reported any health status to the Site server. ¬†In the Devices node of the ConfigMgr Console, you will find “No Results” for the Client Check Result, and the Client Check Detail tab displays nothing, even though the system may be active.



To identify the list of active systems that either have not reported health evaluation results, or have failed the evaluation, I use the following SQL query:

sys.Name0 as 'Computer Name',
sys.User_Name0 as 'User Name',
case when summ.ClientActiveStatus = 0 then 'Inactive'
 when summ.ClientActiveStatus = 1 then 'Active'
 end as 'ClientActiveStatus',
case when summ.IsActiveDDR = 0 then 'Inactive'
 when summ.IsActiveDDR = 1 then 'Active'
 end as 'IsActiveDDR',
case when summ.IsActiveHW = 0 then 'Inactive'
 when summ.IsActiveHW = 1 then 'Active'
 end as 'IsActiveHW',
case when summ.IsActiveSW = 0 then 'Inactive'
 when summ.IsActiveSW = 1 then 'Active'
 end as 'IsActiveSW',
case when summ.ISActivePolicyRequest = 0 then 'Inactive'
 when summ.ISActivePolicyRequest = 1 then 'Active'
 end as 'ISActivePolicyRequest',
case when summ.IsActiveStatusMessages = 0 then 'Inactive'
 when summ.IsActiveStatusMessages = 1 then 'Active'
 end as 'IsActiveStatusMessages',
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
 when LastHealthEvaluationResult = 2 then 'Not Applicable'
 when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
 when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
 when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
 when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
 when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
 end as 'Last Health Evaluation Result',
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when summ.ClientRemediationSuccess = 1 then 'Pass'
 when summ.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
from v_CH_ClientSummary summ
inner join v_R_System sys on summ.ResourceID = sys.ResourceID
where summ.LastEvaluationHealthy in (2,3)
and summ.ClientActiveStatus = 1
order by summ.LastActiveTime Desc

In most cases where the evaluation status reports “Unknown” by this query, you will find that the client has actually run the health evaluation task, it just hasn’t reported the results to the management point for some reason. ¬†I published a PowerShell script previously that lets you view the current health evaluation status on any remote computer by reading the CCMEvalReport.xml file – you can find the script here.

For these “Unknown” status systems, however, I want to force the client to send a health evaluation¬†report to its management point, so I prepared the following PowerShell script to do that. ¬†It can run either against the local computer, or a remote computer. ¬†Admin rights are required on the target system, and if running against the local computer the script must be run as administrator.

The script simply sets the SendAlways flag for CCMEval reports in the registry to “TRUE”, triggers the CM Health Evaluation task to run, waits for it to finish, then changes the SendAlways flag back to “FALSE”. ¬†When the CCMEval program runs with the SendAlways flag set, it will always send the report to the management point even if the client health status has not changed since the last report.

You can verify that from the CcmEval.log on the client:


Within a few minutes you should find that the status for that system has been updated in the ConfigMgr Console with the health evaluation results.

To run the script against the local machine, run PowerShell as administrator and simply do:


To run against a remote computer:

Send-CCMEvalReport -ComputerName PC001

The script also supports verbose output:

Send-CCMEvalReport -ComputerName PC001 -Verbose


Here’s the full code:


        [String]$ComputerName = $env:COMPUTERNAME

# Code to set 'SendAlways' in registry
$SendAlways = {
    $Path = "HKLM:\Software\Microsoft\CCM\CcmEval"

        $null = New-ItemProperty -Path $Path -Name 'SendAlways' -Value $Value -Force -ErrorAction Stop

# Run against local computer
If ($ComputerName -eq $env:COMPUTERNAME)
    If (!([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator"))
        Write-Warning "This cmdlet must be run as administrator against the local machine!"

    Write-Verbose "Enabling 'SendAlways' in registry"
    $Result = Invoke-Command -ArgumentList "TRUE" -ScriptBlock $SendAlways 

    If (!$Result.Exception)
        Write-Verbose "Triggering CM Health Evaluation task"
        Invoke-Command -ScriptBlock { schtasks /Run /TN "Microsoft\Configuration Manager\Configuration Manager Health Evaluation" /I }

        Write-Verbose "Waiting for ccmeval to finish"
        do {} while (Get-process -Name ccmeval -ErrorAction SilentlyContinue)

        Write-Verbose "Disabling 'SendAlways' in registry"
        Invoke-Command -ArgumentList "FALSE" -ScriptBlock $SendAlways
        Write-Error $($Result.Exception.Message)
# Run against remote computer
    Write-Verbose "Enabling 'SendAlways' in registry"
    $Result = Invoke-Command -ComputerName $ComputerName -ArgumentList "TRUE" -ScriptBlock $SendAlways

    If (!$Result.Exception)
        Write-Verbose "Triggering CM Health Evaluation task"
        Invoke-Command -ComputerName $ComputerName -ScriptBlock { schtasks /Run /TN "Microsoft\Configuration Manager\Configuration Manager Health Evaluation" /I }

        Write-Verbose "Waiting for ccmeval to finish"
        do {} while (Get-process -ComputerName $ComputerName -Name ccmeval -ErrorAction SilentlyContinue)

        Write-Verbose "Disabling 'SendAlways' in registry"
        Invoke-Command -ComputerName $ComputerName -ArgumentList "FALSE" -ScriptBlock $SendAlways
        Write-Error $($Result.Exception.Message)