Notification

Icon
Error

Report BitLocker protection status for all fixed internal drives

Posted: Tuesday, June 15, 2021 10:35:03 PM(UTC)
_Shawn_

_Shawn_

Member Original PosterPosts: 13
0
Like
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.
CyberCitizen
#1CyberCitizen Member Posts: 429  
posted: 6/15/2021 11:49:25 PM(UTC)
This is a report I have which I think covers most of what your after, could be tweaked as necessary.

Code:
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
CyberCitizen
#2CyberCitizen Member Posts: 429  
posted: 6/15/2021 11:52:54 PM(UTC)
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.
_Shawn_
#3_Shawn_ Member Original PosterPosts: 13  
posted: 6/16/2021 12:05:25 AM(UTC)
Originally Posted by: CyberCitizen Go to Quoted Post
This is a report I have which I think covers most of what your after, could be tweaked as necessary.
...
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.'


You simply took the existing 'Computer: Encryptable volumes' report and added a restriction to only show Drive C and exclude Apple devices.

I need a report that shows only ALL internal drives. The default report at least shows ALL drives compared to your version which only shows Drive C, which I can easily filter the existing report output for that. But I need a report that shows ALL drives but excludes external drives.
_Shawn_
#4_Shawn_ Member Original PosterPosts: 13  
posted: 6/16/2021 2:14:36 AM(UTC)
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

Code:
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
RC62N
#5RC62N Member Posts: 557  
posted: 6/16/2021 2:51:57 PM(UTC)
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.

Code:
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_
#6_Shawn_ Member Original PosterPosts: 13  
posted: 6/16/2021 3:27:12 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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.
RC62N
#7RC62N Member Posts: 557  
posted: 6/16/2021 4:40:15 PM(UTC)
Quote:
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_
#8_Shawn_ Member Original PosterPosts: 13  
posted: 6/16/2021 6:11:52 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
Quote:
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?
RC62N
#9RC62N Member Posts: 557  
posted: 6/16/2021 6:49:22 PM(UTC)
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_
#10_Shawn_ Member Original PosterPosts: 13  
posted: 6/16/2021 6:53:21 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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.

Active Discussions

Lansweeper Device Duplicates
by  Ian.Prentice   Go to last post Go to first unread
Last post: 7/23/2021 11:33:00 AM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: 7/23/2021 9:04:13 AM(UTC)
Lansweeper Computers Listed as Users
by  td1020   Go to last post Go to first unread
Last post: 7/22/2021 5:16:19 PM(UTC)
Lansweeper Can not see second server in error
by  Jürgen  
Go to last post Go to first unread
Last post: 7/22/2021 3:47:19 PM(UTC)
Lansweeper OU missing
by  Fred   Go to last post Go to first unread
Last post: 7/22/2021 2:08:05 PM(UTC)
Lansweeper Certificate status & end dates
by  VysJamesk  
Go to last post Go to first unread
Last post: 7/22/2021 1:07:02 PM(UTC)
Lansweeper NIST 800-171 Compliance
by  rader  
Go to last post Go to first unread
Last post: 7/20/2021 10:13:17 PM(UTC)