Report XP Computers that are Missing KB update

[keys_it]

I have been trying to create a report for the last 3 days that will show me all of my current XP machines that are missing the KB943729 update. This is crucial to only show XP machines as it is the update for Client side extensions. If possible, I would like to have this report sort it by IP location and then Computer Name. Will someone please help me!

Thank you!

[Srikanth08]

Hi,

if it other OS means, like i want to check in all OS means how to update this query?

[Hemoco]

Srikanth08 wrote:
if it other OS means, like i want to check in all OS means how to update this query?

A sample report was posted here: http://lansweeper.com/forum/yaf_postst7317_Report-for-Missing-Hotfix.aspx

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
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 tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'YourFix') And
tsysAssetTypes.AssetTypename = 'windows' And tblAssetCustom.State = 1

[keys_it]

You guys rock! This report worked great. I went to a few of the machines to verify and it was acurate and easy! I am new to Lansweeper but this has helped me modify a couple of other custom reports that I have already created. Thank you again!

[Hemoco · Accepted solution]

Please use the report below for this.

SELECT tsysOS.Image AS icon, tblAssets.AssetID, tblAssets.AssetName, tblAssets.Domain, tblAssets.IPAddress, tsysIPLocations.IPLocation, tsysOS.OSname, tblAssets.SP,
tblAssets.Firstseen, tblAssets.Lastseen
FROM tblAssets INNER JOIN
tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID INNER JOIN
tsysOS ON tblAssets.OScode = tsysOS.OScode LEFT OUTER JOIN
tsysIPLocations ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
WHERE (tblAssetCustom.State = 1) AND (NOT (tblAssets.AssetID IN
(SELECT tblQuickFixEngineering.AssetID
FROM tblQuickFixEngineering INNER JOIN
tblQuickFixEngineeringUni ON tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
WHERE (tblQuickFixEngineeringUni.HotFixID = 'KB943729')))) AND (tsysOS.OSname = 'win xp')
ORDER BY tsysIPLocations.IPLocation, tblAssets.AssetName