‎06-25-2018 11:10 AM
Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As BitLockStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblBIOSHist.SMBIOSBIOSVersion As BiosVersion
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblBIOSHist On tblAssets.AssetID = tblBIOSHist.SMBIOSBIOSVersion
Where tblEncryptableVolume.ProtectionStatus = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName
Solved! Go to Solution.
‎06-28-2018 03:57 PM
Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As BitLockStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblBIOS.SMBIOSBIOSVersion
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName
‎06-28-2018 03:57 PM
Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As BitLockStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblBIOS.SMBIOSBIOSVersion
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName
‎06-26-2018 02:14 PM
haitam wrote:
Hello everyone, i'm currently in my first internship, and i've been asked to create a report with the BitLock status, model of pc , user's name,asset's name, bios version, OS.
I've never used SQL, but i kinda understood the basics by taking some online courses. This is the report i came up with, but it still displays:"This report has no results!" , it's my first internship, and i would really appreciate some help, thanks a lot:Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As BitLockStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblBIOSHist.SMBIOSBIOSVersion As BiosVersion
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblBIOSHist On tblAssets.AssetID = tblBIOSHist.SMBIOSBIOSVersion
Where tblEncryptableVolume.ProtectionStatus = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now