‎06-29-2017 09:51 AM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblOperatingsystem.Caption As SO,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When (tblOperatingsystem.Caption = 'Microsoft Windows 7 Professional' And
(Replace(SubQuery1.FileVersion, '.', '') > Replace('6.1.7601.23689', '.',
''))) Then 'OK'
When (tblOperatingsystem.Caption Like 'Microsoft Windows Server 2012 R2%'
And (Replace(SubQuery1.FileVersion, '.', '') > Replace('6.3.9600.18604',
'.', ''))) Then 'OK'
When (tblOperatingsystem.Caption Like 'Microsoft(R) Windows(R) Server 2003%'
And (Replace(SubQuery1.FileVersion, '.', '') > Replace('5.2.3790.6021', '.',
''))) Then 'OK'
When (tblOperatingsystem.Caption =
'Microsoft Windows Server 2008 R2 Standard' And
(Replace(SubQuery1.FileVersion, '.', '') > Replace('6.1.7601.23689', '.',
''))) Then 'OK' Else 'ERR' End As [MS17-010],
TsysLastscan.Lasttime As LastFileScan,
Case SubQuery1.Found When 1 Then 'Yes' Else 'No' End As FileFound,
SubQuery1.FileVersion,
SubQuery1.CompanyName,
SubQuery1.LastModified,
SubQuery1.Lastchanged,
Case
When TsysLastscan.Lasttime < GetDate() -
1 Then
'Last file scan more than 24 hours ago!' End As Comment,
SubQuery1.PatchSearched
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 '%srv.sys') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'files'
Order By tblAssets.Domain,
tblAssets.AssetName
‎07-05-2017 03:35 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblOperatingsystem.Caption As SO,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When (SubQuery1.Found!=1) Then 'NO SRV.SYS'
When (tblOperatingsystem.Caption = 'Microsoft Windows 7 Professional' And (Replace(SubQuery1.FileVersion, '.', '') > Replace('6.1.7601.23689', '.',''))) Then 'OK'
When (tblOperatingsystem.Caption Like 'Microsoft Windows Server 2012 R2%' And (Replace(SubQuery1.FileVersion, '.', '') > Replace('6.3.9600.18604','.', ''))) Then 'OK'
When (tblOperatingsystem.Caption Like 'Microsoft(R) Windows(R) Server 2003%' And (Replace(SubQuery1.FileVersion, '.', '') > Replace('5.2.3790.6021', '.',''))) Then 'OK'
When (tblOperatingsystem.Caption = 'Microsoft Windows Server 2008 R2 Standard' And (Replace(SubQuery1.FileVersion, '.', '') > Replace('6.1.7601.23689', '.',''))) Then 'OK' Else 'ERR' End As [MS17-010],
TsysLastscan.Lasttime As LastFileScan,
Case SubQuery1.Found When 1 Then 'Yes' Else 'No' End As FileFound,
SubQuery1.FileVersion,
SubQuery1.CompanyName,
SubQuery1.LastModified,
SubQuery1.Lastchanged,
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
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 '%srv.sys') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'files'
Order By tblAssets.Domain,
tblAssets.AssetName
‎07-04-2017 03:55 PM
‎07-03-2017 04:14 PM
‎07-03-2017 02:06 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now