Template Report to search for KB that are installed

[volcer]

thanks for taking the time to read this

I looking to make a template to serach server for KB that i can add to or replace also would like to see the break down of servers

[volcer]

ok i added it hopefully this will help someone in the future


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
Case When Qfix1.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022730',
Case When Qfix2.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4021558',
Case When Qfix3.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4018106',
Case When Qfix4.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4012583',
Case When Qfix5.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4012598',
Case When Qfix6.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4018271',
Case When Qfix7.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4018466',
Case When Qfix8.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4019204',
Case When Qfix9.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022747',
Case When Qfix10.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4024323',
Case When Qfix11.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4024402',
Case When Qfix12.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4025218',
Case When Qfix13.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB2748349',
Case When Qfix14.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4021923',
Case When Qfix15.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022008',
Case When Qfix16.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022010',
Case When Qfix17.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022013',
Case When Qfix18.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022883',
Case When Qfix19.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022884',
Case When Qfix20.HotFixID Is Null Then 'No' Else 'Yes' End As 'KB4022887'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022730') As Qfix1
On Qfix1.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4021558') As Qfix2
On Qfix2.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4018106') As Qfix3
On Qfix3.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4012583') As Qfix4
On Qfix4.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4012598') As Qfix5
On Qfix5.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4018271') As Qfix6
On Qfix6.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4018466') As Qfix7
On Qfix7.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4019204') As Qfix8
On Qfix8.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022747') As Qfix9
On Qfix9.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4024323') As Qfix10
On Qfix10.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4024402') As Qfix11
On Qfix11.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4025218') As Qfix12
On Qfix12.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4021903') As Qfix13
On Qfix13.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4021923') As Qfix14
On Qfix14.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022008') As Qfix15
On Qfix15.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022010') As Qfix16
On Qfix16.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022013') As Qfix17
On Qfix17.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022883') As Qfix18
On Qfix18.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022884') As Qfix19
On Qfix19.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4022887') As Qfix20
On Qfix20.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4024402') As Qfix21
On Qfix21.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID,
tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB3217845') As Qfix22
On Qfix22.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And (Case
When Qfix1.HotFixID Is Not Null And Qfix2.HotFixID Is Not Null And
Qfix3.HotFixID Is Not Null And Qfix4.HotFixID Is Not Null And
Qfix5.HotFixID Is Not Null And Qfix6.HotFixID Is Not Null And
Qfix7.HotFixID Is Not Null And Qfix8.HotFixID Is Not Null And
Qfix9.HotFixID Is Not Null And Qfix10.HotFixID Is Not Null And
Qfix11.HotFixID Is Not Null And Qfix12.HotFixID Is Not Null And
Qfix13.HotFixID Is Not Null And Qfix14.HotFixID Is Not Null And
Qfix15.HotFixID Is Not Null And Qfix16.HotFixID Is Not Null And
Qfix17.HotFixID Is Not Null And Qfix18.HotFixID Is Not Null And
Qfix19.HotFixID Is Not Null And Qfix20.HotFixID Is Not Null Then
'Up to date' Else 'Missing KB' End) = 'Missing KB'

[RCorbeil]

Add a filter to the WHERE clause.

Where tblAssetCustom.State = 1
  And tblQuickFixEngineeringUni.HotFixID In ('KB4019264', 'KB4019215')

[volcer]

THanks for taking the time to respond . THat is a great report but how do you look for specific KB's like 'KB4019264' , 'KB4019215'

thanks again for the response

[RCorbeil]

See if this is what you're looking for:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblQuickFixEngineeringUni.HotFixID,
  tblQuickFixEngineering.InstalledOn,
  tblQuickFixEngineering.Lastchanged,
  tblQuickFixEngineeringUni.Description
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblQuickFixEngineering On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where
  tblAssetCustom.State = 1

[volcer]

So there is no template to search for specific KB ? The reason for this we want to test and make sure SCCM and WSUS are pushing and installing the updates to desktops and servers without going to each server to confirm