Report on Random KB Articles

[ipirzada]

Trying to setup a generic report query, where i am able to punch in the specific KB's to determine which assets are missing the KB's with their OS version. We are running version 6.0.100.x and currently trying to report on (CVE Microsoft Equation Editor tool (CVE-2017 -11882). Any assistance would appreciated. Thanks

[Bruce_B]

You can find a complete report below that incorporates the where clause found in the reply above. Modify the highlighted text with any other KB number, the report will list all computers that don't have this KB installed.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename = 'Windows' And
Not Exists(Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineering.AssetID = tblAssets.AssetID And
tblQuickFixEngineeringUni.HotFixID = 'KB2952664')
Order By tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

It's not as easy as "punch in a KB number", but if you establish a base query of the fields you want to see, you could include the following in the WHERE clause, editing the HotfixID value for the KB you want to search for.

WHERE
  NOT Exists (SELECT
                tblQuickFixEngineering.AssetID
              FROM
                tblQuickFixEngineering
                Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
              WHERE
                tblQuickFixEngineering.AssetID = tblAssets.AssetID
                AND tblQuickFixEngineeringUni.HotFixID = 'KB2952664'
             )