Has anyone got a SQL query to detect devices that have Opal Encrypted Drives?

Question

We are updating to Windows 10 and some devices with Opal (early version of it) fail the migration as Sophos needs to decrypt the volume before we can wipe it clean. There doesn't seem to be any SQL key/Table (that I can find) that shows if a volume is hardware or Sophos encrypted. Regards Steve (Support have forwarded me to a few SQL query pages, but they don't have the right info)

This thread was automatically locked due to age.

Jasmin · Accepted Answer

Hi StephenCooper 
 There is no official query for this feature from Sophos side even on the article of the few report query. While you have that script ready, you find the list of computers which are using the OPAL drives through the below statement: 
 "Management of endpoints with Opal-compliant hard drives in SafeGuard Enterprise is transparent, which means that management functions in general work the same as for other endpoints protected by SafeGuard Enterprise. The type of computer is shown in the Inventory of a container in Users and Computers. The column POA Type tells you if the respective computer is encrypted by SafeGuard Enterprise or uses a self-encrypting, Opal-compliant hard drive."

StephenCooper · Answer

use SafeGuard 
 SELECT 
 SAFE_GUARD_DIR . SGD_name as 'Machine name' , 
 IVT_MACHINES . IMA_ENCRYPTED_DRIVES as 'Drive encrypted' , 
 IVT_MACHINES . IMA_UNENCRYPTED_DRIVES as 'Drive not encrypted' , 
 IVT_MACHINES . IMA_POA_TYPE as 'POA Type' , 
 IVT_MACHINES . IMA_LAST_POLICY_RECEIVED as 'Last policy received on' , 
 IVT_MACHINES . IMA_LAST_SYNCHRONIZATION as 'Last synchronization on' 
 FROM IVT_MACHINES INNER JOIN 
 SAFE_GUARD_DIR ON IVT_MACHINES . IMA_MACHINE_ID = SAFE_GUARD_DIR . SGD_ID 
 WHERE ( IVT_MACHINES . IMA_ENCRYPTED_DRIVES IS NOT NULL) 
 order by IVT_MACHINES . IMA_MACHINE_ID 
 
 Thanks for the update again. Running this report and extracting the data helps. 
 The numbers for POA type are: 
 
 1) SGN 
 2) Bitlocker 
 3) Opal

Jasmin · Answer

Hi StephenCooper 
 I am glad that my comment helps you to find the way to get your SQL query for the report. 
 OpalEmergencyDecrypt.exe tool is available to Sophos support engineers as it is an internal tool and need to be used under the monitoring of Support staff. I'd request you to open a support case here . You can reference this article in the description ask them to provide help on the tool mentioning this is an internal tool.

Sophos Customer · Answer

I use synchronizing SQL Server database schemas , therefore, everything is synchronized and recognized.