Custom Report - Exclude certain asset groups

[bcallaha_01]

Hello,

I have a report that shows pcs that are not encrypted via bitlocker. I have some PCs in this report that cannot be encrypted and I want to exclude them.  I could exclude one by one using Where tblAssets.AssetName Not Like 'XXX' but the report would get a bit messy.

I have attempted to drop them in an asset group and exclude that particular asset group, but they still belong to the "default group" which you cannot remove PCs from, so they still show up on the report.  I have also tried the custom fields, comments, and Descriptions of the PCs, but when I include the "not Like" XXX nothing returns because I dont have anything in these feilds normally.

Any advice on what to try next is welcome!

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Case
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
tsysIPLocations.IPLocation,
tblOperatingsystem.Caption As OS,
tblAssets.Version,
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblEncryptableVolume.DriveLetter = 'C:' And Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
Else 'UNKNOWN'
End = 'OFF' And tblAssetCustom.Manufacturer Not Like 'Apple Inc.' And
tblAssetCustom.Manufacturer Not Like 'VMware, Inc.' And
tblAssetCustom.Manufacturer Not Like 'Virtual Machine'
Order By tblAssets.AssetName

[rom]

If you mean that they actually cannot be encrypted by bitlocker, then I would report on the TPM Module version of the computers, as that is usually the determining factor for what can be encrypted or not.  Generally TPM has to be 1.2 or higher for bitlocker to work.  You could do a group policy (I think, its been quite a while) to "allow bitlocker without a compatible TPM" -  Make sure though, to make a report that shows everything so that 1) you dont' forget they are there and 2) for compliance and security reasons.

Here's a report you can mess around with:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblTPM.SpecVersion,
  Case
    When tblTPM.IsActivated_InitialValue = 1 Then 'Yes'
    When tblTPM.IsActivated_InitialValue Is Null Then Null
    Else 'No'
  End As Activated,
  Case
    When tblTPM.IsEnabled_InitialValue = 1 Then 'Yes'
    When tblTPM.IsEnabled_InitialValue Is Null Then Null
    Else 'No'
  End As Enabled,
  Case
    When tblTPM.IsOwned_InitialValue = 1 Then 'Yes'
    When tblTPM.IsOwned_InitialValue Is Null Then Null
    Else 'No'
  End As Owned,
  tblOperatingsystem.Caption As OS,
  tblAssets.SP,
  tblEncryptableVolume.DriveLetter,
  Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
    Else 'UNKNOWN'
  End As ProtectionStatus,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblPortableBattery.AssetID Is Null Then 'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
  Case
    When tblTPM.AssetId Is Null Then 'noTPM'
    Else 'HasTPM'
  End As HasTPMorNot,
  tblTPM.IsEnabled_InitialValue,
  tblTPM.IsOwned_InitialValue,
  tblTPM.ManufacturerVersionInfo,
  tblTPM.ManufacturerVersion,
  tblTPM.ManufacturerId,
  tblTPM.PhysicalPresenceVersionInfo
From tblAssets
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
  Inner Join tblTPM On tblAssets.AssetID = tblTPM.AssetId
  Left Join tblEncryptableVolume On
      tblAssets.AssetID = tblEncryptableVolume.AssetId
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
    tblAssetCustom.Model Not Like '%Virtual%') And
  tblOperatingsystem.Caption Not Like '%professional%' And
  tblEncryptableVolume.DriveLetter Like '%C%' And tblAssets.Lastseen Is Not Null
  And tblAssets.Lastseen <> '' And tblState.Statename = 'Active' And
  tsysAssetTypes.AssetTypename In ('Windows')
Order By tblAssets.Domain,
  tblAssets.AssetName