Lansweeper report on Bitlocker

[haitam]

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

[haitam · Accepted solution]

thank you very much, i also managed to get what i want, although it took me a lot of time , your answer helped a lot!! Cheers , here is what i came up with:

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

[Graham_Gobell]

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

Try this which shows the encryption status. Only issue is that it may show as Off but if you check the bios it is actually on, but the Bitlocker status in Control Panel of a PC may show as off where it is detected.

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,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
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
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
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