Count of completed KB installation Report

[jamie21]

Hi guys,

Am trying to create a report which gives the count of latest KB installed. Here is what I tried.

The below code will give the completed count of mentioned KB installed.

Select Top 1000000 tsysOS.OSname As OS,
Case
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
When tsysOS.OScode Like '10.0.18363%' Then '1909'
End As Version,
Count(Case
When tsysOS.OSname = 'Win 7' Then 'null'
When tsysOS.OSname = 'Win 2008 R2' Then 'null'
When tsysOS.OSname = 'Win 8.1' Then 'null'
When tsysOS.OSname = 'Win 2012 R2' Then 'null'
When tsysOS.OSname = 'Win 2016' Then 'null'
When tsysOS.OScode Like '10.0.17134' Then 'null'
When tsysOS.OScode Like '10.0.17763' Then 'null'
When tsysOS.OScode Like '10.0.18362' Then 'null'
When tsysOS.OScode Like '10.0.18363' Then 'null'
When tsysOS.OSname = 'Win 2019' Then 'null'
End) As Completed,
tblQuickFixEngineeringUni.HotFixID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblQuickFixEngineering On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblAssetCustom.State = 1
And tblQuickFixEngineeringUni.HotFixID = 'KB4556836'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4556843'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4556846'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4556853'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4556813'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4556807'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4551853'
Or tblQuickFixEngineeringUni.HotFixID = 'KB4556799'
Group By tsysOS.OSname,tsysOS.OScode,tblQuickFixEngineeringUni.HotFixID

The result will be like

OS Version Completed Hotfix
Win 10 1803 100 KB4556807
.
.
Win 2019 1809 20 KB4551853

The below code will give the total asset count.

Select Top 1000000 tsysOS.OSname As OS,
Case
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
When tsysOS.OScode Like '10.0.18363%' Then '1909'
End As Version,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Group By tsysOS.OSname,tsysOS.OScode

The result will be like

OS Version Total
Win 10 1803 150
.
.
Win 2019 1809 50

Now, my doubt is how to get it as a single report of both the above reports(Total and Completed)?

Result like

OS Version Total Completed Hotfix
Win 10 1803 150 100 KB4556807
.
.
Win 2019 1809 20 50 KB4551853


Any help?

[jamie21]

Thanks Andy.....

You rocked

[Andy_Sismey · Accepted solution]

Hi Jamie,

Give this a go, I think this correct

Select Top 1000000 tsysOS.OSname As OS,
  Case
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    Else '?'
  End As Version,
  Count(tblAssets.AssetID) As [Total Assets],
  Query1.HotFixID,
  Query1.Totals
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Top 1000000 tsysOS.OSname As OS,
        Count(tblAssets.AssetID) As Totals,
        tblQuickFixEngineeringUni.HotFixID,
        tsysOS.OScode
      From tblAssets
        Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
        Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
        Inner Join tblOperatingsystem On tblAssets.AssetID =
          tblOperatingsystem.AssetID
        Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
        Inner Join tblQuickFixEngineering On tblAssets.AssetID =
          tblQuickFixEngineering.AssetID
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
      Where (tblQuickFixEngineeringUni.HotFixID = 'KB4556836' And
        tblAssetCustom.State = 1) Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4556843') Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4556846') Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4556853') Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4556813') Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4556807') Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4551853') Or
        (tblQuickFixEngineeringUni.HotFixID = 'KB4556799')
      Group By tsysOS.OSname,
        tblQuickFixEngineeringUni.HotFixID,
        tsysOS.OScode) Query1 On Query1.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Group By tsysOS.OSname,
  Query1.HotFixID,
  Query1.Totals,
  tsysOS.OScode
Order By OS

[jamie21]

Thanks Andy,

Its working... But it miss one thing...If none of the system is patched in a OS , it is skipping the total asset too.
For example: If none of the 2008R2 is patched , it is not showing in the result.
How to get it like
OS Total Completed
Win 2008R2 10 0

[Andy_Sismey]

Can you give this a go and see if this is correct for you :

Select Top 1000000 tsysOS.OSname As OS,
  Case
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    Else '?'
  End As Version,
  Count(Case
    When tsysOS.OSname = 'Win 7' Then 'null'
    When tsysOS.OSname = 'Win 2008 R2' Then 'null'
    When tsysOS.OSname = 'Win 8.1' Then 'null'
    When tsysOS.OSname = 'Win 2012 R2' Then 'null'
    When tsysOS.OSname = 'Win 2016' Then 'null'
    When tsysOS.OScode Like '10.0.17134' Then 'null'
    When tsysOS.OScode Like '10.0.17763' Then 'null'
    When tsysOS.OScode Like '10.0.18362' Then 'null'
    When tsysOS.OScode Like '10.0.18363' Then 'null'
    When tsysOS.OSname = 'Win 2019' Then 'null'
  End) As Completed,
  tblQuickFixEngineeringUni.HotFixID,
  Query1.[Total Assets]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblQuickFixEngineering On
    tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
  Inner Join (Select Top 1000000 tsysOS.OSname,
        Count(tblAssets.AssetID) As [Total Assets],
        tblOperatingsystem.Version,
        tsysOS.OScode
      From tblAssets
        Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
        Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
        Inner Join tblOperatingsystem On tblAssets.AssetID =
          tblOperatingsystem.AssetID
      Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
      Group By tsysOS.OSname,
        tblOperatingsystem.Version,
        tsysOS.OScode
      Order By [Total Assets] Desc) Query1 On Query1.OScode = tsysOS.OScode
Where (tblQuickFixEngineeringUni.HotFixID = 'KB4556836' And
  tblAssetCustom.State = 1) Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4556843') Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4556846') Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4556853') Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4556813') Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4556807') Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4551853') Or
  (tblQuickFixEngineeringUni.HotFixID = 'KB4556799')
Group By tsysOS.OSname,
  tblQuickFixEngineeringUni.HotFixID,
  tsysOS.OScode,
  Query1.[Total Assets]