‎04-14-2015 01:24 PM
Solved! Go to Solution.
‎04-17-2015 10:28 AM
Select Top 1000000 Coalesce(tsysOS.Image, 'notscanned.png') As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Firstseen,
tblAssets.Lastseen,
SubQuery1.Lasttime As LastEncryptionScan,
tblEncryptableVolume.DriveLetter,
Case When tblAssets.AssetID Not In (Select tblEncryptableVolume.AssetId
From tblEncryptableVolume) Then 'unknown'
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged
From tblEncryptableVolume
Right Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 TsysLastscan.AssetID,
TsysLastscan.Lasttime
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'encryptablevolume') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssets.AssetID Not In (Select tblAssets.AssetID
From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tsysOS.OSname Like '%2000%') Or
(tsysOS.OSname Like '%2003%') Or
(tsysOS.OSname Like '%xp%')) And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter
‎04-17-2015 10:28 AM
Select Top 1000000 Coalesce(tsysOS.Image, 'notscanned.png') As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Firstseen,
tblAssets.Lastseen,
SubQuery1.Lasttime As LastEncryptionScan,
tblEncryptableVolume.DriveLetter,
Case When tblAssets.AssetID Not In (Select tblEncryptableVolume.AssetId
From tblEncryptableVolume) Then 'unknown'
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged
From tblEncryptableVolume
Right Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 TsysLastscan.AssetID,
TsysLastscan.Lasttime
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'encryptablevolume') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssets.AssetID Not In (Select tblAssets.AssetID
From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tsysOS.OSname Like '%2000%') Or
(tsysOS.OSname Like '%2003%') Or
(tsysOS.OSname Like '%xp%')) And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter
‎04-15-2015 02:46 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now