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.

capture

capture

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:


select
sys.Name0 as 'Computer Name',
sys.User_Name0 as 'User Name',
summ.ClientStateDescription,
case when summ.ClientActiveStatus = 0 then 'Inactive'
 when summ.ClientActiveStatus = 1 then 'Active'
 end as 'ClientActiveStatus',
summ.LastActiveTime,
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',
summ.LastOnline,
summ.LastDDR,
summ.LastHW,
summ.LastSW,
summ.LastPolicyRequest,
summ.LastStatusMessage,
summ.LastHealthEvaluation,
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',
summ.ExpectedNextPolicyRequest
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:

capture

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:


Send-CCMEvalReport

To run against a remote computer:


Send-CCMEvalReport -ComputerName PC001

The script also supports verbose output:

Send-CCMEvalReport -ComputerName PC001 -Verbose

capture

Here’s the full code:

Send-CCMEvalReport.ps1


[CmdletBinding()]
    param(
        [Parameter(Mandatory=$False)]
        [String]$ComputerName = $env:COMPUTERNAME
        )

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

    Try
    {
        $null = New-ItemProperty -Path $Path -Name 'SendAlways' -Value $Value -Force -ErrorAction Stop
    }
    Catch
    {
        $_
    }
}

# 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!"
        Return
    }

    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
    }
    Else
    {
        Write-Error $($Result.Exception.Message)
    }
}
# Run against remote computer
Else
{
    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
    }
    Else
    {
        Write-Error $($Result.Exception.Message)
    }
}

New Free App – ConfigMgr Deployment Reporter

Just released a new free application for ConfigMgr admins – ConfigMgr Deployment Reporter.  I developed this app for use in the organisation I currently work for, and it turned out quite well, so I decided to release a public version to the community!

capture

I developed this app as an alternative (and IMO easier) way to report on ConfigMgr deployments than using the ConfigMgr console. It uses a little different format than the console node allowing you to select which deployment you wish to view data for based on the “feature type” (ie application, package etc) and report on only that deployment.  It also introduces a separation of results between all applicable systems for a deployment, and only those systems which have currently reported status, which allows for a more accurate view of the success of a deployment as it progresses.

The app allows the creation of charts and HTML-format reports to give a nice graphical snapshot of a deployment.

I also added the capability to report per-device for Software Update and Task Sequence deployments.  For Software Updates, this allows you to see which updates from the deployment are applicable to the machine and the status of each update, and for Task Sequences it allows viewing the execution status of each step in the task sequence for the selected device.

As usual, I code purely in PowerShell using WPF for the UI.  This time I added metro styling using the excellent MahApps.Metro project 🙂

Download the app from here.

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

Send a Weekly OS Deployment Summary Report with PowerShell

Here’s a script I wrote that sends a simple summary of ConfigMgr OS Deployments in the last week as an HTML-formatted email.

It gives you the start and finish date/time, duration and model for each computer deployed (where the information is available in SCCM), and a list of any steps in an error state for any of the deployments.

You can use a scheduled task to send this report to yourself each week for a nice deployment overview 🙂

OSDreport

Note: to generate ad-hoc reports for specific time-periods for any ConfigMgr Task Sequence you can use my tool ConfigMgr Task Sequence Monitor

Generate-OSDReport

At the top of the script, enter the following:

  • SQLServer name (and instance name where applicable)
  • ConfigMgr Database name
  • The time in hours past that the report will display data for
  • The name/s of your OSD tasks sequences
  • Email parameters

You need db_datareader permission to the ConfigMgr database with the account running the script.


#requires -Version 2

# Database info
$dataSource = 'mysqlserver\myinstance' # SQLServer\Instance
$database = 'CM_ABC' # Database name
$TimeInHours = '168' # 168 = 7 days
$OSDTaskSequences = "
    'Windows OS Deployment x86',
    'Windows OS Deployment x64'
    " # Name/s of your OSD Task Sequences

#Email params
$EmailParams = @{
    To         = 'Joe.Bloggs@contoso.com'
    From       = 'PowerShell@contoso.com'
    Smtpserver = 'myexchangeserver'
    Subject    = "Operating System Deployment Weekly Report $(Get-Date -Format dd-MMM-yyyy)"
}

$results = @()

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

$Query = "
    select distinct tes.ResourceID
    from vSMS_TaskSequenceExecutionStatus tes
    inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
    where tsp.Name in ($OSDTaskSequences)
    and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
"

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

foreach ($ResourceID in $table.Rows.ResourceID)
{
    $Query = "
        Select (select top(1) convert(datetime,ExecutionTime,121)
        from vSMS_TaskSequenceExecutionStatus tes
        inner join v_R_System sys on tes.ResourceID = sys.ResourceID
        inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
        where tsp.Name in ($OSDTaskSequences)
        and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
        and LastStatusMsgName = 'The task sequence execution engine started execution of a task sequence'
        and Step = 0
        and tes.ResourceID = $ResourceID
        order by ExecutionTime desc) as 'Start',
        (select top(1) convert(datetime,ExecutionTime,121)
        from vSMS_TaskSequenceExecutionStatus tes
        inner join v_R_System sys on tes.ResourceID = sys.ResourceID
        inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
        where tsp.Name in ($OSDTaskSequences)
        and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
        and LastStatusMsgName = 'The task sequence execution engine successfully completed a task sequence'
        and tes.ResourceID = $ResourceID
        order by ExecutionTime desc) as 'Finish',
        (Select name0 from v_R_System sys where sys.ResourceID = $ResourceID) as 'ComputerName',
        (select Model0 from v_GS_Computer_System comp where comp.ResourceID = $ResourceID) as 'Model'
    "
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $reader = $command.ExecuteReader()
    $table = New-Object -TypeName 'System.Data.DataTable'
    $table.Load($reader)

    if ($table.rows[0].Start.GetType().Name -eq 'DBNull')
    {
        $Start = ''
    }
    Else
    {
        $Start = $table.rows[0].Start
    }

    if ($table.rows[0].Finish.GetType().Name -eq 'DBNull')
    {
        $Finish = ''
    }
    Else
    {
        $Finish = $table.rows[0].Finish
    }

    #$table
    if ($Start -eq '' -or $Finish -eq '')
    {
        $diff = $null
    }
    else
    {
        $diff = $Finish-$Start
    }

    $PC = New-Object -TypeName psobject
    Add-Member -InputObject $PC -MemberType NoteProperty -Name ComputerName -Value $table.rows[0].ComputerName
    Add-Member -InputObject $PC -MemberType NoteProperty -Name StartTime -Value $table.rows[0].Start
    Add-Member -InputObject $PC -MemberType NoteProperty -Name FinishTime -Value $table.rows[0].Finish
    if ($Start -eq '' -or $Finish -eq '')
    {
        Add-Member -InputObject $PC -MemberType NoteProperty -Name DeploymentTime -Value ''
    }
    else
    {
        Add-Member -InputObject $PC -MemberType NoteProperty -Name DeploymentTime -Value $("$($diff.hours)" + ' hours ' + "$($diff.minutes)" + ' minutes')
    }
    Add-Member -InputObject $PC -MemberType NoteProperty -Name Model -Value $table.rows[0].Model
    $results += $PC
}

$results = $results | Sort-Object -Property ComputerName

$Query = "
    select sys.Name0 as 'ComputerName',
    tsp.Name 'Task Sequence',
    comp.Model0 as Model,
    tes.ExecutionTime,
    tes.Step,
    tes.GroupName,
    tes.ActionName,
    tes.LastStatusMsgName,
    tes.ExitCode,
    tes.ActionOutput
    from vSMS_TaskSequenceExecutionStatus tes
    left join v_R_System sys on tes.ResourceID = sys.ResourceID
    left join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
    left join v_GS_COMPUTER_SYSTEM comp on tes.ResourceID = comp.ResourceID
    where tsp.Name in ($OSDTaskSequences)
    and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
    and tes.ExitCode not in (0,-2147467259)
    Order by tes.ExecutionTime desc
"

$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;}
    font-size: 10pt;
}
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: 1.2em;
    text-align: left;
    padding-top: 5px;
    padding-bottom: 4px;
    background-color: #1FE093;
    color: #ffffff;
}
th, td {
    font-size: 1em;
    border: 1px solid #1FE093;
    padding: 3px 7px 2px 7px;
}
<style>
"

$body1 = $results |
Select-Object -Property ComputerName, StartTime, FinishTime , DeploymentTime, Model |
ConvertTo-Html -Head $style -Body "
<H2>OS Deployments This Week ($($results.Count))</H2>

" |
Out-String

$body2 = $table |
Select-Object -Property ComputerName, 'Task Sequence', Model, ExecutionTime, Step, GroupName, ActionName, LastStatusMsgName, ExitCode, ActionOutput |
ConvertTo-Html -Head $style -Body "
<H2>OS Deployment Errors This Week ($($table.Rows.Count))</H2>

" |
Out-String

$Body = $body1 + $body2

Send-MailMessage @EmailParams -Body $Body -BodyAsHtml

# Close the connection
$connection.Close()

Free ConfigMgr Task Sequence Monitoring Tool

Update! (Nov 19 2015 – If you experienced the issue with the app appearing and immediately disappearing again, this has been fixed in the latest release v1.2.1)

Today I am pleased to release a new free application for System Center Configuration Manager users: ConfigMgr Task Sequence Monitor 🙂

capture

It is a tool for monitoring or reviewing task sequence executions in Configuration Manager and is particularly useful for monitoring Operating System deployments.  Where integration with MDT is available and the MDT monitoring web service has been enabled, the app can include the data from MDT for an enhanced monitoring experience of your ZTI OS deployments.

Action output can be displayed for each step of the task sequence, giving more detail about the execution of that step, and is useful for quickly identifying the cause of any errors in the task sequence execution.

The app can also generate an HTML deployment summary report for any task sequence.

Check it out here: https://smsagent.wordpress.com/tools/configmgr-task-sequence-monitor/

The tool is my first publicly released WPF application coded entirely in PowerShell, so I welcome any feedback!

Free ConfigMgr Client Health Report

Today I am pleased to make available the first of my free reports for System Center Configuration Manager 2012 and onward – a client health report.  These reports have been created in Microsoft Excel and use data connections to the ConfigMgr database, which allows us to pull a large amount of data into a single report, and display it both summarily, graphically and in data tables, without the need to drill down into further reports or navigate to different locations in the ConfigMgr console to find the data you need.

The client health report focuses on key health data for your ConfigMgr clients, including:

  • Inactive / active clients
  • Clients that have passed or failed the client evaluation check, including any error details
  • Clients that are active or inactive for DDR (heartbeat discovery), hardware inventory, software inventory, policy requests and status messages
  • ConfigMgr client versions in your environment
  • Last reboot times
  • Discovered systems with no ConfigMgr client installed
  • Systems that failed to install the ConfigMgr client, including error details

Download and read the full post for the client health report here.

Screenshots

ch1

 

ch11

ch14

ch16

ch18

Retrieving Software Inventory for a ConfigMgr Site with PowerShell

In my last post, I demonstrated how we can retrieve software inventory information for a single ConfigMgr client or an array of clients, using PowerShell.  In this post, we will change the scope from the client to the entire site.  Using this script, you can query for all installations of a specific software/s in your ConfigMgr site, returning either the count, or the full list of machines with the software installed.

When I say “software inventory”, I’m actually referring to the “hardware inventory” process (strange but true) in Configuration Manager that collects data from WMI classes, including the installed software, and not to be confused with the “software inventory” process in ConfigMgr which is used to inventory file types.

As previously, you need ‘db_datareader’ permission to your ConfigMgr database, with your logged on account, and you need to add the sql server and database name in the script.

Examples

Find a count of machines that have “Microsoft Silverlight” installed


Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight%" -Count

capture1

Get the list of machines that have “Microsoft Silverlight 5” installed and output to Gridview


Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5%" | Out-GridView

Capture2

Get the list of machines that have “Microsoft Silverlight 5” installed and output to CSV

 Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5" -CSV 

Capture3

Get-CMInstalledSoftware


[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$False,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$SoftwareName = "%",
        [Parameter(Mandatory=$False)]
            [switch]$Count,
        [Parameter(Mandatory=$False)]
            [switch]$CSV,
        [Parameter(Mandatory=$False)]
            [string]$SQLServer = “mysqlserver\INST_SCCM”, # eg, <sqlserver>, or <sqlserver>\<instance>
        [Parameter(Mandatory=$False)]
            [string]$Database = “CM_ABC”
        )
 
# Open a connection
$connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
 
# Set queries
if ($count)
    {
        $query = "
        SELECT Count(sof.NormalizedName) AS 'Count',
        sof.NormalizedName, sof.NormalizedVersion, sof.NormalizedPublisher, sof.FamilyName, sof.CategoryName
        FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
        where sof.NormalizedName like '$SoftwareName'
        GROUP BY sof.NormalizedName, sof.NormalizedVersion, sof.NormalizedPublisher, sof.FamilyName, sof.CategoryName
        ORDER BY 'Count' DESC, sof.NormalizedName, sof.NormalizedVersion
        "
    }
else
    {
        $query = "select Name0 as 'Computer Name',
        User_Name0 as 'Last Logged-On User',
        NormalizedName as 'Software Name',
        NormalizedPublisher as Publisher,
        NormalizedVersion as Version,
        FamilyName as 'Software Family',
        CategoryName as 'Software Category',
        InstallDate0 as 'Install Date',
        RegisteredUser0 as 'Registered User',
        InstalledLocation0 as 'Install Location',
        InstallSource0 as 'Source Location',
        UninstallString0 as 'Uninstall String',
        TimeStamp as 'Inventory Time'
        from v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
        inner join v_R_System sys on sof.ResourceID = sys.ResourceID
        where sof.NormalizedName like '$SoftwareName' order by Name0
        "
    }
    
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)

# Output results
if ($CSV)
    {
        $Path = "$env:TEMP\SoftwareQuery-$(Get-date -format hh-mm).csv"
        $table | Export-Csv -Path $Path -Force -NoTypeInformation
        Invoke-Item $Path
    }
Else {$table}

# Close the connection
$connection.Close()

Instant Client Software Inventory with ConfigMgr and PowerShell

Here’s a simple but handy PowerShell script I wrote that uses the ConfigMgr database to retrieve software inventory information for any client.  You can return the entire inventory for the client, or search for specific software.  You can also pass the computer name and/or software name along the pipeline to the script, so you can search multiple computers or multiple software titles.

You need ‘db_datareader’ access to your ConfigMgr database with your logged-on account, and you also need to add the ‘Installed Software‘ class to your hardware inventory classes, in your ConfigMgr Client Settings.

Examples

Search for software with “Apple” in the title for a specific client:


Get-CMClientInstalledSoftware -ComputerName mypc-tj8 -SoftwareName %Apple%

capture3

Retrieve the entire software inventory for a client, output to GridView


Get-CMClientInstalledSoftware -ComputerName mypc-tj8 | Out-GridView

capture2Search for “Cisco Webex Meeting Center for FireFox” on an array of clients

Uses PowerShell 4.0’s ForEach method


($computers = @("mypc-tj","mypc-tj8").ForEach({Get-CMClientInstalledSoftware -ComputerName $psitem -SoftwareName "%Cisco%FireFox%"}))

captureGet-CMClientInstalledSoftware

Update the $SQLServer and $Database parameters with your ConfigMgr SQL server (and instance if applicable) and database name.


[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$True,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$ComputerName,
        [Parameter(Mandatory=$False,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$SoftwareName = "%",
        [Parameter(Mandatory=$False)]
            [string]$SQLServer = “mysqlserver\INST_SCCM”, # eg <mysqlserver>, or <mysqlserver\instance>
        [Parameter(Mandatory=$False)]
            [string]$Database = “CM_ABC”
        )

# Open a SQL connection
$connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Set query
$query = "select Name0 as 'Computer Name',
User_Name0 as 'Last Logged-On User',
NormalizedName as 'Software Name',
NormalizedPublisher as Publisher,
NormalizedVersion as Version,
FamilyName as 'Software Family',
CategoryName as 'Software Category',
InstallDate0 as 'Install Date',
RegisteredUser0 as 'Registered User',
InstalledLocation0 as 'Install Location',
InstallSource0 as 'Source Location',
UninstallString0 as 'Uninstall String',
TimeStamp as 'Inventory Time'
from v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
inner join v_R_System sys on sof.ResourceID = sys.ResourceID
where sys.Name0 = '$ComputerName'
and sof.NormalizedName like '$SoftwareName'
order by 'Software Name'"

# Execute query
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

# Load results
$table = new-object “System.Data.DataTable”
$table.Load($result)

# Output results
$table

# Close the connection
$connection.Close()