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

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."

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

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

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.

In the above script, what does POA type '5' mean?  Is that Bitlocker using the drive's algorithm (these are W10 machines with Bitlocker).  The only one with a '2' is a VMware Workstation instance running windows 8.

Sorry can't answer that one.  Were on version 7 of Safeguard so later versions might be different (we also only had 3 encryption types)

Yeah this is on Version 8.2, W10 clients with TPM (I'm thinking maybe the TPM is the difference, the one that is only "bitlocker" is in a VM without a TPM).

Hi BrucekConvergent 

You can run the SGNSTATE.exe on that client and verify the POA type. POA type has been replaced with Encryption type in the latest versions. Please refer to this article for more information on sgnstate.exe tool.

I think you missed the point, was looking for a way to do it from the Enterprise management database, not interested in running it on a bunch of clients.

Hi BrucekConvergent 

You can run the command on the one particular client where you received the POA type 5 in the reports and confirm the POA type name as this tool will let you know the name of the POA type "5".