Help modifying report

[amandaw33]

I have custom inventory set up to capture bitlocker status, and a daily email that sends that info to me which is working great.

I'm wondering if I could clean up the report to remove VMs from my bitlocker off report.

This is what my report looks like now.  I'm clueless when it comes to reporting.  Can anyone help?

Thanks.

 

 

[Alex Hedley]

Do you have a Filter with only the machines you want to check this value against?

It depends how you are determining what machines are a VM.

[Igor Perevozchikov]

Seems like you will need to add additional join in Inv_AeX_AC_Identification table and also additional operator with condition in 'Query' tab to exclude 'VM" machines from this report using Inv_AeX_AC_Identification.[Hardware Serial Number] or Inv_AeX_AC_Identification.[BIOS Serial Number]

or try from 'Filter Expression', add/set condition to exclude machines which have data like VMware n Inv_AeX_AC_Identification.[Hardware Serial Number] or Inv_AeX_AC_Identification.[BIOS Serial Number] columns.

VM managed machines sending their unique data there via basic inventory and it looks like 'VMware-42 09 10 5e aa 58 89 f4-07 49 24 9' so better to check Inv_AeX_AC_Identification.[BIOS Serial Number] what other values are there if you have there not only vmware virtual machines.

Inv_AeX_AC_VirtualMachine sql table contains only virtual machines

 

[amandaw33]

I created a filter "VMWare clients" (weird this isn't built in since it shows VM folder on the computer screen under views) - but not sure how to translate that over to the report, unfortunately.

SELECT Guid 
 FROM RM_ResourceVirtual_Machine

 

 

[Igor Perevozchikov]

Could you please export your report into .xml and send it to me for review/possible solution to add there?

[amandaw33]

sure, should have done that from the beginning.  I sometimes forget that's a thing in the console.  Thanks for your time.

 

below is resolved query, too

 

DECLARE @v1_TrusteeScope nvarchar(155)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{AB3A7B62-A109-4DB3-8107-22D28E0C7813},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca4_H_Bitlocker_Status].[BitlockerDriveCEnabled],
   [dca3_AeX AC Primary User].[User]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca3_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_H_Bitlocker_Status] AS [dca4_H_Bitlocker_Status]
         ON ([vri2_Computer].[Guid] = [dca4_H_Bitlocker_Status].[_ResourceGuid])
WHERE
   (
      (
         ([dca4_H_Bitlocker_Status].[BitlockerDriveCEnabled] = 0)
         AND
         (0 = 0)
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )

 

Attachment(s)

Bitlocker Disabled2.xml   15 KB 1 version

[Igor Perevozchikov]

 I'm not able to modify your report, because it depends on custom data classes which aren't available in my database, I thought that probably this way below can help to solve your case (Alex Hedley was on right way :)

1. Import attached custom filter "Only Virtual Machines are in this filter.xml"  (Console -> Filters)

2. Import attached custom filter "Only Physical machines here excluding virtual machines.xml" (Console -> Filters)

3. Clone your existing report and go to Edit mode, put this query in 'Query" tab (Additional operator marked below)

DECLARE @v1_TrusteeScope nvarchar(MAX)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{AB3A7B62-A109-4DB3-8107-22D28E0C7813},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca4_H_Bitlocker_Status].[BitlockerDriveCEnabled],
   [dca3_AeX AC Primary User].[User]

JOIN CollectionMembership cm
    ON cm.[ResourceGuid] = _ItemGuid
          AND cm.[CollectionGuid] = '%Filter%'

FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca3_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_H_Bitlocker_Status] AS [dca4_H_Bitlocker_Status]
         ON ([vri2_Computer].[Guid] = [dca4_H_Bitlocker_Status].[_ResourceGuid])
WHERE
   (
      (
         ([dca4_H_Bitlocker_Status].[BitlockerDriveCEnabled] = 0)
         AND
         (0 = 0)
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )

4. Now go to "Query Parameters" -> +Add -> create a new parameter with basic GUID and specified there guid  49219ba0-c17f-42ce-99c3-a07463e6252c and set name Filter

5. Go to "Report Parameters" and create there new parameter with type "Basic GUID" and "Value Provider" filter choose parameter control --> specify there this GUID as well 49219ba0-c17f-42ce-99c3-a07463e6252c

Attachment(s)

Only Physical machines here excluding virtual machines.xml   2 KB 1 version

Only Virtual Machines are in this filter.xml   2 KB 1 version

[Igor Perevozchikov]

SymConnect page doesn't allow to modify previously mentioned query... Please use this one

 

DECLARE @v1_TrusteeScope nvarchar(MAX)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{AB3A7B62-A109-4DB3-8107-22D28E0C7813},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca4_H_Bitlocker_Status].[BitlockerDriveCEnabled],
   [dca3_AeX AC Primary User].[User]

FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca3_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_H_Bitlocker_Status] AS [dca4_H_Bitlocker_Status]
         ON ([vri2_Computer].[Guid] = [dca4_H_Bitlocker_Status].[_ResourceGuid])

JOIN CollectionMembership cm
ON cm.[ResourceGuid] = _ItemGuid
AND cm.[CollectionGuid] = '%Filter%'

WHERE
   (
      (
         ([dca4_H_Bitlocker_Status].[BitlockerDriveCEnabled] = 0)
         AND
         (0 = 0)
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )

[amandaw33]

set up the new query and report paramater, used 2nd query... and can't save - get error "Report saving failed: An unexpected SQL error occurred when retrieving the schema from the RawSqlDataSource."

I can just live with the VMs... something else in CMS that I didn't think would be so complicated.

 

Thanks for your time, Igor and Alex.

[Igor Perevozchikov]

Hi Sally!

NS log should show exact place of sql query line number where is a mistake. Could you please show it?