SCCM 2007 - Configuration - Collection and Reporting on Laptops - Part 4
This article is a follow-up to the articles I posted on June 18, July 11, and July 12, 2011.
Collecting and Reporting on Laptops - Part 1
Collecting and Reporting on Laptops - Part 2
Collecting and Reporting on Laptops - Part 3
Now that we have a report which shows our BitLocker compliance counts, we should provide some detail reports so that we can have those which are out of compliance fixed. Identification of problem machines should always be the end goal of set of reports such as these.
The following two queries identify those machines which have drives not encrypted by BitLocker and those machines which are not reporting their status. In the company for which I work, I am only tasked with the requirement for identifying those machiens on which the C: drive is not encrypted. The query I am posting is easy to adjust to show all unencrypted drives on a machine and I'll detail which SQL line to change at the end. Reports (C) and (D) directly correspond to the counts (C) and (D) in the report shown in Part 3 (link posted above.)
<<>>
-- REPORT SECTION FOR DETAILS
-- REPORT (C) - Display laptops not encrypted with BitLocker
SELECT [(C)]=' ', v_R_System.Netbios_Name0,
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 [Primary User],
v_R_System.Operating_System_Name_and0,
v_GS_BITLOCKER.DriveLetter0,
-- Convert the numerical encryption status to something readable
CASE
WHEN v_GS_BITLOCKER.ProtectionStatus0 = 1 THEN 'Encrypted'
ELSE '*** UNENCRYPTED ***'
END AS 'Encryption_Status',
-- Convert the numerical chassis identifier to something readable
CASE
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8 THEN 'Portable'
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 9 THEN 'Laptop'
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10 THEN 'Notebook'
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 12 THEN 'Sub Notebook'
ELSE '*** Unidentified ***'
END AS 'Chassis_Type'
FROM
v_GS_BITLOCKER INNER JOIN
v_R_System ON v_GS_BITLOCKER.ResourceID = v_R_System.ResourceID INNER JOIN
v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID INNER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_R_System.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID
WHERE
(((v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 9) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 =12)) AND
((v_GS_BITLOCKER.DriveLetter0='C:' AND v_GS_BITLOCKER.ProtectionStatus0 = 0)))
-- Sort the report by NetBIOS name, then drive letter
ORDER BY
v_R_System.Netbios_Name0, v_GS_BITLOCKER.DriveLetter0
-- REPORT (D) - Show Windows 7 laptops not reporting in BitLocker
SELECT
[(D)]=' ', v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_R_System.Operating_System_Name_and0,
-- Convert the date value in the pwdLastSet AD attribute to something readable
CAST(v_R_System.pwdLastSet0 / 864000000000.0 - 109207 AS DATETIME) AS Pwd_Last_Changed,
'Not Reporting' [Status]
FROM
v_FullCollectionMembership_Valid INNER JOIN
v_R_System ON v_FullCollectionMembership_Valid.ResourceID = v_R_System.ResourceID
WHERE
(v_FullCollectionMembership_Valid.CollectionID = 'CEN00390') AND
v_R_System.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 6.1%' AND
-- Exclusionary query to identify those not reporting
v_R_System.ResourceID NOT IN
(SELECT
v_R_System.ResourceID
FROM
v_GS_BITLOCKER INNER JOIN
v_R_System ON v_GS_BITLOCKER.ResourceID = v_R_System.ResourceID INNER JOIN
v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE
(((v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 9) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 =14)) AND ((v_GS_BITLOCKER.DriveLetter0='C:'))))
<<>>
If you chain all the reports I've posted together, you can produce a single, consolidated report for BitLocker compliance. This is far easier for a "quick glance" review than having to produce three separate reports. I'm posting the entire report here, but the top two queries are for reports (C) and (D). (As you can see, we have no non-compliant laptops which are reporting in so there are no machines showing up in the report.)
If you have a need to report on all unencrypted drives, change the following line in report (C) in the WHERE section:
((v_GS_BITLOCKER.DriveLetter0='C:' AND v_GS_BITLOCKER.ProtectionStatus0 = 0)))
to:
((v_GS_BITLOCKER.ProtectionStatus0 = 0)))
You can remove one set of parenthesis if you wish, but I generally leave them in just in case I have to add a conditional on the fly that needs to be included in certain AND statements. Changing this statement will produce the following results in that section of the report.
In my next post, I'll lay out some troubleshooting techniques to deal with those which are not reporting.