Symantec Management Platform (Notification Server)

Expand all | Collapse all

filter from report

Jump to Best Answer
  • 1.  filter from report

    Posted 08-24-2020 05:42 PM
    Hello,

    I know the answer has something to do with filters showing the results as it would in manage / computers versus a report that would show custom columns.

    I have been using this report that I would like to use as a filter so that I dont have to repeatedly save the report results as a static report.

    DECLARE @v1_TrusteeScope nvarchar(max)
    SET @v1_TrusteeScope = N'{0ADAE45C-E241-43A7-8DB8-EB5027CAE7EB},{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{A2C6BD1A-9F40-4F11-B747-AAB65F13D7FF},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
    SELECT
    [vri2_Computer].[Guid] AS [_ItemGuid],
    [vri2_Computer].[Name],
    [dca6_Agent Plugin Inventory].[ProductName] AS [Model],
    [dca5_SW BIOS Element].[Version] AS [Bios Version],
    [dca4_HW Processor Name Windows].[Processor Name] AS [HW Proc Name Win.Proc Name],
    [dca3_BitLocker Status].[ProtectionStatus]
    FROM
    [vRM_Computer_Item] AS [vri2_Computer]
    LEFT OUTER JOIN [Inv_BitLocker_Status] AS [dca3_BitLocker Status]
    ON ([vri2_Computer].[Guid] = [dca3_BitLocker Status].[_ResourceGuid])
    LEFT OUTER JOIN [Inv_HW_Processor_Name_Windows] AS [dca4_HW Processor Name Windows]
    ON ([vri2_Computer].[Guid] = [dca4_HW Processor Name Windows].[_ResourceGuid])
    LEFT OUTER JOIN [Inv_SW_BIOS_Element] AS [dca5_SW BIOS Element]
    ON ([vri2_Computer].[Guid] = [dca5_SW BIOS Element].[_ResourceGuid])
    LEFT OUTER JOIN [Inv_Agent_Plugin_Inventory] AS [dca6_Agent Plugin Inventory]
    ON ([vri2_Computer].[Guid] = [dca6_Agent Plugin Inventory].[_ResourceGuid])
    WHERE
    (
    (
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i3%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-7%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-8%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-9%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-10%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-7%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-8%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-9%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-10%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-7%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-8%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-9%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-10%')
    )
    AND
    ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
    )

    How do I get this into a filter? I keep getting a filter sql query must return 1 guid column.
    I did read;
    Symantec Management Platform (Notification Server)

    but couldnt figure out how to modify my report to work in a filter.

    Thank you!
    Giles


  • 2.  RE: filter from report
    Best Answer

    Broadcom Employee
    Posted 08-25-2020 01:08 AM
    Edited by Igor Perevozchikov 08-25-2020 12:40 PM
    It works fine at least on my side when I've created custom filter 'RawSQL' type, put there SQL Query, saved filter and refreshed it
    Try to use SQL Query mentioned below for new created custom filter 'Raw SQL' type

    SELECT
    [vri2_Computer].[Guid] AS [_ItemGuid],
    [vri2_Computer].[Name],
    [dca6_Agent Plugin Inventory].[ProductName] AS [Model],
    [dca5_SW BIOS Element].[Version] AS [Bios Version],
    [dca4_HW Processor Name Windows].[Processor Name] AS [HW Proc Name Win.Proc Name],
    [dca3_BitLocker Status].[ProtectionStatus]
    FROM
    [vRM_Computer_Item] AS [vri2_Computer]
    LEFT OUTER JOIN [Inv_BitLocker_Status] AS [dca3_BitLocker Status]
    ON ([vri2_Computer].[Guid] = [dca3_BitLocker Status].[_ResourceGuid])
    LEFT OUTER JOIN [Inv_HW_Processor_Name_Windows] AS [dca4_HW Processor Name Windows]
    ON ([vri2_Computer].[Guid] = [dca4_HW Processor Name Windows].[_ResourceGuid])
    LEFT OUTER JOIN [Inv_SW_BIOS_Element] AS [dca5_SW BIOS Element]
    ON ([vri2_Computer].[Guid] = [dca5_SW BIOS Element].[_ResourceGuid])
    LEFT OUTER JOIN [Inv_Agent_Plugin_Inventory] AS [dca6_Agent Plugin Inventory]
    ON ([vri2_Computer].[Guid] = [dca6_Agent Plugin Inventory].[_ResourceGuid])
    WHERE
    (
    (
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i3%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-7%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-8%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-9%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i5-10%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-7%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-8%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-9%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i7-10%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-7%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-8%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-9%')
    OR
    ([dca4_HW Processor Name Windows].[Processor Name] LIKE N'%i9-10%')
    ))

    Thanks,
    IP.



    ------------------------------
    Software QA Engineer
    Broadcom Inc.
    ------------------------------



  • 3.  RE: filter from report

    Posted 08-25-2020 09:49 AM
    Thank you! Thank you! Thank you!

    One follow-up please;

    In the report it starts with;

    ​DECLARE @v1_TrusteeScope nvarchar(max)
    SET @v1_TrusteeScope = N'{0ADAE45C-E241-43A7-8DB8-EB5027CAE7EB},{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{A2C6BD1A-9F40-4F11-B747-AAB65F13D7FF},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'

    and ends with;
    AND
    ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
    )

    What are those for and why aren't they needed in the filter?
    Admittedly, I did use report builder to the build the report and then selected the option to convert it to sql query.

    I was told by support a while ago that report builder isn't the best way to create a report. Is this true?

    Thank you again!

    ------------------------------
    Giles
    ------------------------------



  • 4.  RE: filter from report

    Broadcom Employee
    Posted 08-26-2020 12:48 AM
    "TrusteeScope" checks that your currently logged in account has required permissions to see these resources and this check used in reports.
    As you can see, filters aren't the same type of item like reports and mostly they are used in resource targets.
    For example if you check default (built-in) filters, their Query doesn't include TrusteeScope checks

    For report creation its your choice how to build it, using report builder, or you know DB schema and can create your or own SQL Query or the easiest way to create a report, you can use ITMS Views page, an example below:

    Regards,
    IP.


    ------------------------------
    Software QA Engineer
    Broadcom Inc.
    ------------------------------