ConfigMgr Dashboards and Software Update General Compliance Gauge

October 8, 2010 at 11:25 am in AdminUi, ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr Dashboards, ConfigMgr SP2, configmgr2007, Dashboards, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, SCCM Dashboards, sccm2007, SQL, SQL Reporting services, SRS by Kenny Buntinx [MVP]

Microsoft released last year the Configuration Manager 2007 dashboards.A dashboard is basically a webpage containing multiple reports with graphs. The dashboard is a WSS 3.0 / MOSS 2007 solution that tries to give an high level graphical representation of the data contained in ConfigMgr. This enables ConfigMgr administrators to have multiple information in reports on a single page or in this case to help IT managers what is happening in their environment without all the report data .

A manager at a customer asked me if it would be possible to have a general percentage of Software Update Percent Compliance spread over all the Software deployment Management advertisements that are targeted to Pre-Production and Production.

To accomplish this written a SQL query to to this . As I don’t take all the credit for doing this , I will refer to them during my blog post .

I started with getting my initial report SQL code from Kent Agerlund (MVP configMgr) on http://blog.coretech.dk/confmgr07/config-mgr-inventory-and-reporting/status-report-for-software-update-deployments/

As soon as I used this SQL Query in My SCCM Dashboards , I quickly saw that I could not use the value “% Compliance” on the Y axis in combination with my “Deployment” name. It is shown below in the picture :

 

image

 

As I needed to have the Percentage of my total compliance spread over my SDM Lists ,  I needed to modify the query . I want to special thank Raghu Kethineni for his help understanding this query .This is how the SQL query should look like to use the value “% Compliance” on the Y axis in combination with my “Deployment” name :

 

Select Deploymentname, Available, Deadline,

cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) AS ‘% Compliant’,

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt

****** SQL Query end ******

After modifying the SQL query , the value appeared in the list as shown below in the attached picture :

image

 

If you need to select specific SDM advertisements , like I had to split up “Pre-Production” from “Production” , I had to modify my SQL query once again by adding a filter as show in the code below . In this case we want to only have the Production .

****** SQL Query ******

Select Deploymentname, Available, Deadline,
cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) AS ‘% Compliant’,
  [Compliant],
  [Enforcement state unknown],
  [Successfully installed update(s)],
  [Failed to install update(s)],
  [Installing update(s)],
  [Waiting for another installation to complete],
  [Pending system restart],
  [Downloading update(s)]
From
(select
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where AssignmentName NOT like ‘%_PREPROD_%’
group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,
      sn.StateName) as PivotData
PIVOT
(
SUM (NumberOfComputers)
FOR LastEnforcementState IN
( [Compliant],
  [Enforcement state unknown],
  [Successfully installed update(s)],
  [Failed to install update(s)],
  [Installing update(s)],
  [Waiting for another installation to complete],
  [Pending system restart],
  [Downloading update(s)])
) AS pvt

****** SQL Query end ******

 

If done and you saved your dashboard  query , It will look like this :

 

image

 

Hope It Helps  ,

 

Kenny Buntinx

Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInPin on Pinterest