Scoping by Organizational Group to Raw SQL reports
Article ID: 179664
Updated On:
Products
Issue/Introduction
How to utilize scoping by Organizational Group/View on a Raw SQL report?
Environment
ITMS 8.x
Resolution
On a Raw SQL Report look for the code highlighted in RED in the sample query below. First there should be a DECLARATION of a variable ScopeCollectionGuid (in this example v2_ScopeCollectionGuid to be exact, as well as a section in the WHERE statement selecting ScopeCollectionGuid FROM fnGetTrusteScopeCollectionsByScope utilizing the ScopeCollectionGuid (v2_ScopeCollectionGuid):
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
DECLARE @v2_ScopeCollectionGuid uniqueidentifier
SET @v2_ScopeCollectionGuid = '%ScopeCollectionGuid%'
SELECT
[vri4_Computer].[Guid] AS [_ItemGuid],
[vri4_Computer].[Name]
FROM
[vRM_Computer_Item] AS [vri4_Computer]
WHERE
(
([vri4_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollectionsByScope(@v1_TrusteeScope, @v2_ScopeCollectionGuid, 1))))
)
It is likely that the above sections are not present - or not present in their entirety. The easiest way to create these statements is to either copy them from this KB article or to create a new report in the SMP console. Then select a default Computer type report and add in Report and Query parameters for the 'Advanced Types > Scope by Organizational Group Parameter'. When you've saved and applied the OG parameters convert the report to a Raw SQL report, this would show you some SQL Query code like the above highlighted sections.
Of note is the WHERE statement. [vri4_Computer].[Guid] is the guid of resources being scoped. In the above quey vri4_Computer is vRM_Computer_Item (as seen in the FROM statement). You will need to modify this to suit your need. If for example you are trying to create a report off of 'vComputer AS vc' you might need to make it [vComputer].[Guid] or [vc].[guid]. This WILL be environmental, so you may need to attempt this multiple times or ask for assistance if this does not work or seem apparant for you given the above instructions.
It is often seen, that built in Raw SQL reports and especially custom reports written does not include the scoping clauses in the WHERE statement. Again, if this is the case add this in as seen above. If you do find that it is added, but that you do not have an ability to select the organizational view/group to perform scoping on, please make sure that the following two items have been added and set on the report;
Under Query Parameters, make sure Scope By Organizational Group Parameter is present. If this is not so, add it by clicking Add > Advanced Types > Scope By Organizational Group Parameter.
Under Report Parameters, make sure Scope By Organizational Group Parameter is present. If this is not so, add it by clicking Add > Advanced Types > Scope By Organizational Grup Parameter.
Having done all this save and test your report. It should now allow you to select an OG/OV as a 'Resource Selection' option within the report.
NOTE: the scoping SQL code in this KB includes what is necessary for also performing scoping per security role. This is the TrusteeScope seen above.
Additional Information
Feedback
