New Free Tool: ConfigMgr Remote Compliance

Remote Compliance

Today I released a new free tool for ConfigMgr administrators and support staff.

ConfigMgr Remote Compliance can be used to view, evaluate and report on System Center Configuration Manager Compliance Baselines on a remote computer. It provides similar functionality to the Configurations tab of the Configuration Manager Control Panel, but for remote computers. It is a useful troubleshooting tool for remotely viewing client compliance, evaluating baselines, viewing the evaluation report or opening DCM log files from the client, without needing to access the client computer directly.

ConfigMgr Remote Compliance can be downloaded from here.

Source code for this application is available on GitHub and code contributions are welcome.

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.

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 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