SOLVED Custom Report - Excluding a collection

[archon20]

Hello,

I'm not all that familiar with Report Builder and what I am trying to do is take an existing Bitlocker encryption status custom report, and remove VMs from it using either a collection ID or filtering or some other method.

The report has 2 data sets, one of them for a graph report that basically just shows a yes/no stauts, and the other for actual reporting on workstations with more detailed information. It uses the first dataset below to leverage that. So one thing I have found I can do is just to exclude the manufacturer using a filter for vmware and it clears the workstation names out of the list, but it still collects the information and reports them in the overall yes/no graph report. Does anyone have any ideas on an easy way to filter VMs or just a collection even out of the graph report?

DATASET1

SELECT SYS.ResourceID,SYS.Name0, SYS.AD_Site_Name0, USR.Full_User_Name0,
CASE CHCS.ClientActiveStatus
WHEN '1' THEN 'Active'
WHEN '0' THEN 'Inactive' END AS 'Client Active Status',
CASE SYS.Client0
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No'
ELSE 'Unknown' END AS 'SCCM Client Installed',
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
OS.Version0,
EV.DriveLetter0,
CASE EV.ProtectionStatus0
WHEN '0' THEN 'No'
WHEN '1' THEN 'Yes'
WHEN '2' THEN 'Unknown'
END AS 'Bitlocker Enabled',
CASE WHEN (TPM.IsActivated_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Activated],
CASE WHEN (TPM.IsEnabled_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Enabled],
CASE WHEN (TPM.IsOwned_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Owned],
EV.ProtectionStatus0 AS 'Bitlocker Indicator',
WS.LastHWScan
FROM v_R_SYStem SYS
join v_FullCollectionMembership fcm on fcm.resourceid=SYS.resourceid
LEFT JOIN v_gs_operating_system OS on SYS.ResourceID = OS.ResourceID
LEFT JOIN v_r_user USR on USR.User_Name0 = SYS.User_Name0
INNER JOIN v_GS_ENCRYPTABLE_VOLUME EV ON SYS.ResourceID = EV.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE SE ON EV.ResourceID = SE.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM CS ON SE.ResourceID = CS.ResourceID
LEFT OUTER JOIN v_CH_ClientSummary CHCS ON SE.ResourceID = CHCS.ResourceID
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS WS ON CHCS.ResourceID = WS.ResourceID
LEFT JOIN v_GS_TPM TPM ON EV.ResourceID = TPM.ResourceID
WHERE EV.DriveLetter0 = 'C:' AND
SYS.Name0 LIKE ('%' + @Name + '%')
AND EV.ProtectionStatus0 in (@Status)
AND fcm.Collectionid= @Collection
ORDER BY SYS.Name0


DATASET2

select count(Name0) as count,
CASE WHEN ENC.ProtectionStatus0 = 1 THEN 'Yes'
WHEN ENC.ProtectionStatus0 = 0 THEN 'No'
WHEN ENC.ProtectionStatus0 = 2 THEN 'Pin Required'
END AS Protection
from v_GS_ENCRYPTABLE_VOLUME ENC
Join v_R_System SYS on SYS.Resourceid = ENC.ResourceID
where DriveLetter0 = 'C:'
group by Enc.ProtectionStatus0

 

[Garth]

Personally, I would NOT remove VM from the report and VM should (IMO) have Bitlocker enabled.

however add this to the where section and it will remove VMs.
and isnull(SYS.Is_Virtual_Machine0,0) = 0

 

[archon20]

Thanks Garth.

I just realized what I was doing wrong, I needed to add the filter or where statement to both datasets, I was only adding it to the dataset that generated the workstation list, and not adding it to the dataset that the pie graph was using.

I know there are a lot of folks with strong feelings on both sides of that issue, but as it stands it's just not something we are doing and our security team doesn't think it provides real benefit.