Notification

Icon
Error

Count of completed KB installation Report

Posted: Thursday, June 4, 2020 11:49:57 AM(UTC)
jamie21

jamie21

Member Original PosterPosts: 3
0
Like
This issue has been solved! Click here to view the solution
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)? Think

Result like

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


Any help?
Andy.S
#1Andy.S Member Posts: 22  
posted: 6/9/2020 2:40:35 PM(UTC)
Can you give this a go and see if this is correct for you :

Code:
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]
jamie21
#2jamie21 Member Original PosterPosts: 3  
posted: 6/9/2020 6:04:17 PM(UTC)
Thanks Andy,

Its working... Dancing 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.S
#3Andy.S Member Posts: 22  
posted: 6/10/2020 8:41:04 AM(UTC)
Hi Jamie,

Give this a go, I think this correct Drool

Code:
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
#4jamie21 Member Original PosterPosts: 3  
posted: 6/10/2020 11:16:10 AM(UTC)
Thanks Andy.....

You rocked Applause

Active Discussions

Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Today at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Today at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: Yesterday at 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)
Lansweeper Wake on Lan in VLANs
by  FrankSc   Go to last post Go to first unread
Last post: 7/3/2020 1:56:46 PM(UTC)
Lansweeper Ticket Closed = email to all helpdesk desk agents
by  Laurent Maene  
Go to last post Go to first unread
Last post: 7/3/2020 1:23:49 PM(UTC)
Lansweeper Helpdek Call Re-Opened
by  pryan67   Go to last post Go to first unread
Last post: 7/3/2020 1:12:17 PM(UTC)
Lansweeper Office 365 32bit vs 64bit?
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/2/2020 10:35:19 PM(UTC)