July 2011 - Posts
Information concerning the hardware device with the details = "PCI\VEN_1217&DEV_8231&SUBSYS_04A91028&REV_03"
Discription without a driver installed:
Mass Storage Controller
Details:
O2Micro OZ600XXX Memory Card Driver for just about every windows desktop operating system back to XP.
Link:
O2Micro OZ600XXX Memory Card link at Dell Support
Silent installation command line:
setup.exe /s /SMS
Requires a reboot.
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.
This article is a follow-up to the articles I posted on June 18 and July 11, 2011.
Collecting and Reporting on Laptops - Part 1
Collecting and Reporting on Laptops - Part 2
Now that you have created laptop collections, began collecting BitLocker status from your SCCM clients and have a basic report to show encrytpion status, it would be nice to have a single report which provides some comprehensive information upon which you can act. At the company for which I work, we also have Pointsec encrypting the Windows XP machines. I'll be leaving out those specific queries so these reports are not muddled with items I'm not discussing. So, the number of total laptops will NOT match the number of Windows 7 machines. (Just so you know why.)
First, we need to set up the counts so that the numbers presented can be reconciled to management. The following set of queries provides the counts for total number of laptops, Windows 7 laptops, Windows XP laptops, BitLocker compliant laptops, BitLocker non-compliant laptops, and those laptops which are not reporting status one way or the other.
<<>>
-- REPORT SECTION FOR COUNTS --
--
-- Variable declaration section --
-- Declare variables used in the count functions
SET NOCOUNT ON
DECLARE @COLLCOUNT AS INT
DECLARE @COUNTWIN7 AS INT
DECLARE @COUNTWINXP AS INT
DECLARE @LAPTOTAL AS INT
DECLARE @BLCOMP AS INT
DECLARE @BLNON AS INT
DECLARE @BLNOTREP AS INT
-- Calculations section --
-- Count the number of laptops
SELECT
@LAPTOTAL=count(*)
FROM
v_FullCollectionMembership_Valid INNER JOIN
v_R_System ON v_FullCollectionMembership_Valid.ResourceID = v_R_System.ResourceID
-- Conditional equal to the collection ID of the All laptops collection
WHERE
(v_FullCollectionMembership_Valid.CollectionID = 'CEN00390')
-- Count the number of Windows 7 laptops
SELECT
@COUNTWIN7=count(*)
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%')
-- Count the number of Windows XP laptops
SELECT
@COUNTWINXP=count(*)
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 5.1%')
-- Count BitLocker compliant machines
SELECT
@BLCOMP=count(*)
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 =12)) AND
((v_GS_BITLOCKER.DriveLetter0='C:' AND v_GS_BITLOCKER.ProtectionStatus0 = 1)))
-- Count BitLocker non-compliant machines
SELECT
@BLNON=count(*)
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 =12)) AND
((v_GS_BITLOCKER.DriveLetter0='C:' AND v_GS_BITLOCKER.ProtectionStatus0 = 0)))
-- Count machines not reporting BitLocker status *troubleshoot these*
SELECT
@BLNOTREP=@COUNTWIN7-@BLCOMP-@BLNON
-- Display laptop divisions by OS and encryption reporting status
SELECT
@LAPTOTAL [Total Laptops],
@COUNTWINXP [Windows XP],
@COUNTWIN7 [Windows 7],
@BLCOMP [BitLocker Compliant],
@BLNON [BitLocker Non-Compliant (C)],
@BLNOTREP [Not Reporting (D)]
<<>>
In my next post, I'll fill out the reconciliation report with items on which we can act. Below is the report produced from the above queries. (The (C) and (D) will be clarified in my next post.)
This article is a follow-up to the article I posted on June 18, 2011.
Collecting and Reporting on Laptops - Part 1
The first thing that needs to be done in order to get BitLocker information from a computer is to ensure that the status is properly reported to SCCM. I won't go into the details on how to do this as the Configuration Manager Support Team has a very nice blog entry on the procedure here:
Config Mgr Team Blog on BitLocker Status
As the blog entry states, once this is complete a view named v_GS_BITLOCKER will be created in the SCCM database. Columns created in the view are as follows:
- ResourceID
- GroupID
- RevisionID
- AgentID
- TimeStamp
- DeviceID0
- DriveLetter0
- DriverLetter0
- ProtectionStatus0
So, now for a basic report showing encryption status. (Again... lengthy query because I like readability)
<<>>
-- Query to report on BitLocker encryption status
-- Retrieve system name, user name, OS, drive letter, status and chassis
SELECT
v_R_System.Netbios_Name0,
v_R_System.User_Name0,
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 = 14 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
-- Pull only valid chassis types
WHERE
((v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 9) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 9) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 =10) OR
(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 =14))
-- Sort the report by NetBIOS name, then drive letter
ORDER BY
v_R_System.Netbios_Name0, v_GS_BITLOCKER.DriveLetter0
<<>>
In my next post, I'll go into some more reporting which brings it all together - Cheers!
(The report is below)
