cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Alaponuke
Engaged Sweeper II

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:

31-05-2023_13-52-21.png

1 ACCEPTED SOLUTION
Alaponuke
Engaged Sweeper II

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

 

 

View solution in original post

7 REPLIES 7
Mercedes_O
Community Manager
Community Manager

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

Alaponuke
Engaged Sweeper II

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
Champion Sweeper III

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
Engaged Sweeper II

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
Engaged Sweeper II

after testing looks like is not counting 

Alaponuke_0-1685701841740.png

 

rader
Champion Sweeper III

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

rader
Champion Sweeper III

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.