Introduction

Microsoft released the mother of all releases when it comes to SCCM Technical Preview recently and that was Microsoft System Center Configuration Manager Technical Preview version 1905. It contained many features including one which I’m interested in, namely Microsoft BitLocker Administration and Monitoring (MBAM) integrated directly into SCCM, negating the need for a separate MBAM infrastructure.

I showed you how to setup  MBAM in SCCM Technical Preview 1905  here, and if you’ve done that then you may also want to run some SQL queries to obtain and verify data directly from ConfigMgr’s database, as that is the new location of your Recovery Keys and associated data (which were previously stored in the MBAM database).

How can I run a SQL query ?

But first, how can you create a query directly in Microsoft SQL Server ? If you don’t know, then read on, if you do, then skip to the next section. To run a SQL query, start up Microsoft SQL Server Management Studio (MSSMS) and click on the New Query button and type in the following,

select @@version

click on ! Execute to run the query.

That query, when run (or executed) will show you what version of SQL Server you are using in the Results pane.

Cool !

So now that you know how to run queries, let’s see how to get Recovery Key data directly from the ConfigMgr database.

Note: The queries below only work on SCCM Technical Preview version 1905 with the MBAM service enabled and working and with valid data in the database, they may also work with later versions but those versions are not yet released so I cannot confirm that ;-).

Get Recovery key data based on Computer Name

In the screenshot below, you can see a query running on the CM_P01 database (my Technical Preview 1905 database).

Here’s the query, modify the database name (CM_P01) to match your ConfigMgr database name, eg: CM_xxx

use [CM_P01];
select a.Id, a.Name, b.VolumeId, c.RecoveryKeyId, c.RecoveryKey, c.LastUpdateTime from dbo.RecoveryAndHardwareCore_Machines a
inner join dbo.RecoveryAndHardwareCore_Machines_Volumes b ON a.Id = b.MachineId
inner join dbo.RecoveryAndHardwareCore_Keys c ON b.VolumeId = c.VolumeId
where a.name LIKE 'Surface%'

Note: You also need to modify the Computer Name field in the query, in my query I’m searching for Computer Name’s like ‘Surface’, the ‘%’ means search for all results that match any computer name beginning with Surface.

Keep in mind that the Recovery Key is long, you need to expand the columns to get all the data as shown here.

Get Recovery Key based on date

In this query, you are looking for the Recovery Key based on a given date, in this case it’s the last update time.

Here’s the query, modify the database name (CM_P01) to match your ConfigMgr database name, eg: CM_xxx

use [CM_P01];
select a.Id, a.Name, b.VolumeId, c.RecoveryKeyId, c.RecoveryKey, c.LastUpdateTime from RecoveryAndHardwareCore_Machines a 
inner join RecoveryAndHardwareCore_Machines_Volumes b ON a.Id = b.MachineId 
inner join RecoveryAndHardwareCore_eys c ON b.VolumeId = c.VolumeId 
where c.LastUpdateTime >= '2019-05-20'

Get all Recovery Keys based on Computer Name

Here’s the query, modify the database name (CM_P01) to match your ConfigMgr database name, eg: CM_xxx, replace the SurfacePro computer name with one that matches your computer name that you want the details of.

use [CM_P01];
select a.Id, a.Name, b.VolumeId, c.RecoveryKeyId, c.RecoveryKey, c.LastUpdateTime, c.Disclosed from RecoveryAndHardwareCore_Machines a 
inner join RecoveryAndHardwareCore_Machines_Volumes b ON a.Id = b.MachineId 
inner join RecoveryAndHardwareCore_Keys c ON b.VolumeId = c.VolumeId 
where a.name = 'SurfacePro'

Get all Recovery Keys based on Recovery KeyID

Here’s the query, modify the database name (CM_P01) to match your ConfigMgr database name, eg: CM_xxx, replace the RecoveryKeyID with one that matches Recovery Key ID that you want the details of.

use [CM_P01];
select a.Id, a.Name, b.VolumeId, c.RecoveryKeyId, c.RecoveryKey, c.LastUpdateTime, c.Disclosed from RecoveryAndHardwareCore_Machines a 
inner join RecoveryAndHardwareCore_Machines_Volumes b ON a.Id = b.MachineId 
inner join RecoveryAndHardwareCore_Keys c ON b.VolumeId = c.VolumeId 
where RecoveryKeyId LIKE '6734fa14%'

have fun !

cheers

niall