counter hit make
ConfigMgr Dashboards and Software Update General Compliance Gauge - sccm

SCCM - System Center Configuration Manager

Blog about SCCM 2007 aka SMS v4

Recent Posts

Tags

News

Community

Email Notifications

    Blogs

    Archives

    ConfigMgr Dashboards and Software Update General Compliance Gauge

    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

    Posted: Oct 08 2010, 01:25 PM by Kenny Buntinx | with 1 comment(s) |
    Filed under: , , , , , , , , , , , , , , , , , ,

    Comments

    Agerlund said:

    Hey Kenny,

    Nice work - I'll try this asap :-)

    # October 10, 2010 10:14 AM