Report for all without hotfix

[sullivane]

I have been, unsuccessfully, trying to get a report that shows all devices that do not have a certain hotfix I need installed. I found one report on here from 2009 that seems to be outdated. Looking for help.
Thanks!!

[sullivane]

Works great! Thanks Mike!

[MikeMc · Accepted solution]

This should get you started:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1 And
  Not Exists(Select tblQuickFixEngineering.AssetID
  From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
      On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
  Where tblQuickFixEngineering.AssetID = tblAssets.AssetID And
    tblQuickFixEngineeringUni.HotFixID = 'KB2921916')
Order By tblAssets.AssetName

[sullivane]

I found something and I have been playing with it but it's showing the same device and listing it for every hotfix installed that's NOT the one I want, instead of list each device just once if it doesn't have it. So each device is list 50+ times.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblQuickFixEngineeringUni.Description
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.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 tblQuickFixEngineeringUni.HotFixID Not Like '%KB2921916%'
Order By tblAssets.AssetName,
tblQuickFixEngineeringUni.Description