filter from report

[Iskyfly]

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

[Igor Perevozchikov]

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.
------------------------------

[Iskyfly]

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
------------------------------

[Igor Perevozchikov]

"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.
------------------------------