SQL Server Query to Check Last Client Health Check on Specific Machine

There are times when I need to check the last time a machine ran the SCCM client health check. There are several different ways to check this, from the using the console to reports. Since I have SQL Server Management Studio up the majority of the time, I find the easiest way for me is to run the below query. It will output the last time it was reported online, last hardware inventory, last time the client health check was run, and the status of the check.

SELECT S1.Netbios_Name0 AS [Machine Name], S2.LastOnline AS [Last Online], S2.LastHW AS [Last Hardware Inventory], S2.LastHealthEvaluation AS [Last Evaluation Time], S3.HealthCheckDescription AS [Last Evaluation Result] FROM v_R_System AS S1 INNER JOIN v_CH_ClientSummary AS S2 ON S1.ResourceID = S2.ResourceID INNER JOIN v_CH_EvalResults AS S3 ON S1.ResourceID = S3.ResourceID WHERE (S1.Netbios_Name0 = '<<insertmachinename>>')

Available views for SCCM 2012 custom reporting

Microsoft has made it easy to locate a view to use when creating a custom report. On TechNet, they have grouped the reports into to categories such as Inventory Views , Discovery Views, and Software Update views. Below are links to two key categories that I use regularly along with a link to all views. 

Discovery Views - Use to find machines and users discovered by SCCM.


Inventory Views - Use to find installed software, hardware information, and files on machines.


All Views


Automatically upgrade the SCCM 2012 Client

Microsoft regularly releases updates for their System Center products. One of the nice features of SCCM 2012 R2 is the automatic client upgrade feature. By enabling it, all clients throughout the  environment (except clients on slow or unreliable networks, which must upgraded using another method) will automatically upgrade to the latest version. It can also be configured to upgrade within a certain period of time, with the default being within 7 days. Below are the steps to enable this feature.

  1. In the SCCM administrator console, select Administration.
  2. Expand Site Configuration.
  3. Right-click Sites and select Hierarchy Settings.
  4. Select the Automatic Client Upgrade tab.
  5. Check Upgrade client automatically when new client updates are available.
  6. Read the warning and click OK to enable automatic client upgrades.

Queries for Collections for a Specific Machine and All Software In Add/Remove Programs

For a number of years, I have been collecting SQL Server queries that I use regularly. They allow me to get requested data much quicker without having to write a new query each time. Below are two queries that I use often and have saved me a lot of time.

All Collections that a Specific Machine is In

(Replace <<InsertMachineName>> with the name of the machine to check



v_FullCollectionMembership AS S2 ON S1.ResourceID = S2.ResourceID INNER JOIN

v_Collection AS S3 ON S2.CollectionID = S3.CollectionID

WHERE (S1.Netbios_Name0 = '<<InsertMachineName>>')

All Software In Add/Remove Programs on a Specific Machine (x86 and x64)

Replace <<InsertMachineName>> with the name of the machine to check

SELECT S2.DisplayName0 AS 'Display Name', S2.Version0, S2.InstallDate0



WHERE (S1.Netbios_Name0 = 'CM12')


SELECT S2.DisplayName0 'Display Name', S2.Version0, S2.InstallDate0


v_GS_ADD_REMOVE_PROGRAMS_64 AS S2 ON S1.ResourceID = S2.ResourceID

WHERE (S1.Netbios_Name0 = '<<InsertMachineName>>')

ORDER BY 'Display Name'

PowerShell - Add comments to PowerShell

I find it very useful when a scripter includes help in their PowerShell scripts. Simply including examples of how a script works can save a lot of time by showing how something is run and what the output will look like. I found it especially useful when using functions with parameters. Adding comment-based help on required parameters, description information, and examples can show the user how a function works via the command line rather than making them work through the script themselves if they have a problems.

Below is an example of help that I added to a simple function that looks for the specific process on a machine. The included help will assist the user with knowing the correct parameters to use, a description of what the function does, and several examples.

More information about comment based help


SCCM 2012 R2 - Configure Multiple Network Access Accounts

While doing some testing in my lab, I noticed my OS deployments suddenly started failing. Looking at the build logs, I could see that the network access account was being denied access. I had been testing a PowerShell script for creating SCCM 2012 accounts and had somehow locked out the network access account. To prevent this from happening again, I decided to take advantage of a new feature in SCCM 2012 R2 that allows for multiple network access accounts. This should prevent the problem from occurring again.

SCCM 2012 R2 - Using Dynamic Variables during OS Deployments

With the release of R2 for SCCM 2012, Microsoft has introduced Dynamic Variables. These will allow for If/Then statements in the build process without having to have multiple build steps. Dynamic variables can be set via the General steps. Below is a simple example where I am looking for the IsDesktop task sequence variable to be true. If it is, I am setting the OSDDomainOUName variable that will add newly built desktops to the desktop organizational unit.

PowerShell - TechNet Script Browser and Script Analyzer

Microsoft released version 1.3 of the Script Browser and Script Analyzer this month. Incorporating this into the PowerShell ISE will allow you to search the over 9000 scripts in the TechNet script center. It allows you to create favorites, filter by programming language, and configure language settings. It also has the ability to analyze your PowerShell scripts. In the picture gallery, you will see a simple example of my analysis of a Get-Process script. In the script, I am using Get-Process to get more information on the notepad process that is running. In the first picture, I used notepad as a positional parameter without using the actual -Name parameter. In poorly written scripts this can create issues so the analyzer generated a warning. In the second picture, I actually used the -Name parameter followed by the process name (notepad). As you can see, the analyzer no longer generated a warning.

Link to Download of Script Browser & Script Analyzer