Group Lineage Dataset

[boonhapus]

In ThoughtSpot, you are allowed for a group to be a member of another group - nested groups - in order to achieve complex content access and permission strategies.

With the CS Tools Searchable data model, we expose Groups (TS_GROUP) and Memberships (TS_XREF_PRINCIPAL).

The below query will recursively navigate group hierarchies and show the lineage of that hierarchy in a final column. In order to execute this type of query easily, you can fetch your metadata with a SQLite syncer.

Command to fetch the information
cs_tools tools searchable gather --syncer sqlite://database_path=thoughtspot_metadata.db

SQL Query

WITH group_to_group_memberships AS (
    SELECT DISTINCT
          g.group_guid AS group_guid
        , g.group_name AS group_name
        , s.group_guid AS sub_group_guid
        , s.group_name AS sub_group_name
        , 1            AS group_level
        , g.group_name AS lineage
    FROM
        TS_GROUP AS g
    JOIN
        TS_XREF_PRINCIPAL AS m ON m.group_guid = g.group_guid
    LEFT JOIN
        TS_GROUP AS s ON s.group_guid = m.principal_guid
), groups AS (
    SELECT * FROM group_to_group_memberships WHERE sub_group_guid IS NULL
    UNION ALL
    SELECT
          g.group_guid
        , g.group_name
        , s.group_guid
        , s.group_name
        , s.group_level + 1 AS group_level
        , s.lineage || ' > ' || g.group_name AS lineage
    FROM
        TS_GROUP AS g
    JOIN
        group_to_group_memberships AS s ON s.sub_group_guid = g.group_guid  
)

SELECT
      group_guid
    , group_name
    , group_level
    , CASE WHEN sub_group_guid IS NOT NULL THEN lineage ELSE NULL END AS lineage
FROM
    groups
;

When executing this against my local environment (where maintain hierarchical groups for privilege inheritance purposes), you can see a sample of the results I receive.

| group_guid                           | group_name                 | group_level | lineage                                                            |
| ------------------------------------ | -------------------------- | ----------- | ------------------------------------------------------------------ |
| bb188764-1a1e-4285-a3c7-f1bacfeac272 | team-thoughtspot           | 1           |                                                                    |
| cfbf2ee5-c1ce-45a9-a077-f0d0c2441fcf | team-professional-services | 2           | team-thoughtspot > team-professional-services                      |
| 791793a9-8929-41c3-8fca-2993c6bb7ae5 | team-solutions-consulting  | 2           | team-thoughtspot > team-solutions-consulting                       |
| f8bd6540-9089-43b5-b749-b3dbfaf19b75 | friends-of-solutions       | 2           | team-thoughtspot > friends-of-solutions                            |
| 91625da2-8ffe-42d8-bcd8-1c982f5f3249 | sc-content-builders        | 3           | team-thoughtspot > team-solutions-consulting > sc-content-builders |