Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastFileScan,
Case
When TsysLastscan.Lasttime < GetDate() -
1 Then
'Last file scan more than 24 hours ago! Scanned file information may not be up-to-date. Try rescanning this machine.' End As Comment,
SubQuery1.PatchSearched,
Case SubQuery1.Found When 1 Then 'Yes' Else 'No' End As FileFound,
SubQuery1.FileVersion,
SubQuery1.CompanyName,
SubQuery1.Filesize,
SubQuery1.CreationDate,
SubQuery1.LastAccessed,
SubQuery1.LastModified,
SubQuery1.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblFileVersions.AssetID,
tblFileVersions.FilePathfull As PatchSearched,
tblFileVersions.Found,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged,
tblFileVersions.CreationDate,
tblFileVersions.LastAccessed,
tblFileVersions.LastModified
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%iexplore.exe') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'files'
Order By tblAssets.Domain,
tblAssets.AssetName