You are browsing the archive for ConfigMgr Dashboards.

Detect, Inventory and report about the encryption method used by Bitlocker thru ConfigMgr

6:54 pm in bitlocker, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr 2012, configmgr 2012 R2, ConfigMgr 2012 R2 SP1, ConfigMgr 2012 SP1, ConfigMgr Dashboards, ConfigMgr SP2, Encryption, Inventory, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, SCCM 2012, sccm 2012 R2, SCCM 2012 R2, SCCM 2012 R2 SP1, SCCM 2012 SP1, SCCM Dashboards, sccm RTM, SCCM v.Next, sccm2007 by Kenny Buntinx [MVP]

 

Recently at a client, we needed to provide a report that was listing what Bitlocker Encryption strength method was used. That information had to be fed into the CMDB to make sure we had ‘256AES with Diffuser’ enabled.

Unfortunately, Configmgr 2012 does deliver out-of-the-box a way to determine what Bitlocker Encryption strength method, and that means the information is not in the registry or WMI.

Dependencies :

Well I tried to find an easy way , and the customer required a solution that was :

– Flexible and dynamic as they where constantly migrating from Mcafee Disk Encryption to Bitlocker and the CMDB had to be dynamically updated.

– Centrally managed code , meaning that if we needed to change anything to the code , it had to be intelligent enough to update it auto magically to all clients.

– Had to be reliable .

The solution :

– was to use a kind of detection powershell script for the Bitlocker Encryption strength using the standard powershell commandlet ‘Manage-bde’ .

– The script was to be used with a “compliance Item” and deployed thru a “Baseline” as one of my colleagues Henrik Hoe explains here :  http://blog.coretech.dk/heh/configuration-items-and-baselines-using-scripts-powershell-example/ . By using a CI , you will meet the centrally managed code part , but also the automatically way of updating the detection logic to all clients.

Forget about the old package/program way and then a way to execute the script on regular basis ( That can all be done thru the Baseline deployment)

– The script will be executed and will write a registry value BitlockerEncryptionStrenght = “TheActualValue”  and the baseline will report complaint when it has the ‘256AES with Diffuser’ detected. When the machine is not bitlockered at all , we will write a value  BitlockerEncryptionStrenght = “None”

$ErrorActionPreference="silentlycontinue" $StrBEncryption = "" $objBEncryption = "" $objBEncryption=manage-bde.exe -status |Where-Object{$_ -like "*encryption method*"} $arrBEncryption=$objBEncryption.Split(":") $StrBEncryption=$arrBEncryption[1].Trim() If ($StrBEncryption.Contains("AES")) { New-ItemProperty -Path HKLM:\SYSTEM\ABPosdInstall -Name BitlockerEncryptionStrenght -Value $StrBEncryption -Property String -Force -ErrorAction SilentlyContinue | Out-Null if ($StrBEncryption -eq "AES 256 with Diffuser") { return 1 } } Else { New-ItemProperty -Path HKLM:\SYSTEM\ABPosdInstall -Name BitlockerEncryptionStrenght -Value "None" -Property String -Force -ErrorAction SilentlyContinue | Out-Null Return 0 }

– We will pick the value up later with a custom registry key hardware inventory extension and use that in our reporting later on. For more details on how to do it : https://technet.microsoft.com/en-us/library/gg712290.aspx

Hope it Helps ,

Kenny Buntinx

Enterprise Client Management MVP

How to retrieve the Lenovo Modelname (Full name) instead of TypeNumber within Configmgr

2:56 pm in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr Dashboards, ConfigMgr Reporting, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, SCCM Dashboards, sccm2007, SQL Reporting services by Kenny Buntinx [MVP]

A customer of mine wanted a readable report on the different machine that existed in his country.

Right now it apears as the typenumber , witch frankly a manager does not care about and I can totally understand it.

You will see what I mean . This is what you get if you use the default report :

image

This is what management wants to see :

image

To get to this result , please follow the guidelines below :

Now to tried and  find out where it was stored  , we used wmiexplorer . We show you where to find it below :

wmi

Lenovo’s store their “ModelName” in a different WMI class that others (Win32_ComputerSystemProduct as opposed to Win32_ComputerSystem ).  By default that class is not enabled in the SMS_DEF.MOF file.

Go to your SMS_DEF.MOF file and enable it as followed

<div>
<div>
[ SMS_Report 
SMS_Group_Name ("Computer System Product")
<!--CRLF-->
<pre><span style="color: #606060;">   3:</span>   SMS_Class_ID   (<span style="color: #006080;">"MICROSOFT|COMPUTER_SYSTEM_PRODUCT|1.0"</span>) ]</pre>
<pre><span style="color: #606060;">   4:</span></pre>
<pre><span style="color: #606060;">   5:</span> <span style="color: #0000ff;">class</span> Win32_ComputerSystemProduct : SMS_Class_Template</pre>
{
[SMS_Report (TRUE)     ]
string</span>     Caption;
<pre><span style="color: #606060;">   9:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  10:</span>         <span style="color: #0000ff;">string</span>     Description;</pre>
<pre><span style="color: #606060;">  11:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>), key]</pre>
<pre><span style="color: #606060;">  12:</span>         <span style="color: #0000ff;">string</span>     IdentifyingNumber;
<pre><span style="color: #606060;">  13:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>), key]</pre>
<pre><span style="color: #606060;">  14:</span>         <span style="color: #0000ff;">string</span>     Name;</pre>
<pre><span style="color: #606060;">  15:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  16:</span>         <span style="color: #0000ff;">string</span>     SKUNumber;</pre>
<pre><span style="color: #606060;">  17:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  18:</span>         <span style="color: #0000ff;">string</span>     UUID;</pre>
<pre><span style="color: #606060;">  19:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  20:</span>         <span style="color: #0000ff;">string</span>     Vendor;</pre>
<pre><span style="color: #606060;">  21:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>), key]</pre>
<pre><span style="color: #606060;">  22:</span>         <span style="color: #0000ff;">string</span>     Version;</pre>
<pre><span style="color: #606060;">  23:</span> };</pre>

Now that the HW inventory is modified , we still need to update our reporting .Below you will find the code for the report :

Here’s the fun part… win32_computersystemproduct is somehow hard coded into inventory to be resolved into v_gs_client0 , so go look there for the results (once you’ve made the changes)  . Thanks Sherry Kissinger for the tip !!!

<pre><span style="color: #606060;">   1:</span> <span style="color: #0000ff;">select</span>  distinct</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   2:</span>  v_R_System_Valid.ResourceID,</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   3:</span>  v_R_System_Valid.Netbios_Name0 <span style="color: #0000ff;">AS</span> [Computer Name],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   4:</span>  v_R_System_Valid.Resource_Domain_OR_Workgr0 <span style="color: #0000ff;">AS</span> [Domain/Workgroup],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   5:</span>  [Top Console User] = <span style="color: #0000ff;">CASE</span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">   6:</span>  <span style="color: #0000ff;">when</span> (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 <span style="color: #0000ff;">is</span> NULL <span style="color: #0000ff;">or</span> v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = <span style="color: #008000;">'-1') </span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">   7:</span>  <span style="color: #0000ff;">then</span> <span style="color: #008000;">'Unknown' </span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">   8:</span>  <span style="color: #0000ff;">Else</span> v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   9:</span>  <span style="color: #0000ff;">End</span>,</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  10:</span>  v_GS_OPERATING_SYSTEM.Caption0 <span style="color: #0000ff;">AS</span> [Operating System],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  11:</span>  v_GS_OPERATING_SYSTEM.CSDVersion0 <span style="color: #0000ff;">AS</span> [Service Pack Level],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  12:</span>  v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 <span style="color: #0000ff;">AS</span> [Serial Number],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  13:</span>  v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 <span style="color: #0000ff;">AS</span> [Asset Tag],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  14:</span>  v_GS_COMPUTER_SYSTEM.Manufacturer0 <span style="color: #0000ff;">AS</span> [Manufacturer],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  15:</span>  v_GS_COMPUTER_SYSTEM.Model0 <span style="color: #0000ff;">AS</span> [Model],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  16:</span>  v_GS_Client0.Version0 <span style="color: #0000ff;">AS</span> [ModelName],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  17:</span>  v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 <span style="color: #0000ff;">AS</span> [Memory (KBytes)],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  18:</span>  v_GS_PROCESSOR.NormSpeed0 <span style="color: #0000ff;">AS</span> [Processor (GHz)],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  19:</span>  (<span style="color: #0000ff;">Select</span> sum(Size0)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  20:</span>  from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  21:</span>   where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID <span style="color: #0000ff;">and</span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">  22:</span>   v_FullCollectionMembership.CollectionID = @CollectionID) <span style="color: #0000ff;">As</span> [Disk Space (MB)],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  23:</span>  (<span style="color: #0000ff;">Select</span> sum(v_GS_LOGICAL_DISK.FreeSpace0)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  24:</span>  from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  25:</span>  where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID <span style="color: #0000ff;">and</span> v_FullCollectionMembership.CollectionID = @CollectionID) <span style="color: #0000ff;">As</span> [Free Disk Space (MB)]</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  26:</span>  from v_R_System_Valid</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  27:</span>  inner join v_GS_OPERATING_SYSTEM <span style="color: #0000ff;">on</span> (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  28:</span>  left join v_GS_SYSTEM_ENCLOSURE_UNIQUE <span style="color: #0000ff;">on</span> (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  29:</span>  inner join v_GS_COMPUTER_SYSTEM <span style="color: #0000ff;">on</span> (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  30:</span>  inner join v_GS_Client0 <span style="color: #0000ff;">on</span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">  31:</span> ( v_GS_Client0.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  32:</span>  inner join v_GS_X86_PC_MEMORY <span style="color: #0000ff;">on</span> (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  33:</span>  inner join v_GS_PROCESSOR <span style="color: #0000ff;">on</span> (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  34:</span>  inner join v_FullCollectionMembership <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  35:</span>  left  join v_Site <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  36:</span>  inner join v_GS_LOGICAL_DISK <span style="color: #0000ff;">on</span> (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) <span style="color: #0000ff;">and</span> v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  37:</span>  left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP <span style="color: #0000ff;">on</span> (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  38:</span>  Where v_FullCollectionMembership.CollectionID = @CollectionID</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  39:</span>  Order by v_R_System_Valid.Netbios_Name0</pre>
<!--CRLF-->

Hope it Helps ,

Kenny Buntinx

SCCM OSD issue with Win7 x86x64 bit deployment : Error 31

10:12 am in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr Dashboards, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, Deployment, Error 31, OSD, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, sccm2007, Windows 7 by Kenny Buntinx [MVP]

Hi,

I was deploying a windows 7 32/64 bit, at a customer but during the step “setup windows and Configmgr” it fails with the following error :

Windows setup failed, code 31

Failed to run the action: Setup Windows and ConfigMgr.
Unspecified error (Error: 80004005; Source: Windows)

Looking further in the SMSTS.log it looks like :

——SMSTSLog—–

Command line for extension .EXE is "%1" %*    OSDSetupWindows    4/29/2010 7:45:19 PM    1576 (0x0628)
Set command line: "\\<yourSCCMServer>\SMSPKGE$\ABC00014\SOURCES\SETUP.EXE" "/unattend:C:\_SMSTaskSequence\unattend.xml" /noreboot    OSDSetupWindows    4/29/2010 7:45:19 PM    1576 (0x0628)
Executing command line: "\\<yourSCCMServer>\SMSPKGE$\ABC00014\SOURCES\SETUP.EXE" "/unattend:C:\_SMSTaskSequence\unattend.xml" /noreboot    OSDSetupWindows    4/29/2010 7:45:19 PM    1576 (0x0628)
Process completed with exit code 31    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
Windows Setup completed with exit code 31    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
Entering ReleaseSource() for \\<yourSCCMServer>\SMSPKGE$\ABC00014\    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
reference count 1 for the source \\<yourSCCMServer>\SMSPKGE$\ABC00014\ before releasing    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
Released the resolved source \\<yourSCCMServer>\SMSPKGE$\ABC00014\    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
exitCode == 0, HRESULT=80004005 (e:\nts_sms_fre\sms\client\osdeployment\setupwindows\setupwindows.cpp,440)    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
Windows setup failed, code 31    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
setup.run(), HRESULT=80004005 (e:\nts_sms_fre\sms\client\osdeployment\setupwindows\setupwindows.cpp,1707)    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
Exiting with code 0x80004005    OSDSetupWindows    4/29/2010 7:45:34 PM    1576 (0x0628)
Process completed with exit code 2147500037    TSManager    4/29/2010 7:45:34 PM    1384 (0x0568)

——SMSTSLog—–

This error (error 31) could have multiple causes in my environment as described into a previous blog post , see http://scug.be/blogs/sccm/archive/2010/10/28/sccm-osd-issue-with-win7-x64-bit-deployment-on-hp-8740w.aspx.

In my particular case , it worked before and now suddenly it didn’t anymore . The only thing I did , was changing my advertisment to another collection. Now that I know my root cause , lets investigate.

Oh , I am using variables in my unattended.xml file to define my different Keyboard settings as described in one of my previous blog posts ( http://scug.be/blogs/sccm/archive/2010/02/02/sccm-windows-7-deployments-amp-unattended-xml.aspx ) . Did I define them on my new collection ? … Damn , No I didn’t …

Solution : After some testing it became clear that the solution was the following “Define my Collection Variables as shown below !”

I

n order to use the unattended.xml that where containing the variables for the regional , keyboard , etc settings as shown below we need to assign some variables to our collection first.

image

 

On your collection , you add collection variables as shown in the picture below. This gives you the flexibility to create multiple collections with different keyboard layouts , if you for example are a international company.

It now works back like a charm .

Hope it Helps ,

Kenny Buntinx

ConfigMgr Dashboards and Software Update General Compliance Gauge

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