Get-CimInstance – the WMI Champion

Quick one – inspired by today’s Power Tip on Powershell.com, I ran a comparison of different methods of getting data from WMI on a remote computer and the execution time of each.  The results were interesting, with Get-CimInstance the clear winner.

I retrieved the full property list for the Win32_Processor class from a remote computer with a ~290ms latency.

capture


$ComputerName = "remoteserver-01"

## 4.54 Seconds # Get-CimInstance
[math]::Round($(Measure-Command -Expression {Get-CimInstance -ClassName Win32_Processor -ComputerName $ComputerName -Property *} |
    Select -ExpandProperty TotalSeconds),2)

## 4.59 Seconds # Get-CimInstance in WSMan Session
[math]::Round($(Measure-Command -Expression {
$option = New-CimSessionOption -Protocol Wsman
$session = New-CimSession -SessionOption $option -ComputerName $ComputerName
Get-CimInstance -ClassName Win32_Processor -CimSession $session -Property *
Remove-CimSession -CimSession $session
}| Select -ExpandProperty TotalSeconds),2)

## 7.68 Seconds # Invoke-Command / Get-CimInstance
[math]::Round($(Measure-Command -Expression {invoke-command -ComputerName $ComputerName -scriptblock {Get-CimInstance -ClassName Win32_Processor -Property *}} |
    Select -ExpandProperty TotalSeconds),2)

## 8.37 Seconds # Invoke-Command / Get-WMIObject
[math]::Round($(Measure-Command -Expression {invoke-command -ComputerName $ComputerName -scriptblock {Get-WmiObject -Class Win32_Processor -Property *}} |
    Select -ExpandProperty TotalSeconds),2)

## 11.04 Seconds # Get-CimInstance in Dcom session
[math]::Round($(Measure-Command -Expression {
$option = New-CimSessionOption -Protocol Dcom
$session = New-CimSession -SessionOption $option -ComputerName $ComputerName
Get-CimInstance -ClassName Win32_Processor -CimSession $session -Property *
Remove-CimSession -CimSession $session
}| Select -ExpandProperty TotalSeconds),2)

## 15.31 Seconds # Get-WmiObject
[math]::Round($(Measure-Command -Expression {Get-WmiObject -Class Win32_Processor -ComputerName $ComputerName -Property *} |
    Select -ExpandProperty TotalSeconds),2)

Prompting the End-User during ConfigMgr Application Installs

As a Configuration Manager administrator, from time to time I have to deploy an application where I need to notify the end-user of something before the installation begins. A recent example was a plugin for IE that would fail to install if Internet Explorer was running at the time. I can force-ably kill the running process of course, but that’s not necessarily a nice experience for the user – without warning their browser and any open tabs get closed. So better to notify them first, and give them a chance to close the application themselves and save any work. Rather than email each targeted user and warn them to close Internet Explorer before the plugin installs (which they probably ignore or forget anyway), I wanted the installation process to handle that by some kind of prompt.

I could create a script wrapper for the plugin but that would necessitate running in the user context to display interactively. An easier way is simply to install it using a task sequence with some additional steps that will prompt the user first, kill the process if necessary, then install the plugin. A task sequence also gives me better logging.

The problem with a task sequence is that it runs in the system context, so I cannot interact with the end user who is effectively working in a different session. This can be solved however by using the ServiceUI.exe that comes with MDT. Sometime ago I wrote a post about how to prompt for input during a task sequence, but in this case I don’t want input, I simply want to use a message box.  I also want something reusable – so I don’t have to create a new package for each custom prompt.

I have a nice PowerShell function that will create a message box for me using the Wscript.shell “popup” method, so I added this function to a script, where I have also defined the message parameters I want to use at the bottom.


function New-PopupMessage {
# Return values for reference (https://msdn.microsoft.com/en-us/library/x83z1d9f(v=vs.84).aspx)

# Decimal value    Description  
# -----------------------------
# -1               The user did not click a button before nSecondsToWait seconds elapsed.
# 1                OK button
# 2                Cancel button
# 3                Abort button
# 4                Retry button
# 5                Ignore button
# 6                Yes button
# 7                No button
# 10               Try Again button
# 11               Continue button

# Define Parameters
[CmdletBinding()]
    [OutputType([int])]
    Param
    (
        # The popup message
        [Parameter(Mandatory=$true,Position=0)]
        [string]$Message,

        # The number of seconds to wait before closing the popup.  Default is 0, which leaves the popup open until a button is clicked.
        [Parameter(Mandatory=$false,Position=1)]
        [int]$SecondsToWait = 0,

        # The window title
        [Parameter(Mandatory=$true,Position=2)]
        [string]$Title,

        # The buttons to add
        [Parameter(Mandatory=$true,Position=3)]
        [ValidateSet('Ok','Ok-Cancel','Abort-Retry-Ignore','Yes-No-Cancel','Yes-No','Retry-Cancel','Cancel-TryAgain-Continue')]
        [array]$ButtonType,

        # The icon type
        [Parameter(Mandatory=$true,Position=4)]
        [ValidateSet('Stop','Question','Exclamation','Information')]
        $IconType
    )

# Convert button types
switch($ButtonType)
    {
        "Ok" { $Button = 0 }
        "Ok-Cancel" { $Button = 1 }
        "Abort-Retry-Ignore" { $Button = 2 }
        "Yes-No-Cancel" { $Button = 3 }
        "Yes-No" { $Button = 4 }
        "Retry-Cancel" { $Button = 5 }
        "Cancel-TryAgain-Continue" { $Button = 6 }
    }

# Convert Icon types
Switch($IconType)
    {
        "Stop" { $Icon = 16 }
        "Question" { $Icon = 32 }
        "Exclamation" { $Icon = 48 }
        "Information" { $Icon = 64 }
    }

# Create the popup
(New-Object -ComObject Wscript.Shell).popup($Message,$SecondsToWait,$Title,$Button + $Icon)
}

# Close the Task Sequence Progress UI temporarily (if it is running) so the popup is not hidden behind
try
    {
        $TSProgressUI = New-Object -COMObject Microsoft.SMS.TSProgressUI
        $TSProgressUI.CloseProgressDialog()
    }
Catch {}

# Define the parameters.  View the function parameters above for other options.
$Params = @(
    "The software 'Custom IE Plugin' is being installed to your computer. Please close Internet Explorer then click OK to continue." # Popup message
    0                           # Seconds to wait till the popup window is closed
    "Contoso IT: Custom IE Plugin" # title
    "Ok"                        # Button type
    "Exclamation"               # Icon type
    )

# Run the function
New-PopupMessage @Params

I place this script in a network share that everyone can access, and then simply call it during the task sequence using ServiceUI.exe.

How to Do It

Firstly, I need to create a package in SCCM containing the ServiceUI.exe for x86 and x64 architectures.  This package has no program, but simply contains the exe files, which I have renamed per architecture.  You can find the ServiceUI.exe in the following locations in your MDT install:

C:\Program Files\Microsoft Deployment Toolkit\Templates\Distribution\Tools\x64, or
C:\Program Files\Microsoft Deployment Toolkit\Templates\Distribution\Tools\x86

Capture

Once I have created and distributed the package, I create a new task sequence and add two “Run command line” steps at the beginning where I will prompt the user, one for x86 OS and one for x64.

Capture

The following things are needed in this step:

  • Use the package you created that contains the ServiceUI executables
  • Call ServiceUI using a process that the end user is running.  This enables ServiceUI to detect the session of the end user and interact with it.  If you are using a task sequence deployment with the option “Show task sequence progress” enabled, then you can use the tsprogressui.exe process, however if you are hiding the task sequence progress from the user, then this process will not exist, so you can call Explorer.exe which is certain to be running in the user session.
    • Eg, ServiceUI_x86 -process:Explorer.exe
  • You must specify the full path to powershell.exe
    • Eg, %SYSTEMROOT%\System32\WindowsPowershell\v1.0\powershell.exe
  • Use the “-File” parameter to call the powershell script that displays the popup.
  • Do NOT use the “timeout” option in the step, as this will cause ServiceUI to give an access denied error.
  • On the Option tab of the step, I use a couple of WMI queries so that the step only runs if the correct OS architecture is detected, and the Internet Explorer process is actually running.  I don’t want to prompt the user to close IE if it’s not actually open.
    • Eg, Select * from win32_OperatingSystem where OSArchitecture = ’32-bit’
    • Select * from Win32_Process where Name = ‘iexplore.exe’

Capture

A couple of things to note:

  • You could include the PowerShell script in the package with the ServiceUI executables, then you can call it locally instead of from a network share.  But the advantage of keeping the script and the executables separate is that you don’t need to create a new package each time you want to add a prompt – you simply reuse the ServiceUI package and create a new PowerShell script in the network share by copying and updating and existing script.
  • If you are using the “Show task sequence progress” option, the script includes some code that will hide the progress UI temporarily while the popup is displayed, otherwise it may appear behind the progress UI.
  • Don’t try to pass parameters when calling the PowerShell script, ServiceUI doesn’t seem to like that.
  • The script function includes a “SecondsToWait” parameter – this is set to 0 by default, which means the popup will stay on the screen indefinitely until a button is clicked.  In some cases this may not be desirable, so you can set a value here such that the task sequence will continue if no button has been clicked for some time.

Next, in case the user ignored the prompt or it timed-out, we add another “Run command line” step to kill the process forcefully using taskkill, if it is still running.

  • Eg, cmd /c taskkill /F /IM iexplore.exe

Capture

Make sure to add the same WMI process query to this step:

Capture

Then in the last step, we install the application itself.

Now, when the application is deployed to the end user’s machine, the first thing that happens is they get a popup on the screen warning them to close Internet Explorer.

Capture

Sweet 🙂

You could customise this further by adding some code to the script that will set a task sequence variable based on the exit code of the popup function, which will tell you what button was pressed, for example Yes, No, Ok, Cancel, Abort, Retry etc.  Then you could perform different activities in the task sequence based on the value of the variable.

Reading CCMEval Results Directly from a ConfigMgr Client with PowerShell

Since Configuration Manager 2012, a scheduled task is created by the client installer that runs “CCMEval.exe” periodically to check that the client is healthy and all the components it depends on are functioning as they should be. A number of checks are performed and if anything is found amiss, the default setting is to attempt remediation to fix the problem.  The client health status is reported to ConfigMgr and stored in the database.

By default, if a client has previously reported healthy and continues to be so, no new health report will be sent by the client to the management point, therefore the “last health evaluation time” reported in the console for active devices may be many months ago.  At first, this can seem disconcerting as it is tempting to think something may be wrong, but in reality the client is in all probability healthy.

If we check the ccmeval.log on the client, we can see that a report has recently been generated but it is “not necessary to send”.  The current report still exists on the client however as we will see later.

capture

It is possible to set the following registry key on the client which will cause the report to always be sent, that way ConfigMgr will always display the date of the most recent evaluation, but this will generate more traffic from the client and more work for ConfigMgr, especially if your client count is high, so probably isn’t best practice.

HKLM\SOFTWARE\Microsoft\CCM\CcmEval\SendAlways = TRUE

We can see the last health evaluation time from the ConfigMgr console, either in the Devices node under Assets and Compliance, or in the Client Check node under Monitoring.

capture

We can also find this information from the database directly with the following SQL query:


Select NetBiosName,ClientStateDescription,HealthCheckDescription,LastHealthEvaluation
from dbo.v_CH_ClientSummary ch
inner join dbo.v_CH_EvalResults eval on ch.ResourceID = eval.ResourceID
where NetBiosName = 'PC001'

 

capture

Yet if we look at the registry of the client itself (using PowerShell), we can see that it reports a much more recent date for the last evaluation time:


Get-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\CCM\CcmEval -Name LastEvalTime | Select -ExpandProperty LastEvalTime | Get-Date

capture

So in this case the difference between the last evaluation date in the database and the last evaluation date on the client itself is 12 months!  But that’s not a problem though, right? The client has been healthy for 12 months and hasn’t needed to report any further health data. Or has it?  What if the client is having trouble sending the current health evaluation report, therefore ConfigMgr is unaware of the actual current status?

That should be a rare occurrence since an inability to communicate with the management point will cause issues in other areas of client function and will probably result in the client being marked inactive in the console, but to be sure, lets look at the results of the most recent CCMEval task on the client.  When the CCMEval.exe runs, it creates an xml file called “CCMEvalReport.xml” with the results of the health evaluation at %windir%\CCM.  If you try to open this file however, you are greeted with an access denied warning:

capture

So first we need to take a copy of it, and open the copy.  Reading an XML file in notepad though is not too much fun:

capture

Of course, you can use an XML editor or a browser for better viewing, but since PowerShell can read XML nicely, lets create a PowerShell function that will open and parse this file into readable format for us.  We can then use this function to retrieve the CCMEval results from any local or remote computer.

First, since we need to run as administrator to take a copy of the file, let’s invoke a separate PowerShell instance using the “runas” verb so we don’t need to open an additional console window, and copy the file to a temp location:


$ComputerName -eq $env:COMPUTERNAME
$TargetFile = "$env:temp\CcmEvalReport.xml"
try
    {
        Start-Process -FilePath powershell.exe -ArgumentList "-Command ""&{Copy-Item $env:windir\CCM\CcmEvalReport.xml $TargetFile -Force}""" -Wait -ErrorAction Stop  -Verb Runas -WindowStyle Hidden
     }
catch
     {
          $_.Exception.Message
          continue
     }

Then let’s check that the file was copied and exists:


if (!(test-path $TargetFile))
    {
        Write-Error -Message "Could not locate the CcmEvalReport.xml"
        continue
    }

To load the file into PowerShell we need to create a new XML object and call the load method. Then we can remove the file from the temp location.


$xml = New-Object -TypeName System.Xml.XmlDocument
$xml.Load($TargetFile)
Remove-Item $TargetFile -Force

To run the previous code against either the local or a remote machine, we need to encapsulate the code in a script block, then call it using Invoke-Command. In addition, we will add a prompt for credentials in the case access is denied when using PS remoting:


$Script = {
    $TargetFile = ...
}

if ($ComputerName -eq $env:COMPUTERNAME)
{
    $xml = Invoke-Command -ScriptBlock $Script
}
Else
{
    try
    {
        $xml = Invoke-Command -ScriptBlock $Script -ComputerName $ComputerName -ErrorAction Stop
    }
    catch
    {
        if ($Error[0] | Select-String -Pattern 'Access is denied')
        {
            $Credentials = $host.ui.PromptForCredential('Credentials required', "Access was denied to $ComputerName.  Enter credentials to connect.", '', '')
            $xml = Invoke-Command -ScriptBlock $Script -ComputerName $ComputerName -Credential $Credentials
        }
        Else
        {
            $_.Exception.Message
        }
    }
} 

Now if we browse the $xml object, we can find 2 nodes of importance, a summary node:

capture

And a health check node:

capture

So let’s filter the information we need, and add the results to objects that we can output with our function:


$Checks = $xml.ClientHealthReport.HealthChecks.HealthCheck |
Select-Object -Property @{
    l = 'Test'
    e = {
        $_.Description
    }
}, @{
    l = 'Result'
    e = {
        $_.'#text'
    }
} |
Sort-Object -Property Test

$Summary = $xml.ClientHealthReport.Summary |
Select-Object -Property @{
    l = 'ComputerName'
    e = {
        $ComputerName
    }
}, @{
    l = 'EvaluationTime'
    e = {
        [datetime]($_.EvaluationTime)
    }
}, @{
    l = 'Result'
    e = {
        $_.'#text'
    }
}

Finally, we add this code into a function so we can easily run it against the local computer, or pass a remote computername/s to it as a parameter or along the pipeline to read the CCMEval results from other computers.

Get-CCMEvalResult


function Get-CCMEvalResult
{
    <#             .Synopsis             Get the results of the most recent client health evaluation on a local or remote computer             .DESCRIPTION             Parses the ccmevalreport.xml file into a readable format to view the results of the ccmeval task.  Can be run on the local or remote computer.             .EXAMPLE             Get-CCMEvalResult             Returns the ccmeval results from the local machine             .EXAMPLE             Get-CCMEvalResult -ComputerName PC001             Returns the ccmeval results from a remote machine             .EXAMPLE             'PC001','PC002' | Get-CCMEvalResult             Returns the ccmeval results from a remote machine     #>

    #requires -Version 2

    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory = $false,
                ValueFromPipeline = $true
        )]
        [string[]]$ComputerName = $env:COMPUTERNAME
    )

    Begin {
        $Script = {
            $TargetFile = "$env:temp\CcmEvalReport.xml"
            try
            {
                Start-Process -FilePath powershell.exe -ArgumentList "-Command ""&{Copy-Item $env:windir\CCM\CcmEvalReport.xml $TargetFile -Force}""" -Wait -ErrorAction Stop  -Verb Runas -WindowStyle Hidden
            }
            catch
            {
                $_.Exception.Message
                continue
            }

            if (!(test-path $TargetFile))
                {
                    Write-Error -Message "Could not locate the CcmEvalReport.xml"
                    continue
                }

            $xml = New-Object -TypeName System.Xml.XmlDocument
            $xml.Load($TargetFile)
            Remove-Item $TargetFile -Force
            return $xml
        }
    }

    Process {
        if ($ComputerName -eq $env:COMPUTERNAME)
        {
            $xml = Invoke-Command -ScriptBlock $Script
        }
        Else
        {
            try
                {
                    $xml = Invoke-Command -ScriptBlock $Script -ComputerName $ComputerName -ErrorAction Stop
                }
            catch
                {
                    if ($Error[0] | Select-String -Pattern 'Access is denied')
                        {
                            $Credentials = $host.ui.PromptForCredential('Credentials required', "Access was denied to $Computername.  Enter credentials to connect.", '', '')
                            $xml = Invoke-Command -ScriptBlock $Script -ComputerName $ComputerName -Credential $Credentials
                        }
                    Else { $_.Exception.Message }
                }
        }

        $Checks = $xml.ClientHealthReport.HealthChecks.HealthCheck |
        Select-Object -Property @{
            l = 'Test'
            e = {
                $_.Description
            }
        }, @{
            l = 'Result'
            e = {
                $_.'#text'
            }
        } |
        Sort-Object -Property Test
        [array]$Summary = $xml.ClientHealthReport.Summary |
        Select-Object -Property @{
            l = 'ComputerName'
            e = {
                $ComputerName
            }
        }, @{
            l = 'EvaluationTime'
            e = {
                [datetime]($_.EvaluationTime)
            }
        }, @{
            l = 'Result'
            e = {
                $_.'#text'
            }
        }

        $Summary
        $Checks | Format-Table
    }
}

Now if I run the function against my local machine, I can see each health test performed and the results, as well as the overall status of the evaluation and the time it was performed.

capture

I can also run it against remote computers like this:


Get-CCMEvalResult -ComputerName 'SRV001'
'PC001','PC001' | Get-CCMEvalResult

To troubleshoot any evaluation failures, view the ccmeval.log (%windir%\CCM) and the ccmsetup-ccmeval.log (%windir%\ccmsetup\Logs).

Client Health: Find all CCMEval Failed or Unknown

Keeping your ConfigMgr clients healthy is an important task for an SCCM administrator. Here’s a SQL query that will find all devices in ConfigMgr that have been active in the last 7 days, but have either failed their CCMEval or have “unknown” status, ie no CCMEval results.


Select sys.Name0 as 'ComputerName',sys.User_Name0 as 'UserName',
cs.ClientStateDescription,
DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) as 'Days Active',
DATEDIFF(day,cs.LastHealthEvaluation,GetDate()) as 'Days Since Last Eval',
sys.Creation_Date0 as 'SCCM Client Registration Date',
cs.LastActiveTime, cs.LastHealthEvaluation,
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when cs.ClientRemediationSuccess = 1 then 'Pass'
 when cs.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
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 'LastHealthEvaluationResult',
HealthCheckDescription,ResultDetail,ResultCode
from dbo.v_CH_ClientSummary cs
inner join v_R_System sys on cs.ResourceID = sys.ResourceID
left join v_CH_EvalResults eval on cs.ResourceID = eval.ResourceID
where cs.ClientStateDescription in ('Active/Fail','Active/Unknown')
and DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) > 7
Order by ClientStateDescription,ComputerName

I like to add SQL queries to PowerShell functions so I can return quick results to my current session:

capture

You can run this query from PowerShell like this (enter your database info at the top of the script):


function Get-CCMEvalFailedOrUnknown
{
# Database info
$dataSource = 'mysqlserver\INST_SCCM'
$database = 'CM_ABC'

$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$Query = "
Select sys.Name0 as 'ComputerName',sys.User_Name0 as 'UserName',
cs.ClientStateDescription,
DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) as 'Days Active',
DATEDIFF(day,cs.LastHealthEvaluation,GetDate()) as 'Days Since Last Eval',
sys.Creation_Date0 as 'SCCM Client Registration Date',
cs.LastActiveTime, cs.LastHealthEvaluation,
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when cs.ClientRemediationSuccess = 1 then 'Pass'
 when cs.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
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 'LastHealthEvaluationResult',
HealthCheckDescription,ResultDetail,ResultCode
from dbo.v_CH_ClientSummary cs
inner join v_R_System sys on cs.ResourceID = sys.ResourceID
left join v_CH_EvalResults eval on cs.ResourceID = eval.ResourceID
where cs.ClientStateDescription in ('Active/Fail','Active/Unknown')
and DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) > 7
Order by ClientStateDescription,ComputerName

"

$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object -TypeName 'System.Data.DataTable'
$table.Load($reader)

# Close the connection
$connection.Close()

return $table
}

Get-CCMEvalFailedOrUnknown | Out-GridView

capture

You could also send this as a weekly HTML report by email using the Windows Task Scheduler. Simply use the following code, entering your database and email parameters at the top:


# Database info
$dataSource = 'mysqlserver\INST_SCCM'
$database = 'CM_ABC'

#Email params
$EmailParams = @{
    To         = 'Joe.Bloggs@contoso.com'
    From       = 'PowerShell@contoso.com'
    Smtpserver = 'mysmtpserver'
    Subject    = "Client Health: CCMEval Failed or Unknown Weekly Report $(Get-Date -Format dd-MMM-yyyy)"
}

$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$Query = "
Select sys.Name0 as 'ComputerName',sys.User_Name0 as 'UserName',
cs.ClientStateDescription,
DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) as 'Days Active',
DATEDIFF(day,cs.LastHealthEvaluation,GetDate()) as 'Days Since Last Eval',
sys.Creation_Date0 as 'SCCM Client Registration Date',
cs.LastActiveTime, cs.LastHealthEvaluation,
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when cs.ClientRemediationSuccess = 1 then 'Pass'
 when cs.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
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 'LastHealthEvaluationResult',
HealthCheckDescription,ResultDetail,ResultCode
from dbo.v_CH_ClientSummary cs
inner join v_R_System sys on cs.ResourceID = sys.ResourceID
left join v_CH_EvalResults eval on cs.ResourceID = eval.ResourceID
where cs.ClientStateDescription in ('Active/Fail','Active/Unknown')
and DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) > 7
Order by ClientStateDescription,ComputerName

"

$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object -TypeName 'System.Data.DataTable'
$table.Load($reader)

# Send html email
$style = @"
<style>
body {
    color:#333333;
    font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}
}
h1 {
    text-align:center;
}
h2 {
    border-top:1px solid #666666;
}
table {
    border-collapse: collapse;
    font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;
}
th {
    font-size: 10pt;
    text-align: left;
    padding-top: 5px;
    padding-bottom: 4px;
    background-color: #1FE093;
    color: #ffffff;
}
td {
    font-size: 8pt;
    border: 1px solid #1FE093;
    padding: 3px 7px 2px 7px;
}
</style>

"@

$Properties = @(
'ComputerName',
'UserName',
'ClientStateDescription',
'Days Active',
'Days Since Last Eval',
'SCCM Client Registration Date',
'LastActiveTime',
'LastHealthEvaluation',
'Last Evaluation Healthy',
'ClientRemediationSuccess',
'LastHealthEvaluationResult',
'HealthCheckDescription',
'ResultDetail',
'ResultCode'
)

$body = $table |
Select-Object -Property $Properties|
ConvertTo-Html -Head $style -Body "
<H2>Computers that failed or have unknown CCMEval Results, and have been active in the last week ($($results.Count))</H2>

" |
Out-String

Send-MailMessage @EmailParams -Body $Body -BodyAsHtml

# Close the connection
$connection.Close()

capture

New tool! Reliability Viewer for Windows

bs

I am pleased to release a new free GUI tool today to assist in troubleshooting Windows systems – the Reliability Viewer for Windows.  It is based on the concept of the built-in Windows Reliability Monitor, but can be used to display data from both the local or remote systems, which the Microsoft tool cannot do.

It is useful for identifying stability issues on a Windows system by reporting key system events such as

  • Application crashes
  • Software Update installations
  • MsiInstaller events
  • Unexpected system shutdowns
  • Blue-screens
  • Driver installations
  • Hardware failure

It uses PowerShell Remoting to retrieve reliability data from remote computers and displays it in an event-log style grid format.  Filters are included for the key fields for easy searching of particular events. The application can also generate a system stability chart using Microsoft Excel, which uses reliability metrics to give an overview of the stability of the system over time.

RelChart

Download the tool here, or read the full blog for the tool here.

Enjoy 🙂