Security audits, we all love them right? Being asked the same questions by management such as are our machines encrypted and how can I get a report to show that. So taking BitLocker encryption as an example, how can we generate dynamic reports and share them either as quick ad-hoc reports or via a scheduled upload mechanism?

PowerBI FTW

There are reporting tools for BitLocker, MBAM for instance is included with SA on Windows 10 Enterprise. This got me thinking though as to the possibilities of PowerBI to publish this information, especially given not every environment uses Enterprise licensing and lets face it SSRS is a bit graphically dated.

So here we are then, a fully functional BitLocker PowerBI report showing you all the vital information such as the machine name, OS, encryption state, encryption cipher strength etc:

Publish the report to your IT Team using the publish option or automate scheduling of the report via PowerBI gateway for continuously updated reports.

Note PowerBI gateway requires PowerBI Pro or Office 365 E5 licensing.

Download the PowerBI Template from Microsoft Technet – https://gallery.technet.microsoft.com/BitLocker-Compliance-bd91ac13.

Remember to edit the Query in the Advanced Editor and replace the YOURSQLSERVER and YOURDBNAME entries with your own environment details.

Pre-Requisite Steps

So here are the steps required to publish your BitLocker compliance settings via PowerBI:

BitLocker Hardware Inventory Class

The starting point for evaluating your BitLocker compliance is to ensure that ConfigMgr is collecting inventory on the encrypted drives. To do this you will need to add a hardware class into the client settings – hardware inventory settings. To do this, simply perform the following actions;

  1. Open your ConfigMgr Admin Console
  2. Click on the Administration tab
  3. Click on the Client Settings option
  4. Select the client settings that apply you wish to report compliance on
  5. Click on the Hardware Inventory section and then click on the Set Classes button

  6. Type in “BitLocker” and select the class (Win32_EncryptableVolume)

  7. Now refresh the Computer Policy and then run the Hardware Inventory to gather the required pre-requisite information

BitLocker Settings Custom Class

In order to report on the BitLocker settings applied to your clients we now need to add a custom hardware inventory class. To do this we need to edit the Configuration.MOF file located in the “C:\Program Files\Microsoft Configuration Manager\inboxes\clifiles.src\hinv” directory.

  1. Take a backup of the Configuration.MOF file
  2. Edit the file and look for the section starting :

    //========================
    // Added extensions start
    //========================

    <INSERT HERE>

    //========================
    // Added extensions end
    //========================

  3. Paste in the following code between the Start and End blocks:
    #pragma namespace ("\\\\.\\root\\cimv2")
    #pragma deleteclass("BitLockerSettings", NOFAIL)
    [DYNPROPS]
    Class BitLockerSettings
    {
    [key] string KeyName;
    Uint32 ActiveDirectoryBackup;
    Uint32 RequireActiveDirectoryBackup;
    Uint32 ActiveDirectoryInfoToStore;
    Uint32 EncryptionMethod;
    Uint32 MinimumPIN;
    Uint32 OSRecovery;
    Uint32 OSManageDRA;
    Uint32 OSRecoveryPassword;
    Uint32 OSRecoveryKey;
    Uint32 OSHideRecoveryPage;
    Uint32 OSActiveDirectoryBackup;
    Uint32 OSActiveDirectoryInfoToStore;
    Uint32 OSRequireActiveDirectoryBackup;
    Uint32 UseAdvancedStartup;
    Uint32 EnableBDEWithNoTPM;
    Uint32 UseTPM;
    Uint32 UseTPMPIN;
    Uint32 UseTPMKey;
    Uint32 UseTPMKeyPIN;
    Uint32 RecoveryKeyMessageSource;
    String RecoveryKeyMessage;
    String RecoveryKeyUrl;
    Uint32 OSEncryptionType;
    Uint32 DisallowStandardUserPINReset;
    Uint32 EncryptionMethodNoDiffuser;
    };
    
    [DYNPROPS]
    Instance of BitLockerSettings
    {
    KeyName="BitLockerSettings";
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|ActiveDirectoryBackup"),Dynamic,Provider("RegPropProv")] ActiveDirectoryBackup;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|RequireActiveDirectoryBackup"),Dynamic,Provider("RegPropProv")] RequireActiveDirectoryBackup;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|ActiveDirectoryInfoToStore"),Dynamic,Provider("RegPropProv")] ActiveDirectoryInfoToStore;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|EncryptionMethod"),Dynamic,Provider("RegPropProv")] EncryptionMethod;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|MinimumPIN"),Dynamic,Provider("RegPropProv")] MinimumPIN;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSRecovery"),Dynamic,Provider("RegPropProv")] OSRecovery;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSManageDRA"),Dynamic,Provider("RegPropProv")] OSManageDRA;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSRecoveryPassword"),Dynamic,Provider("RegPropProv")] OSRecoveryPassword;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSRecoveryKey"),Dynamic,Provider("RegPropProv")] OSRecoveryKey;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSHideRecoveryPage"),Dynamic,Provider("RegPropProv")] OSHideRecoveryPage;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSActiveDirectoryBackup"),Dynamic,Provider("RegPropProv")] OSActiveDirectoryBackup;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSActiveDirectoryInfoToStore"),Dynamic,Provider("RegPropProv")] OSActiveDirectoryInfoToStore;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSRequireActiveDirectoryBackup"),Dynamic,Provider("RegPropProv")] OSRequireActiveDirectoryBackup;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|UseAdvancedStartup"),Dynamic,Provider("RegPropProv")] UseAdvancedStartup;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|EnableBDEWithNoTPM"),Dynamic,Provider("RegPropProv")] EnableBDEWithNoTPM;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|UseTPM"),Dynamic,Provider("RegPropProv")] UseTPM;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|UseTPMPIN"),Dynamic,Provider("RegPropProv")] UseTPMPIN;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|UseTPMKey"),Dynamic,Provider("RegPropProv")] UseTPMKey;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|UseTPMKeyPIN"),Dynamic,Provider("RegPropProv")] UseTPMKeyPIN;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|RecoveryKeyMessageSource"),Dynamic,Provider("RegPropProv")] RecoveryKeyMessageSource;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|RecoveryKeyMessage"),Dynamic,Provider("RegPropProv")] RecoveryKeyMessage;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|RecoveryKeyUrl"),Dynamic,Provider("RegPropProv")] RecoveryKeyUrl;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|OSEncryptionType"),Dynamic,Provider("RegPropProv")] OSEncryptionType;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|DisallowStandardUserPINReset"),Dynamic,Provider("RegPropProv")] DisallowStandardUserPINReset;
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\FVE|EncryptionMethodNoDiffuser"),Dynamic,Provider("RegPropProv")] EncryptionMethodNoDiffuser;
    };
    
    // this section tells the inventory agent what to report to the server
    // 14/11/2017 09:47:57
    
    #pragma namespace ("\\\\.\\root\\cimv2\\SMS")
    #pragma deleteclass("BitLockerSettings", NOFAIL)
    [SMS_Report(TRUE),SMS_Group_Name("BitLockerSettings"),SMS_Class_ID("Custom|BitLockerSettings|1.0"),
    SMS_Context_1("__ProviderArchitecture=32|uint32"),
    SMS_Context_2("__RequiredArchitecture=true|boolean")]
    Class BitLockerSettings
    {
    [SMS_Report(TRUE),key] string KeyName;
    [SMS_Report(TRUE)] Uint32 ActiveDirectoryBackup;
    [SMS_Report(TRUE)] Uint32 RequireActiveDirectoryBackup;
    [SMS_Report(TRUE)] Uint32 ActiveDirectoryInfoToStore;
    [SMS_Report(TRUE)] Uint32 EncryptionMethod;
    [SMS_Report(TRUE)] Uint32 MinimumPIN;
    [SMS_Report(TRUE)] Uint32 OSRecovery;
    [SMS_Report(TRUE)] Uint32 OSManageDRA;
    [SMS_Report(TRUE)] Uint32 OSRecoveryPassword;
    [SMS_Report(TRUE)] Uint32 OSRecoveryKey;
    [SMS_Report(TRUE)] Uint32 OSHideRecoveryPage;
    [SMS_Report(TRUE)] Uint32 OSActiveDirectoryBackup;
    [SMS_Report(TRUE)] Uint32 OSActiveDirectoryInfoToStore;
    [SMS_Report(TRUE)] Uint32 OSRequireActiveDirectoryBackup;
    [SMS_Report(TRUE)] Uint32 UseAdvancedStartup;
    [SMS_Report(TRUE)] Uint32 EnableBDEWithNoTPM;
    [SMS_Report(TRUE)] Uint32 UseTPM;
    [SMS_Report(TRUE)] Uint32 UseTPMPIN;
    [SMS_Report(TRUE)] Uint32 UseTPMKey;
    [SMS_Report(TRUE)] Uint32 UseTPMKeyPIN;
    [SMS_Report(TRUE)] Uint32 RecoveryKeyMessageSource;
    [SMS_Report(TRUE)] String RecoveryKeyMessage;
    [SMS_Report(TRUE)] String RecoveryKeyUrl;
    [SMS_Report(TRUE)] Uint32 OSEncryptionType;
    [SMS_Report(TRUE)] Uint32 DisallowStandardUserPINReset;
    [SMS_Report(TRUE)] Uint32 EncryptionMethodNoDiffuser;
    };
  4. Save the file
  5. Open a PowerShell or CMD window and browse to the directory
  6. Type in the following:
    MOFComp.exe .\Configuration.MOF
  7. Confirm that the settings have been applied successfully

  8. Import the custom class into your Default Client Settings. 

    Note: Remember to deselect the value from the list after importing if you want to selectively apply the custom hardware inventory

  9. Select the BitLockerSettings class in each of your client settings where you wish to capture the configuration details from.
  10. Now refresh the Computer Policy and then run the Hardware Inventory

SQL Query

So now we should have information on BitLocker enabled volumes, we can check this in SQL Management Studio by running the following SQL query on the ConfigMgr database:

SELECT rs.Name0 , ev.DriveLetter0, ev.ProtectionStatus0, os.Caption0, os.BuildNumber0, clienttype.SystemRole0, systemenc.ChassisTypes0, bitsetting.EncryptionMethod0, bitsetting.OSEncryptionType0
FROM dbo.v_GS_ENCRYPTABLE_VOLUME as ev
INNER JOIN dbo.v_R_System as rs ON ev.ResourceID = rs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM as os ON os.ResourceID = ev.ResourceID
INNER JOIN dbo.v_GS_SYSTEM as clienttype ON clienttype.ResourceID = ev.ResourceID
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE as systemenc ON systemenc.ResourceID = ev.ResourceID
INNER JOIN dbo.v_GS_BITLOCKERSETTINGS as bitsetting ON bitsetting.ResourceID = ev.ResourceID
WHERE ev.DriveLetter0 IS NOT NULL

Running this you should have something similar to the below;

At this point we now have a list of computers, their chassis type, protection state and operating system so lets progress to putting this together in PowerBI.

PowerBI Field Naming

In order to provide a more friendly means of viewing the data, we will have to add calculated fields:

Chassis Type

Machine Type = SWITCH([Chassis Type],”2″,”Desktop”,”3″,”Desktop”,”4″,”Desktop”,”6″,”Desktop”,”15″,”Desktop”,”16″,”Desktop”,”9″,”Laptop”,”10″,”Laptop”,”12″,”Laptop”,”1″,”Server”,”7″,”Server”,”23″,”Server”)

BitLocker State

BitLocker Enabled = if(BitLockerInformation[Protection Status]=1,”Protected”,”Not Protected”)

Encryption Cipher

Encryption Cipher = IF(BitLockerInformation[EncryptionMethod0]=1,”AES 128 with Diffuser”,IF(BitLockerInformation[EncryptionMethod0]=2,”AES 256 with Diffuser”,IF(BitLockerInformation[EncryptionMethod0]=3,”AES 128″,IF(BitLockerInformation[EncryptionMethod0]=4,”AES 256″,IF(BitLockerInformation[EncryptionMethod0]=6,”AES-XTS 128″,IF(BitLockerInformation[EncryptionMethod0]=7,”AES-XTS 256″))))))

Customising

Now you should be in a position whereby you have the data, its time to customise the look and feel with different graphs and brand your report to suit your needs.

(1461)

Maurice Daly

Maurice has been working in the IT industry since 1999 and was awarded his first MVP Enterprise Mobility award in 2017. Technology focus includes Active Directory, Group Policy, Hyper-V, Windows Deployment (SCCM & MDT) and Office 365.

comments
  • Eric Schloss
    Posted at 22:39 November 14, 2017
    Eric Schloss
    Reply
    Author

    Very nice solution. Thanks for posting. We have a lot of systems in our environment using AES256 with diffuser cipher, but it wasn’t taken into account in the statement to populate the Encryption Cipher column. I updated the statement to take into account all 7 possible values for EncryptionMethod: Encryption Cipher = IF(‘BitLockerInformation'[EncryptionMethod0]=0,”NONE”,IF(‘BitLockerInformation'[EncryptionMethod0]=1,”AES 128 w/ Diffuser”,IF(‘BitLockerInformation'[EncryptionMethod0]=2,”AES 256 w/ Diffuser”,IF(‘BitLockerInformation'[EncryptionMethod0]=3,”AES 128″,IF(‘BitLockerInformation'[EncryptionMethod0]=4,”AES 256″,IF(‘BitLockerInformation'[EncryptionMethod0]=5,”Hardware Encryption”,IF(‘BitLockerInformation'[EncryptionMethod0]=6,”AES-XTS 128″,IF(‘BitLockerInformation'[EncryptionMethod0]=7,”AES-XTS 256″))))))))

  • Ashok
    Posted at 12:54 November 17, 2017
    Ashok
    Reply
    Author

    i get the below error when trying to add the section to Configuration.mof

    https://imgur.com/MlHnLQH

    however if i remove the #PRAGMA AUTORECOVER it will give me the below when running in powershell -> MOFComp.exe .\Configuration.MOF

    PS D:\Program Files\Microsoft Configuration Manager\inboxes\clifiles.src\hinv> mofcomp.exe .\configuration.mof
    Microsoft (R) MOF Compiler Version 10.0.10586.117
    Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
    Parsing MOF file: .\configuration.mof
    MOF file has been successfully parsed
    Storing data in the repository…
    WARNING: File .\configuration.mof does not contain #PRAGMA AUTORECOVER.
    If the WMI repository is rebuilt in the future, the contents of this MOF file will not be included in the new WMI reposi
    tory.
    To include this MOF file when the WMI Repository is automatically reconstructed, place the #PRAGMA AUTORECOVER statement
    on the first line of the MOF file.
    Done!
    PS D:\Program Files\Microsoft Configuration Manager\inboxes\clifiles.src\hinv>

    removing #PRAGMA AUTORECOVER seems to work so do i not need that?

    Thanks,

    Ash

    • Maurice Daly
      Posted at 13:28 November 17, 2017
      Maurice Daly
      Reply
      Author

      Actually the #PRAGMA AUTORECOVER entry shouldn’t have been included in the post, this was coming from a custom MOF file I was using. If the import does complain, simply add that entry as the first line in the MOF. Once it has compiled though you are good to go.

  • Leave a Reply