cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
keys_it
Engaged Sweeper III
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!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
Srikanth08
Engaged Sweeper II
Hi,

if it other OS means, like i want to check in all OS means how to update this query?
Regards, Srikanth08
Hemoco
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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