TIBCO Spotfire® Metadata Queries
Last updated:
6:55am Jul 12, 2017

WARNING: The queries shown below are unsupported by TIBCO as they query the Spotfire Metadata admin database. TIBCO may make changes to the Spotfire Metadata admin database without notice which could break any of these queries at any time. Having said that we have been using these queries since Spotfire 3.3 till now (currently 7.9) and found virtually no issues aside from having to extend the views to cover new functionality being added. 


The purpose of this wiki page is to share user created queries writting to work on the Spotfire Metadata admin database. While these are unsupported (as per the warning above) they can be an invaluable source in obtaining live information from your Spotfire library. I (the wiki page's original creator) have been involved with Spotfire development and administration since v3.3 on the Financial industry where we manage a very large installation (124 Cores/1TB of RAM, 200 Scheduled Updates, 1200 Unique Web Player users per month) and found the need to access this data directly to be able to have more controls and monitoring of our Spotfire Infrastruture.

Oracle Queries

The queries on this section relate to a Spotfire Metadata admin database running in Oracle.

Data Sources

Purpose: The following query will list all Spotfire Data Sources (not to be confused with Data Connections) in the Spotfire Metadata admin database. Spotfire stores data sources as XML file which is stored as BLOB object in the DATA column in the LIB_DATA column. This query uses the UTL_RAW.CAST_TO_VARCHAR2 Oracle function to "extract" the data source attributes from the BLOB object and display them in a tabular format. Unfortunately this function only supports up to 4000 characters since Oracle only supports VARCHAR2 types of up to 4000 characters in SQL (although it supports 32,767 bytes on PL/SQL so you could potentialy perform this conversion in a PL/SQL function). This means that data sources which have an XML definition of more than 4000 characters will not be shown on this query. This can happen if you have lots of Aliases defined in that data source as aliases are stored within the Data Source object. This is something we found annoying as it means you have to reimport a data source on different environments when you create new aliases. This query has been tested to work on Oracle 11.2.0.4 and Spotfire 4.5/6.5/7.9. 

Sample Usage (get all Sqlserver Data Sources):

SELECT * FROM SPF_DATA_SOURCES_V
WHERE DS_TYPE = 'Sqlserver'

Query Definition:

CREATE OR REPLACE FORCE VIEW SPF_DATA_SOURCES_V
(
   DS_ID,
   DS_NAME,
   DS_PATH,
   DS_CREATED_BY_ID,
   DS_CREATED_BY_NAME,
   DS_CREATED_BY_EXTERNAL_ID,
   DS_CREATED_BY_DOMAIN,
   DS_CREATED_DATETIME,
   DS_MODIFIED_BY_ID,
   DS_MODIFIED_BY_NAME,
   DS_MODIFIED_BY_EXTERNAL_ID,
   DS_MODIFIED_BY_DOMAIN,
   DS_MODIFIED_DATETIME,
   DS_TYPE,
   DS_CONN_URL,
   DS_CONN_MIN_CONT,
   DS_CONN_MAX_CONT,
   DS_WRITE_ALLOWED,
   DS_FETCH_SIZE,
   DS_BATCH_SIZE,
   DS_AUTHENTICATION, 
   DS_CREDENTIALS_TIMEOUT,
   DS_INIT_COMMAND    
)
AS
    SELECT  DS_ID,
            DS_NAME,
            DS_PATH,
            DS_CREATED_BY_ID,
            DS_CREATED_BY_NAME,
            DS_CREATED_BY_EXTERNAL_ID,
            DS_CREATED_BY_DOMAIN,
            CAST(FROM_TZ (DS_CREATED_DATETIME, 'UTC') AT TIME ZONE 'Europe/London' AS DATE) AS DS_CREATED_DATETIME,
            DS_MODIFIED_BY_ID,
            DS_MODIFIED_BY_NAME,
            DS_MODIFIED_BY_EXTERNAL_ID,
            DS_MODIFIED_BY_DOMAIN,
            CAST(FROM_TZ (DS_MODIFIED_DATETIME, 'UTC') AT TIME ZONE 'Europe/London' AS DATE) AS DS_MODIFIED_DATETIME,
            INITCAP (EXTRACTVALUE (DATA_BLOB, 'data-source/type')) DS_TYPE,
            EXTRACTVALUE(DATA_BLOB, 'data-source/connection-pool/connection-url') AS DS_CONN_URL,
            EXTRACTVALUE (DATA_BLOB, 'data-source/connection-pool/min-count') AS DS_CONN_MIN_CONT,
            EXTRACTVALUE (DATA_BLOB, 'data-source/connection-pool/max-count') AS DS_CONN_MAX_CONT,
            EXTRACTVALUE(DATA_BLOB, 'data-source/write-allowed') DS_WRITE_ALLOWED,
            EXTRACTVALUE(DATA_BLOB, 'data-source/fetch-size') DS_FETCH_SIZE,
            EXTRACTVALUE(DATA_BLOB, 'data-source/batch-size') DS_BATCH_SIZE,
            EXTRACTVALUE(DATA_BLOB, 'data-source/data-source-authentication') DS_AUTHENTICATION, 
            EXTRACTVALUE(DATA_BLOB, 'data-source/credentials-timeout') DS_CREDENTIALS_TIMEOUT,
            EXTRACTVALUE(DATA_BLOB, 'data-source/connection-pool/init-command') DS_INIT_COMMAND 
       FROM 
            (
             SELECT /*+ NO_PARALLEL(usr1) NO_PARALLEL(usr2) NO_PARALLEL(lida) */ 
                    daso.ITEM_ID AS DS_ID,
                    daso.ITEM_NAME AS DS_NAME,
                    daso.ITEM_PATH AS DS_PATH,
                    daso.ITEM_CREATED_BY AS DS_CREATED_BY_ID,
                    UPPER (usr1.USER_NAME) AS DS_CREATED_BY_NAME,
                    usr1.EXTERNAL_ID AS DS_CREATED_BY_EXTERNAL_ID,
                    UPPER (usr1.DOMAIN_NAME) AS DS_CREATED_BY_DOMAIN,
                    daso.ITEM_CREATED_DATETIME AS DS_CREATED_DATETIME,
                    daso.ITEM_MODIFIED_BY AS DS_MODIFIED_BY_ID,
                    UPPER (usr2.USER_NAME) AS DS_MODIFIED_BY_NAME,
                    usr2.EXTERNAL_ID AS DS_MODIFIED_BY_EXTERNAL_ID,
                    UPPER (usr2.DOMAIN_NAME) AS DS_MODIFIED_BY_DOMAIN,
                    daso.ITEM_MODIFIED_DATETIME AS DS_MODIFIED_DATETIME,
                    CASE 
                        WHEN DBMS_LOB.GETLENGTH(lida.DATA) > 4000 
                        THEN null 
                        ELSE XMLTYPE (
                            UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR(lida.DATA,2000,1)) ||
                            UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR(lida.DATA,2000,2001))
                            )
                        END AS DATA_BLOB
               FROM (SELECT /*+ NO_PARALLEL(parent) NO_PARALLEL(child) NO_PARALLEL(chty) NO_PARALLEL(paty) */
                            child.ITEM_ID,
                            child.PARENT_ID,
                            child.TITLE AS ITEM_NAME,
                            parent.TITLE AS PARENT_NAME,
                            child.CREATED_BY AS ITEM_CREATED_BY,
                            child.MODIFIED_BY AS ITEM_MODIFIED_BY,
                            child.CREATED_DATETIME AS ITEM_CREATED_DATETIME,
                            child.MODIFIED_DATETIME AS ITEM_MODIFIED_DATETIME,
                            chty.DISPLAY_NAME AS CHILD_TYPE,
                            paty.DISPLAY_NAME AS PARENT_TYPE,
                            SUBSTR (child.ITEM_LIBRARY_PATH, 6, 2000) AS ITEM_PATH
                       FROM (    SELECT /*+ NO_PARALLEL(LIB_ITEMS) */ 
                                        ITEM_ID,
                                        CREATED_BY,
                                        MODIFIED_BY,
                                        CREATED AS CREATED_DATETIME,
                                        MODIFIED AS MODIFIED_DATETIME,
                                        PARENT_ID,
                                        ITEM_TYPE,
                                        TITLE,
                                        SYS_CONNECT_BY_PATH (TITLE, '/') AS ITEM_LIBRARY_PATH
                                   FROM LIB_ITEMS
                                  WHERE ITEM_TYPE IN
                                           (SELECT TYPE_ID
                                              FROM LIB_ITEM_TYPES
                                             WHERE DISPLAY_NAME IN ('datasource'))
                                        AND HIDDEN = '0'
                                        AND TITLE NOT IN
                                               ('EmbeddedResources',
                                                'AnalyticItems',
                                                'Bookmarks')
                             START WITH ITEM_ID = '6b67ec30-712e-11dd-7434-00100a64217d'
                             CONNECT BY PRIOR ITEM_ID = PARENT_ID) child,
                            LIB_ITEMS parent,
                            LIB_ITEM_TYPES chty,
                            LIB_ITEM_TYPES paty
                      WHERE     child.PARENT_ID = parent.ITEM_ID
                            AND child.ITEM_TYPE = chty.TYPE_ID
                            AND parent.ITEM_TYPE = paty.TYPE_ID) daso,
                    USERS usr1,
                    USERS usr2,
                    LIB_DATA lida
              WHERE     daso.ITEM_CREATED_BY = usr1.USER_ID
                    AND daso.ITEM_MODIFIED_BY = usr2.USER_ID
                    AND daso.ITEM_ID = lida.ITEM_ID
                    );

Library Items

Purpose: The following query will list all Spotfire Library Items in the Spotfire Metadata admin database where the item type is a folder or a dxp (Analysis). The query "flatens" the data so that it can easily be queried. Spotfire stores date time fields in UTC so the query converts them to the London time zone, amend this to match your local time zone. This query has been tested to work on Oracle 11.2.0.4 and Spotfire 4.5/6.5/7.9. 

Sample Usage (get all DXPs on your Library):

SELECT * FROM SPF_LIBRARY_ITEMS_FLAT_V
WHERE CHILD_TYPE = 'dxp'

Query Definition:

CREATE OR REPLACE FORCE VIEW SPF_LIBRARY_ITEMS_FLAT_V
(
   ITEM_ID,
   PARENT_ID,
   ITEM_NAME,
   PARENT_NAME,
   CHILD_TYPE,
   PARENT_TYPE,
   ITEM_PATH,
   CHILD_CREATED_DATE_TIME,
   CHILD_CREATED_BY_NAME,
   CHILD_MODIFIED_DATE_TIME,
   CHILD_MODIFIED_BY_NAME,
   PARENT_CREATED_DATE_TIME,
   PARENT_CREATED_BY_NAME,
   PARENT_MODIFIED_DATE_TIME,
   PARENT_MODIFIED_BY_NAME
)
AS
   SELECT chld.ITEM_ID,
          chld.PARENT_ID,
          chld.TITLE AS ITEM_NAME,
          pare.TITLE AS PARENT_NAME,
          chty.DISPLAY_NAME AS CHILD_TYPE,
          paty.DISPLAY_NAME AS PARENT_TYPE,
          SUBSTR (chld.ITEM_LIBRARY_PATH, 6, 2000) AS ITEM_PATH,
          chld.CREATED_DATE_TIME AS CHILD_CREATED_DATE_TIME,
          UPPER (usc1.USER_NAME) AS CHILD_CREATED_BY_NAME,
          chld.MODIFIED_DATE_TIME AS CHILD_MODIFIED_DATE_TIME,
          UPPER (usc2.USER_NAME) AS CHILD_MODIFIED_BY_NAME,
          CAST (
             FROM_TZ (pare.CREATED, 'UTC') AT TIME ZONE 'Europe/London' AS DATE)
             AS PARENT_CREATED_DATE_TIME,
          UPPER (usp1.USER_NAME) AS PARENT_CREATED_BY_NAME,
          CAST (
             FROM_TZ (pare.MODIFIED, 'UTC') AT TIME ZONE 'Europe/London' AS DATE)
             AS PARENT_MODIFIED_DATE_TIME,
          UPPER (usp2.USER_NAME) AS PARENT_MODIFIED_BY_NAME
     FROM (    SELECT ITEM_ID,
                      CREATED_BY,
                      MODIFIED_BY,
                      CAST (
                         FROM_TZ (CREATED, 'UTC') AT TIME ZONE 'Europe/London' AS DATE)
                         AS CREATED_DATE_TIME,
                      CAST (
                         FROM_TZ (MODIFIED, 'UTC') AT TIME ZONE 'Europe/London' AS DATE)
                         AS MODIFIED_DATE_TIME,
                      PARENT_ID,
                      ITEM_TYPE,
                      TITLE,
                      SYS_CONNECT_BY_PATH (TITLE, '/') AS ITEM_LIBRARY_PATH
                 FROM LIB_ITEMS
                WHERE ITEM_TYPE IN (SELECT TYPE_ID
                                      FROM LIB_ITEM_TYPES
                                     WHERE DISPLAY_NAME IN ('folder', 'dxp'))
                      AND HIDDEN = '0'
                      AND TITLE NOT IN
                             ('EmbeddedResources', 'AnalyticItems', 'Bookmarks')
           START WITH ITEM_ID = '6b67ec30-712e-11dd-7434-00100a64217d'
           CONNECT BY PRIOR ITEM_ID = PARENT_ID) chld,
          LIB_ITEMS pare,
          LIB_ITEM_TYPES chty,
          LIB_ITEM_TYPES paty,
          USERS usc1,
          USERS usc2,
          USERS usp1,
          USERS usp2
    WHERE     chld.PARENT_ID = pare.ITEM_ID
          AND chld.ITEM_TYPE = chty.TYPE_ID
          AND pare.ITEM_TYPE = paty.TYPE_ID
          AND pare.CREATED_BY = usp1.USER_ID(+)
          AND pare.MODIFIED_BY = usp2.USER_ID(+)
          AND chld.CREATED_BY = usc1.USER_ID(+)
          AND chld.MODIFIED_BY = usc2.USER_ID(+);

Library User Permissions

CREATE OR REPLACE FORCE VIEW SPF_LIBRARY_USER_PRIV_V
(
   FOLDER_ID,
   FOLDER_NAME,
   FOLDER_FULL_PATH,
   USER_ID,
   USER_NAME,
   USER_DOMAIN,
   PERMISSION_1,
   PERMISSION_2,
   PERMISSION_3,
   PERMISSION_4,
   PERMISSION_ALL
)
AS
     SELECT liit.ITEM_ID AS FOLDER_ID,
            liit.TITLE AS FOLDER_NAME,
            flat.ITEM_PATH AS FOLDER_FULL_PATH,
            usrs.USER_ID,
            UPPER (usrs.USER_NAME) AS USER_NAME,
          UPPER (usrs.DOMAIN_NAME)
               AS USER_DOMAIN,
            MAX (CASE WHEN liac.PERMISSION = 'X' THEN 'Access' END)
               AS PERMISSION_1,
            MAX (CASE WHEN liac.PERMISSION = 'R' THEN 'Browse' END)
               AS PERMISSION_2,
            MAX (CASE WHEN liac.PERMISSION = 'W' THEN 'Modify' END)
               AS PERMISSION_3,
            MAX (CASE WHEN liac.PERMISSION = 'O' THEN 'Full Control' END)
               AS PERMISSION_4,
            CASE
               WHEN MAX (
                       CASE WHEN liac.PERMISSION = 'O' THEN 'Full Control' END) =
                       'Full Control'
               THEN
                  'Full Control'
               ELSE
                     MAX (CASE WHEN liac.PERMISSION = 'X' THEN 'Access' END)
                  || MAX (CASE WHEN liac.PERMISSION = 'R' THEN ' + Browse' END)
                  || MAX (CASE WHEN liac.PERMISSION = 'W' THEN ' + Modify' END)
            END
               AS PERMISSION_ALL
       FROM LIB_ACCESS liac,
            LIB_ITEMS liit,
            USERS usrs,
            (SELECT child.ITEM_ID,
                    child.PARENT_ID,
                    child.TITLE AS ITEM_NAME,
                    parent.TITLE AS PARENT_NAME,
                    chty.DISPLAY_NAME AS CHILD_TYPE,
                    paty.DISPLAY_NAME AS PARENT_TYPE,
                    SUBSTR (child.ITEM_LIBRARY_PATH, 6, 2000) AS ITEM_PATH
               FROM (    SELECT ITEM_ID,
                                PARENT_ID,
                                ITEM_TYPE,
                                TITLE,
                                SYS_CONNECT_BY_PATH (TITLE, '/')
                                   AS ITEM_LIBRARY_PATH
                           FROM LIB_ITEMS
                          WHERE ITEM_TYPE IN
                                   (SELECT TYPE_ID
                                      FROM LIB_ITEM_TYPES
                                     WHERE DISPLAY_NAME IN ('folder', 'dxp'))
                                AND HIDDEN = '0'
                                AND TITLE NOT IN
                                       ('EmbeddedResources',
                                        'AnalyticItems',
                                        'Bookmarks')
                     START WITH ITEM_ID =
                                   '6b67ec30-712e-11dd-7434-00100a64217d'
                     CONNECT BY PRIOR ITEM_ID = PARENT_ID) child,
                    LIB_ITEMS parent,
                    LIB_ITEM_TYPES chty,
                    LIB_ITEM_TYPES paty
              WHERE     child.PARENT_ID = parent.ITEM_ID
                    AND child.ITEM_TYPE = chty.TYPE_ID
                    AND parent.ITEM_TYPE = paty.TYPE_ID) flat
      WHERE     liac.ITEM_ID = liit.ITEM_ID
            AND liac.USER_ID = usrs.USER_ID
            AND liac.ITEM_ID = flat.ITEM_ID
   GROUP BY liit.ITEM_ID,
            liit.TITLE,
            flat.ITEM_PATH,
            usrs.USER_ID,
            usrs.USER_NAME,
            usrs.DOMAIN_NAME
   ORDER BY ITEM_PATH, USER_NAME;

Library Group Permissions

CREATE OR REPLACE FORCE VIEW SPOTFIRE_LIBRARY_GROUP_PRIV_V
(
   FOLDER_ID,
   FOLDER_NAME,
   FOLDER_FULL_PATH,
   GROUP_ID,
   GROUP_NAME,
   GROUP_DOMAIN,
   PERMISSION_1,
   PERMISSION_2,
   PERMISSION_3,
   PERMISSION_4,
   PERMISSION_ALL
)
AS
   SELECT FOLDER_ID,
          FOLDER_NAME,
          FOLDER_FULL_PATH,
          GROUP_ID,
          GROUP_NAME,
          GROUP_DOMAIN,
          PERMISSION_1,
          PERMISSION_2,
          PERMISSION_3,
          PERMISSION_4,
          PERMISSION_ALL
     FROM (SELECT FOLDER_ID,
                  FOLDER_NAME,
                  FOLDER_FULL_PATH,
                  GROUP_ID,
                  GROUP_NAME,
                  GROUP_DOMAIN,
                  PERMISSION_1,
                  PERMISSION_2,
                  PERMISSION_3,
                  PERMISSION_4,
                  PERMISSION_ALL
             FROM (  SELECT liit.ITEM_ID AS FOLDER_ID,
                            liit.TITLE AS FOLDER_NAME,
                            flat.ITEM_PATH AS FOLDER_FULL_PATH,
                            grps.GROUP_ID,
                            grps.GROUP_NAME,
                            DOMAIN_NAME AS GROUP_DOMAIN,
                            MAX (
                               CASE
                                  WHEN liac.PERMISSION = 'X' THEN 'Access'
                               END)
                               AS PERMISSION_1,
                            MAX (
                               CASE
                                  WHEN liac.PERMISSION = 'R' THEN 'Browse'
                               END)
                               AS PERMISSION_2,
                            MAX (
                               CASE
                                  WHEN liac.PERMISSION = 'W' THEN 'Modify'
                               END)
                               AS PERMISSION_3,
                            MAX (
                               CASE
                                  WHEN liac.PERMISSION = 'O'
                                  THEN
                                     'Full Control'
                               END)
                               AS PERMISSION_4,
                            CASE
                               WHEN MAX (
                                       CASE
                                          WHEN liac.PERMISSION = 'O'
                                          THEN
                                             'Full Control'
                                       END) = 'Full Control'
                               THEN
                                  'Full Control'
                               ELSE
                                  MAX (
                                     CASE
                                        WHEN liac.PERMISSION = 'X'
                                        THEN
                                           'Access'
                                     END)
                                  || MAX (
                                        CASE
                                           WHEN liac.PERMISSION = 'R'
                                           THEN
                                              ' + Browse'
                                        END)
                                  || MAX (
                                        CASE
                                           WHEN liac.PERMISSION = 'W'
                                           THEN
                                              ' + Modify'
                                        END)
                            END
                               AS PERMISSION_ALL
                       FROM LIB_ACCESS liac,
                            LIB_ITEMS liit,
                            GROUPS grps,
                            (SELECT child.ITEM_ID,
                                    child.PARENT_ID,
                                    child.TITLE AS ITEM_NAME,
                                    parent.TITLE AS PARENT_NAME,
                                    chty.DISPLAY_NAME AS CHILD_TYPE,
                                    paty.DISPLAY_NAME AS PARENT_TYPE,
                                    SUBSTR (child.ITEM_LIBRARY_PATH, 6, 2000)
                                       AS ITEM_PATH
                               FROM (    SELECT ITEM_ID,
                                                PARENT_ID,
                                                ITEM_TYPE,
                                                TITLE,
                                                SYS_CONNECT_BY_PATH (TITLE, '/')
                                                   AS ITEM_LIBRARY_PATH
                                           FROM LIB_ITEMS
                                          WHERE ITEM_TYPE IN
                                                   (SELECT TYPE_ID
                                                      FROM LIB_ITEM_TYPES
                                                     WHERE DISPLAY_NAME IN
                                                              ('folder', 'dxp'))
                                                AND HIDDEN = '0'
                                                AND TITLE NOT IN
                                                       ('EmbeddedResources',
                                                        'AnalyticItems',
                                                        'Bookmarks')
                                     START WITH ITEM_ID =
                                                   '6b67ec30-712e-11dd-7434-00100a64217d'
                                     CONNECT BY PRIOR ITEM_ID = PARENT_ID) child,
                                    LIB_ITEMS parent,
                                    LIB_ITEM_TYPES chty,
                                    LIB_ITEM_TYPES paty
                              WHERE     child.PARENT_ID = parent.ITEM_ID
                                    AND child.ITEM_TYPE = chty.TYPE_ID
                                    AND parent.ITEM_TYPE = paty.TYPE_ID) flat
                      WHERE     liac.ITEM_ID = liit.ITEM_ID
                            AND liac.GROUP_ID = grps.GROUP_ID
                            AND liac.ITEM_ID = flat.ITEM_ID
                   GROUP BY liit.ITEM_ID,
                            liit.TITLE,
                            flat.ITEM_PATH,
                            grps.GROUP_ID,
                            grps.GROUP_NAME,
                            DOMAIN_NAME
                   ORDER BY ITEM_PATH, GROUP_NAME));

Groups Flattened Hierarchy

CREATE OR REPLACE FORCE VIEW SPF_GROUPS_FLAT_HIER_V
(
   PARENT_GROUP_ID,
   PARENT_GROUP_NAME,
   PARENT_GROUP_DOMAIN_NAME,
   PARENT_GROUP_SYNCING,
   CHILD_GROUP_ID,
   CHILD_GROUP_NAME,
   CHILD_GROUP_DOMAIN_NAME,
   CHILD_GROUP_SYNCING,
   PARENT_GROUP_PATH
)
AS
     SELECT flat.PARENT_GROUP_ID,
            grpa.GROUP_NAME AS PARENT_GROUP_NAME,
            grpa.DOMAIN_NAME AS PARENT_GROUP_DOMAIN_NAME,
            CASE WHEN grpa.CONNECTED = 1 THEN 'Yes' 
                 WHEN grpa.CONNECTED = 0 THEN 'No'
                 ELSE NULL
            END AS PARENT_GROUP_SYNCING,
            flat.CHILD_GROUP_ID,
            grch.GROUP_NAME AS CHILD_GROUP_NAME,
            grch.DOMAIN_NAME AS CHILD_GROUP_DOMAIN_NAME,
            CASE WHEN grch.CONNECTED = 1 THEN 'Yes' 
                 WHEN grch.CONNECTED = 0 THEN 'No'
                 ELSE NULL
            END AS CHILD_GROUP_SYNCING,            
            grch.GROUP_NAME || flat.PARENT_GROUP_PATH AS PARENT_GROUP_PATH
       FROM (    SELECT DISTINCT
                        hier.PARENT_ID AS PARENT_GROUP_ID,
                        CONNECT_BY_ROOT gr.GROUP_ID AS CHILD_GROUP_ID,
                        SYS_CONNECT_BY_PATH ( (SELECT GROUP_NAME
                                                 FROM GROUPS
                                                WHERE GROUP_ID = hier.PARENT_ID),
                                             '/')
                           AS PARENT_GROUP_PATH
                   FROM (SELECT GROUP_ID AS PARENT_ID, MEMBER_GROUP_ID AS CHILD_ID
                           FROM GROUP_MEMBERS
                          WHERE MEMBER_USER_ID IS NULL) hier,
                        GROUPS gr
             START WITH hier.CHILD_ID = gr.GROUP_ID
             CONNECT BY PRIOR hier.PARENT_ID = hier.CHILD_ID
             UNION ALL
             SELECT GROUP_ID AS PARENT_GROUP_ID,
                    NULL AS CHILD_GROUP_ID,
                    NULL AS PARENT_GROUP_PATH
               FROM GROUPS) flat,
            GROUPS grch,
            GROUPS grpa
      WHERE flat.PARENT_GROUP_ID = grpa.GROUP_ID(+)
            AND flat.CHILD_GROUP_ID = grch.GROUP_ID(+)
   ORDER BY UPPER (grpa.GROUP_NAME), UPPER (grch.GROUP_NAME);

User Groups Flattened Hierarchy

CREATE OR REPLACE FORCE VIEW SPF_GROUP_USERS_FLAT_V
(
   GROUP_ID,
   GROUP_NAME,
   GROUP_DOMAIN_NAME,
   GROUP_SYNCING,
   USER_ID,
   USER_NAME,
   USER_DOMAIN,
   USER_FULL_DOMAIN,
   USER_EMAIL,       
   USER_ENABLED,
   USER_DISPLAY_NAME,
   USER_LAST_MODIFIED_MEMBERSHIP,
   LAST_LOGIN,
   GRANTED_VIA
)
AS
WITH usgr AS 
(
     SELECT   usrs.USER_ID,
              UPPER(usrs.USER_NAME) AS USER_NAME,
              CAST(FROM_TZ(usrs.LAST_LOGIN, 'UTC') AT TIME ZONE 'Europe/London' AS DATE) AS LAST_LOGIN,
              UPPER(SUBSTR(usrs.EXTERNAL_ID, LENGTH(usrs.EXTERNAL_ID) - INSTR2(REVERSE(CAST(usrs.EXTERNAL_ID AS VARCHAR2 (800))), ',', 1, 3) + 2)) AS USER_FULL_DOMAIN,
              UPPER(usrs.DOMAIN_NAME) AS USER_DOMAIN,
              LOWER(CAST(usrs.EMAIL AS VARCHAR2 (450))) AS USER_EMAIL,       
              usrs.ENABLED AS USER_ENABLED,
              usrs.DISPLAY_NAME AS USER_DISPLAY_NAME, 
              CAST(FROM_TZ(usrs.LAST_MODIFIED_MEMBERSHIP, 'UTC') AT TIME ZONE 'Europe/London' AS DATE) AS USER_LAST_MODIFIED_MEMBERSHIP,
              grps.GROUP_ID,
              grps.GROUP_NAME,
              grps.DOMAIN_NAME AS GROUP_DOMAIN_NAME,
              CASE WHEN grps.CONNECTED = 1 THEN 'Yes' 
                   WHEN grps.CONNECTED = 0 THEN 'No'
                   ELSE NULL
               END AS GROUP_SYNCING
       FROM   USERS                 usrs,
              GROUP_MEMBERS         grme,
              GROUPS                grps
      WHERE   usrs.USER_ID          = grme.MEMBER_USER_ID (+) 
        AND   grme.GROUP_ID         = grps.GROUP_ID (+)
) 
   SELECT GROUP_ID,
          GROUP_NAME,
          GROUP_DOMAIN_NAME,
          GROUP_SYNCING,
          USER_ID,
          USER_NAME,
          USER_DOMAIN,
          USER_FULL_DOMAIN,
          USER_EMAIL,
          USER_ENABLED,
          USER_DISPLAY_NAME,
          USER_LAST_MODIFIED_MEMBERSHIP,
          LAST_LOGIN,
          GROUP_NAME AS GRANTED_VIA
     FROM usgr
   UNION ALL
   SELECT grgr.PARENT_GROUP_ID AS GROUP_ID,
          grgr.PARENT_GROUP_NAME AS GROUP_NAME,
          grgr.PARENT_GROUP_DOMAIN_NAME AS GROUP_DOMAIN_NAME,
          grgr.PARENT_GROUP_SYNCING AS GROUP_SYNCING,
          USER_ID,
          USER_NAME,
          USER_DOMAIN,
          USER_FULL_DOMAIN,
          USER_EMAIL,
          USER_ENABLED,
          USER_DISPLAY_NAME,
          USER_LAST_MODIFIED_MEMBERSHIP,
          LAST_LOGIN,
          grgr.PARENT_GROUP_PATH AS GRANTED_VIA
     FROM usgr, 
          SPF_GROUPS_FLAT_HIER_V grgr
    WHERE usgr.GROUP_ID = grgr.CHILD_GROUP_ID
    ORDER 
       BY 2, 4;

User Emails by Group

Purpose: Spotfire started syncing user emails from LDAP in v6.5. This is very neat as it means you can easily access the user's email address from AD on your Spotfire Admin metadata database. This query uses Oracle's Analytical functions to "bucket" all the user emails for each group in a single CLOB field. This allows you to easily copy/paste this field into Outlook to email a specific set of users belonging to a particular Spotfire group. Since Oracle doesn't support VARCHAR2 fields of more than 4000 bytes in SQL the query breaks the list in 20 buckets of less than 4000. This should handle most of the use cases although you may run out of space if you have groups with large number of users. The 20 buckets are then concatenated into a CLOB which can then be easily used to copy/paste the resulting list of emails. Please be aware that you will need an Oracle SQL client that supports CLOB columns (like TOAD). Otherwise create an Information Link and use Spotfire to see the data as it does support CLOBs.

View Definition:

CREATE OR REPLACE FORCE VIEW SPF_USER_EMAILS_BY_GROUP_V
(
   GROUP_NAME,
   USERS_LIST_CLOB
)
AS
SELECT GROUP_NAME,
       USERS_LIST_CLOB
FROM
    (
    SELECT GROUP_NAME,
           TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 1 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 2 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 3 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 4 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 5 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 6 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 7 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 8 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 9 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 10 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 11 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 12 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 13 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 14 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 15 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 16 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 17 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 18 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 19 THEN USERS_LIST ELSE NULL END)) 
           || TO_CLOB(MAX(CASE WHEN BUCKET_GROUP = 20 THEN USERS_LIST ELSE NULL END)) 
           AS USERS_LIST_CLOB
      FROM 
            (
            SELECT GROUP_NAME,
                   BUCKET_GROUP,
                   LISTAGG(USER_EMAIL, ';') WITHIN GROUP (ORDER BY GROUP_NAME, BUCKET_GROUP) AS USERS_LIST 
              FROM 
                (
                    SELECT GROUP_NAME, 
                           USER_EMAIL, 
                           CUM_LENGTH,
                           TRUNC(CUM_LENGTH / 3500) + 1 AS BUCKET_GROUP
                    FROM
                        (       
                            SELECT GROUP_NAME, USER_EMAIL,
                                     SUM( LENGTH(USER_EMAIL) + 1) 
                                        OVER (PARTITION BY GROUP_NAME ORDER BY GROUP_NAME, USER_EMAIL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUM_LENGTH
                             FROM SPF_GROUP_USERS_FLAT_V
                             WHERE USER_EMAIL        IS NOT NULL
                               AND GROUP_NAME        IS NOT NULL
                             GROUP BY GROUP_NAME, USER_EMAIL
                        )
                ) 
            GROUP BY GROUP_NAME, BUCKET_GROUP
            ORDER BY GROUP_NAME
            )
            GROUP BY GROUP_NAME
    )
ORDER BY GROUP_NAME;

 

Feedback (1)

This is Awesome. Working perfectly fine and very usefull. Please post if you have some otheruse fill queries from Admin Prospective.

 

Thanks,

Amit

amit.leo2404 5:17pm Jul. 10, 2017