Report BitLocker protection status for all fixed internal drives

[shawn]

I am currently using the built in report 'Computer: Encryptable volumes' to list the BitLocker Protection status of drives. But I found out that this includes all drive types that support BitLocker. So it is including external USB drives. We are only encrypting internal drives and I need to be able to report which systems have internal drives that are not protected.

There is another report 'Drive: External usb drives' that appears to list external drives. Which is how I can see that the first report is including these drives as well.

I haven't been able to find a custom report or forum post about this. I am terrible at writing reports.

Can anyone help create a report that will list the ON/OFF/Unknown BitLocker protection status of all fixed internal drives?
If that is the proper way to exclude external drives. I don't think Windows can tell the difference between an external eSata drive and an internal Sata drive. But that should be a very rare case in our environment.

[RCorbeil]

Config->Disk->Harddisk is what's recorded in tblDiskDrives.
It only stores Windows computer data pulled from the Win32_LogicalDisk WMI class.

Config->Disk->Partitions is what's recorded in tblDiskPartition.
It only stores Windows computer data pulled from the Win32_DiskPartition WMI class.

Config->Disk->Volumes is what's recorded in tblVolume.
It only stores Windows computer data pulled from the Win32_Volume WMI class.

Config->Disk->Diskdrive is what's recorded in tblFloppy.
It only stores Windows computer data pulled from the Win32_DiskDrive WMI class.

(The "It only stores..." information is copy/pasted from the database documentation.)

It's not that they (LANSweeper) don't want to relate the data, it's that that's what Windows delivers.

[shawn]

RC62N wrote:
Config->
It's not that they (LANSweeper) don't want to relate the data, it's that that's what Windows delivers.

I understand and it's good info. Just trying to get the info that LanSweeper can see (that Windows reports) into a single report.

[RCorbeil]

Thanks. I need the physical Interface Type and the Drive Model information of the Physical Disk that the Logical Volume Drive Letter is using. There must be a way to join a relationship between the data. hmmmm.

Unfortunately, there isn't a direct relationship. The data comes from two different sources, each reporting different things. The closest you're likely to come involves making assumptions.

If you only have one volume (drive letter assigned to a partition) per physical drive and if they're consistently assigned sequentially, you can make a relationship between tblFloppy.Name (e.g. \\.\PHYSICALDRIVE0) and tblEncryptableVolume.DriveLetter (e.g. C:).

Looking at my own inventory, I don't think I could safely make the assumption that any given PHYSICALDRIVEx value can consistently be assumed to be any given drive letter, especially beyond C:. Your situation may differ, of course. Worst case, it may be necessary to manually build yourself a cross-reference list of something like (AssetName+DriveLetter) = tblFloppy.Name so that you can make the inter-table link you need.

[shawn]

RC62N wrote:

Thanks. I need the physical Interface Type and the Drive Model information of the Physical Disk that the Logical Volume Drive Letter is using. There must be a way to join a relationship between the data. hmmmm.

Unfortunately, there isn't a direct relationship. The data comes from two different sources, each reporting different things. The closest you're likely to come involves making assumptions.

If you only have one volume (drive letter assigned to a partition) per physical drive and if they're consistently assigned sequentially, you can make a relationship between tblFloppy.Name (e.g. \\.\PHYSICALDRIVE0) and tblEncryptableVolume.DriveLetter (e.g. C:).

Looking at my own inventory, I don't think I could safely make the assumption that any given PHYSICALDRIVEx value can consistently be assumed to be any given drive letter, especially beyond C:. Your situation may differ, of course. Worst case, it may be necessary to manually build yourself a cross-reference list of something like (AssetName+DriveLetter) = tblFloppy.Name so that you can make the inter-table link you need.

Thanks. I have no SQL abilities. I just copy/paste and hope it doesn't error and I got it right. lol.

Seems there should be a way to grab data from 2 tables and then match on computer name and something else...

I see that when I go to the computer and then Config->Disk that it shows drive letters and Description like Removable and Fixed. I do wish it would show interface type. But is there a report out there that I can pull that info that is already shown under Config->Disk? And then include that with the Volumes Encryptable report?

[RCorbeil]

You don't have a relationship between tblEncryptableVolume and tblFloppy. You can't, in fact, as they're recording two different things: tblFloppy is a record of the physical characteristics of the drive while tblEncryptableVolume is a record of volume (not drive) drive letters and their encryption status.

Depending on what you want/need, you might try using tblDiskDrives instead of tblFloppy.

tblFloppy offers physical characteristics of the drives: model, serial, partitions, size, etc.

tblDiskDrives, despite what the table name suggests, offers volume information rather than drive information.

No guarantees this is what you're after, but hopefully it helps guide you in.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblEncryptableVolume.DriveLetter,
  Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
    Else 'UNKNOWN'
  End As ProtectionStatus,
  tblDiskDrives.FileSystem,
  tblDiskDrives.VolumeName,
  tblDiskDrives.VolumeSerialNumber,
  tblDiskDrives.Size,
  tblDiskDrives.FreeSpace,
  tblEncryptableVolume.LastChanged,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblEncryptableVolume
  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  INNER JOIN tblDiskDrives ON tblDiskDrives.AssetID = tblAssets.AssetID
      AND tblDiskDrives.Caption = tblEncryptableVolume.DriveLetter  -- same drive letter
      AND tblDiskDrives.DriveType = 3                               -- local fixed disk
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Order By tblAssets.AssetName

[shawn]

RC62N wrote:
You don't have a relationship between tblEncryptableVolume and tblFloppy. You can't, in fact, as they're recording two different things: tblFloppy is a record of the physical characteristics of the drive while tblEncryptableVolume is a record of volume (not drive) drive letters and their encryption status.

Depending on what you want/need, you might try using tblDiskDrives instead of tblFloppy.

tblFloppy offers physical characteristics of the drives: model, serial, partitions, size, etc.

tblDiskDrives, despite what the table name suggests, offers volume information rather than drive information.

No guarantees this is what you're after, but hopefully it helps guide you in.

Thanks. I need the physical Interface Type and the Drive Model information of the Physical Disk that the Logical Volume Drive Letter is using. There must be a way to join a relationship between the data. hmmmm.

[shawn]

Here is what I came up with. Sadly it is flawed but the closest I have come so far.
UPDATE: my edit of this report is broken. It is returning incorrect info. If I can correct this, it could be useful enough for my needs.

Here is a sample of what it returned for the same computer name. This computer does have a C, F, and G drive. But the Drive Model field is mixed up and duplicated as it can't have more than 1 type per drive letter. I think I have the join wrong or something.

SameName G: OFF IDE Samsung SSD 850 EVO 500GB
SameName G: OFF SCSI Seagate BUP BK SCSI Disk Device
SameName C: ON SDXC Card
SameName C: ON IDE Samsung SSD 850 EVO 500GB
SameName C: ON SCSI Seagate BUP BK SCSI Disk Device
SameName F: OFF SDXC Card
SameName F: OFF IDE Samsung SSD 850 EVO 500GB
SameName F: OFF SCSI Seagate BUP BK SCSI Disk Device


I took the existing Computer: Encryptable volumes report and added Interface Type and Drive Model. This way when I export to Excel, I can filter out Type = USB. But the main flaw is that not all SCSI type devices are internal. I see several USB external hard drives and USB dock/adapters listed as Type SCSI. It's a fairly small number but I wish there was a way to accurately list only internal drive letters.

I am currently calling this custom report: Computer: Encryptable Volumes with Interface Type

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblEncryptableVolume.DriveLetter,
  Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
    Else 'UNKNOWN'
  End As ProtectionStatus,
  tblFloppy.InterfaceType,
  tblFloppy.Model As [Drive Model],
  tblEncryptableVolume.LastChanged,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblEncryptableVolume
  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Order By tblAssets.AssetName

[CyberCitizen]

Note I have excluded Apple devices as we run a few that run bootcamp and they have been a nightmare, but these are desktops that never leave an office, so minimal risk.

Our laptop fleet all have the necessary security chips and report bitlocker to AD etc.

[CyberCitizen]

This is a report I have which I think covers most of what your after, could be tweaked as necessary.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblEncryptableVolume.DriveLetter,
  Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
    Else 'UNKNOWN'
  End As ProtectionStatus,
  tblEncryptableVolume.LastChanged,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblEncryptableVolume
  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblEncryptableVolume.DriveLetter = 'C:' And Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
    Else 'UNKNOWN'
  End = 'OFF' And tblAssetCustom.Manufacturer Not Like 'Apple Inc.'
Order By tblAssets.AssetName