→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
renato_gabriel
Engaged Sweeper
Is there a way to determine if the laptop hard drives in my network are encrypted? If so can you provide a way to report?

Thank you!
1 ACCEPTED SOLUTION
sukaitsu
Champion Sweeper
I posted a new report (pending approval) called Laptop: No Encryption ().

For Bitlocke, what you'll want do usually look for MBAM or MDOP MBAM as installed software. The table that contains drive encryption information is "tblEncryptableVolume". I've noticed it only shows encrypted drive status for Bitlocker encrypted drives. For other vendors you usually just have look for certain software or setup custom file scanning to show it exist on the asset. I'll include the SQL for the report I mentioned above, but please mark the report as helpful once it gets posted if it helps.

Thank you,

Jeffrey

Select Top 1000000 A1.AssetID,
A1.AssetName,
A1.Domain,
tblAssetCustom.Model,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'Off'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'On'
End As [Protection Status],
Upper(A1.Userdomain + '\' + A1.Username) As [Last User],
tblADusers.Name,
tblADusers.email,
tblADusers1.Name As Manager,
A1.Lastseen
From tblAssets As A1
Inner Join tblAssetCustom On A1.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure On A1.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Left Outer Join tblEncryptableVolume
On A1.AssetID = tblEncryptableVolume.AssetId
Inner Join tblADusers On tblADusers.Username = A1.Username And
tblADusers.Userdomain = A1.Userdomain
Left Outer Join tblADusers tblADusers1 On tblADusers1.ADObjectID =
tblADusers.ManagerADObjectId
Where A1.AssetID Not In (Select tblRegistry.AssetID From tblRegistry
Where A1.AssetID = tblRegistry.AssetID And tblRegistry.Regkey Like
'%\SOFTWARE\SafeBoot International\SafeBoot Device Encryption') And
A1.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Left Outer Join tblEncryptableVolume
On tblEncryptableVolume.AssetId = tblSoftware.AssetID
Where tblSoftwareUni.softwareName Like 'MBAM%' And
tblEncryptableVolume.ProtectionStatus = 1) And
A1.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Left Outer Join tblEncryptableVolume
On tblEncryptableVolume.AssetId = tblSoftware.AssetID
Where tblSoftwareUni.softwareName Like 'MDOP MBAM%' And
tblEncryptableVolume.ProtectionStatus = 1) And
A1.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'McAfee Agent%') And
A1.AssetID Not In (Select tblFileVersions.AssetID From tblFileVersions
Where A1.AssetID = tblFileVersions.AssetID And tblFileVersions.FilePathfull
Like '%sbsetup.exe' And tblFileVersions.Found = 1) And
A1.AssetID Not In (Select tblFileVersions.AssetID From tblFileVersions
Where A1.AssetID = tblFileVersions.AssetID And tblFileVersions.FilePathfull
Like '%EpePcMonitor.exe' And tblFileVersions.Found = 1) And
(tblEncryptableVolume.DriveLetter Like '[C-D]:' Or
tblEncryptableVolume.DriveLetter Is Null) And
(tblEncryptableVolume.ProtectionStatus = 0 Or
tblEncryptableVolume.ProtectionStatus Is Null) And
(TsysChassisTypes.ChassisName = 'Laptop' Or TsysChassisTypes.ChassisName =
'Notebook' Or TsysChassisTypes.ChassisName = 'Portable') And
tblAssetCustom.State = '1'
Order By A1.AssetName
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com

View solution in original post

1 REPLY 1
sukaitsu
Champion Sweeper
I posted a new report (pending approval) called Laptop: No Encryption ().

For Bitlocke, what you'll want do usually look for MBAM or MDOP MBAM as installed software. The table that contains drive encryption information is "tblEncryptableVolume". I've noticed it only shows encrypted drive status for Bitlocker encrypted drives. For other vendors you usually just have look for certain software or setup custom file scanning to show it exist on the asset. I'll include the SQL for the report I mentioned above, but please mark the report as helpful once it gets posted if it helps.

Thank you,

Jeffrey

Select Top 1000000 A1.AssetID,
A1.AssetName,
A1.Domain,
tblAssetCustom.Model,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'Off'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'On'
End As [Protection Status],
Upper(A1.Userdomain + '\' + A1.Username) As [Last User],
tblADusers.Name,
tblADusers.email,
tblADusers1.Name As Manager,
A1.Lastseen
From tblAssets As A1
Inner Join tblAssetCustom On A1.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure On A1.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Left Outer Join tblEncryptableVolume
On A1.AssetID = tblEncryptableVolume.AssetId
Inner Join tblADusers On tblADusers.Username = A1.Username And
tblADusers.Userdomain = A1.Userdomain
Left Outer Join tblADusers tblADusers1 On tblADusers1.ADObjectID =
tblADusers.ManagerADObjectId
Where A1.AssetID Not In (Select tblRegistry.AssetID From tblRegistry
Where A1.AssetID = tblRegistry.AssetID And tblRegistry.Regkey Like
'%\SOFTWARE\SafeBoot International\SafeBoot Device Encryption') And
A1.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Left Outer Join tblEncryptableVolume
On tblEncryptableVolume.AssetId = tblSoftware.AssetID
Where tblSoftwareUni.softwareName Like 'MBAM%' And
tblEncryptableVolume.ProtectionStatus = 1) And
A1.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Left Outer Join tblEncryptableVolume
On tblEncryptableVolume.AssetId = tblSoftware.AssetID
Where tblSoftwareUni.softwareName Like 'MDOP MBAM%' And
tblEncryptableVolume.ProtectionStatus = 1) And
A1.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'McAfee Agent%') And
A1.AssetID Not In (Select tblFileVersions.AssetID From tblFileVersions
Where A1.AssetID = tblFileVersions.AssetID And tblFileVersions.FilePathfull
Like '%sbsetup.exe' And tblFileVersions.Found = 1) And
A1.AssetID Not In (Select tblFileVersions.AssetID From tblFileVersions
Where A1.AssetID = tblFileVersions.AssetID And tblFileVersions.FilePathfull
Like '%EpePcMonitor.exe' And tblFileVersions.Found = 1) And
(tblEncryptableVolume.DriveLetter Like '[C-D]:' Or
tblEncryptableVolume.DriveLetter Is Null) And
(tblEncryptableVolume.ProtectionStatus = 0 Or
tblEncryptableVolume.ProtectionStatus Is Null) And
(TsysChassisTypes.ChassisName = 'Laptop' Or TsysChassisTypes.ChassisName =
'Notebook' Or TsysChassisTypes.ChassisName = 'Portable') And
tblAssetCustom.State = '1'
Order By A1.AssetName
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com