05-31-2023 01:56 PM - last edited on 06-02-2023 12:56 PM by Mercedes_O
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:
Solved! Go to Solution.
06-02-2023 12:50 PM
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
06-02-2023 12:59 PM
@Alaponuke please accept as solution to help the response from @rader rank higher in related searches
06-01-2023 09:12 AM
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.
06-01-2023 04:29 PM
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.
06-02-2023 12:50 PM
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
06-02-2023 12:31 PM
after testing looks like is not counting
06-02-2023 06:40 PM
Glad I could help. It takes a village and all that 😁.
06-01-2023 12:29 AM
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.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now