[Help] Chart Report - For counting computers with Bitlocker activated

[Alaponuke]

Hello I created this simple chart report to count how many computers have with BitLocker.

The problem is  some of the computers are no possible to activate and I want to exclude from the chart report, I have another report with all computers but in the chart I just what to show the available devices that had Hardware with tpm.  For Manager view.

SO I would like to exclude some computers by name in the count.

This is the report:

Select Top 1000000 Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Disabled'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Enabled'
    Else 'UNKNOWN'
  End As [Bitlocker Status],
  Count(tblEncryptableVolume.ProtectionStatus) As Total
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblEncryptableVolume On
      tblAssets.AssetID = tblEncryptableVolume.AssetId
Where (tblEncryptableVolume.ProtectionStatus = 0 Or
    tblEncryptableVolume.ProtectionStatus = 1)
Group By Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Disabled'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Enabled'
    Else 'UNKNOWN'
  End,
  tblEncryptableVolume.ProtectionStatus
Order By Total Desc

this is an image of the Chart:

[Mercedes_O]

@Alaponuke please accept as solution to help the response from @rader rank higher in related searches 

[Alaponuke]

hi! thanks for the reply the, report is working fine!! but I just what to add a line to exclude specific computer names, something like where....computer name is not   XXXX or XXXX or xxxx, this way those computers won't take into account, but I don't know where to write this "where" clause.

[rader]

Try adding in the tblAssets.AssetName to the report and then filter based on that field.

Here's an example.

Select Top 1000000 Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Disabled'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Enabled'
    Else 'UNKNOWN'
  End As [Bitlocker Status],
  Count(tblEncryptableVolume.ProtectionStatus) As Total,
  tblAssets.AssetName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblEncryptableVolume On
      tblAssets.AssetID = tblEncryptableVolume.AssetId
Where tblAssets.AssetName Not Like 'v%' And tblAssets.AssetName Not Like
  '%0%' And tblAssets.AssetName Not Like 'p%' And
  (tblEncryptableVolume.ProtectionStatus = 0 Or
    tblEncryptableVolume.ProtectionStatus = 1)
Group By Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Disabled'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Enabled'
    Else 'UNKNOWN'
  End,
  tblEncryptableVolume.ProtectionStatus,
  tblAssets.AssetName
Order By Total Desc

Here I've filtered out any asset that starts with V {'v%'}, has the number 0 in it {'%0%'}  AND assets that start with P {'p%'}. Using the Not Like filters out the undesired assets.

Just adjust to your needs.

Good luck.

[Alaponuke · Accepted solution]

Fixed: this is the code that is working (@rader thank you for your help, you put me in the right direction!!!)

Select Top 1000000 Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Disabled'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Enabled'
    Else 'UNKNOWN'
  End As [Bitlocker Status],
  Count(tblEncryptableVolume.ProtectionStatus) As Total
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblEncryptableVolume On
      tblAssets.AssetID = tblEncryptableVolume.AssetId
Where (tblEncryptableVolume.ProtectionStatus = 0 Or
    tblEncryptableVolume.ProtectionStatus = 1) And tblAssets.AssetName
  Not Like 'ComputerName3' And tblAssets.AssetName Not Like 'ComputerName2'
Group By Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Disabled'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Enabled'
    Else 'UNKNOWN'
  End,
  tblEncryptableVolume.ProtectionStatus
Order By Total Desc

[Alaponuke]

after testing looks like is not counting 

[rader]

Glad I could help. It takes a village and all that 😁.

[rader]

I don't have any Bitlocker systems enabled but you could add tblTPM and maybe use that to sort your data the way you want.