Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Displaying the Organizational View Structure via SQL

Created: 10 Feb 2014 • Updated: 10 Feb 2014
SK's picture
+2 2 Votes
Login to vote

When you go to "Manage > Organizational Views and Groups" and select either a Org View or an Org Group, if one or more Org Groups contain resources, the Organizational Group column will display the sub-group hierarchy as a single string value, by seperating each node by a backslash:

Default.jpg

The following query will allow you to target either an Org View or an Org Group so that you can see this same representation by directly interrigating the database:

declare @parentGuid uniqueidentifier;
set @parentGuid = N'Guid of Org View or Org Group goes here';
declare @folders TABLE (  ScopeCollectionGuid uniqueidentifier
                        , ParentScopeCollectionGuid uniqueidentifier
                        , BaseGuid uniqueidentifier)
declare @OrganizationalGroup TABLE (ScopeCollectionGuid uniqueidentifier
                    , OrganizationalGroup nvarchar(1000))

INSERT @folders
SELECT DISTINCT ScopeCollectionGuid = fbf.[FolderGuid]
, ParentScopeCollectionGuid = f.[ParentFolderGuid]
, BaseGuid = ISNULL(ipf.BaseGuid, fbf.FolderGuid )
FROM FolderBaseFolder fbf
INNER JOIN ItemFolder f ON fbf.FolderGuid = f.ItemGuid
LEFT OUTER JOIN ItemPresentation ipf ON ipf.Guid = fbf.FolderGuid
WHERE fbf.ParentFolderGuid = @parentGuid
AND f.[IsFolder] = 1;

WITH Hierarchy AS (
select f.ScopeCollectionGuid, f.ParentScopeCollectionGuid, f.BaseGuid, CAST(s.String as NVARCHAR(1000)) [String]
from @folders f
INNER JOIN String s ON s.BaseGuid=f.BaseGuid AND s.StringRef='item.name' AND s.Culture=''
where f.BaseGuid=@parentGuid
UNION ALL
select f.ScopeCollectionGuid, f.ParentScopeCollectionGuid, f.BaseGuid, CAST(h.String+'\'+s.String as NVARCHAR(1000)) [String]
from @folders f
INNER JOIN String s ON s.BaseGuid=f.BaseGuid AND s.StringRef='item.name' AND s.Culture=''
inner join Hierarchy h ON f.ParentScopeCollectionGuid = h.ScopeCollectionGuid
)
INSERT @OrganizationalGroup
SELECT h.ScopeCollectionGuid, h.String
FROM Hierarchy h
;

/* Example: using the @OrganisationalGroup table variable in a query */

SELECT vi.Guid, vi.Name, og.OrganizationalGroup
from vItem vi
inner join ScopeMembership sm
on sm.ResourceGuid = vi.Guid
inner join @OrganizationalGroup og
on og.ScopeCollectionGuid = sm.ScopeCollectionGuid

ORDER BY vi.Name ASC;

SQL.jpg

AllResources.jpg

SQL2.jpg