TIBCO Spotfire® Metadata Queries
Last updated:
6:07am Oct 17, 2018

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;

 

Consolidated User Actions

Purpose: Spotfire Action Logging started around v5.5 and has been steadly increased the amount of data that is logged for audit or user tracking purposes. The documentation covers enabling Action Logging at length but basically once enabled the data will be persisted onto the ACTIONLOG table. This table logs all events using an attribute-value system which some defined columns names and some generic column names. The different Action log categoriesAction log actions and Action log entries are explained in the documentation. Therefore extracting information from the ACTIONLOG table is not straight forward unless you know what you are doing. Tibco tried to improve the visibility of the data by adding some basic views which are now part of the Action Log instalation SQL script. But while these views do resolve some of the issues of using an attribute-value table with generic column names they are too granular for general purpose reporting since they are broken down by log category and log action. You could potentially create "master" views to union the more granular views together according to your needs but this will be highly inneficient piece of SQL since you will in effect be querying the ACTIONLOG table multiple times. The view SPF_ACTION_LOG_EVENTS_V below in an attemp to produce a consolidated view over the most relevant user Action events (according to our needs). It covers most of the actions we want to report on in a single pass over the ACTIONLOG table which can then can easily be used in a Spotfire report to produce good stats about who is doign what in your system. 

We use this view joined to other views to get additional information about the user. Unfortunatelly Tibco does not persist the User ID, which is the user's primary key on the user's table. We therefore created a new column called USER_ID on the ACTIONLOG table and added a trigger to populate it accordingly. The User ID might not be populated in all cases, depending on the log event. The trigger also uses an error field called TRIGGER_LOG which will show up any errors found by the trigger trying to populate the USER_ID. For safaty reasons the trigger traps any errors and ends successfully. This guarantees that the logging event will not throw an error. The table changes needed are listed below too.

View Definition:

CREATE OR REPLACE FORCE VIEW SPF_ACTION_LOG_EVENTS_V
(
   EVENT_DATE_TIME,
   IP_ADDRESS,
   USER_ID,
   TIBCO_USER_NAME,
   USER_NAME,
   USER_DOMAIN,
   LOG_CATEGORY,
   LOG_ACTION,
   LOG_ACTION_DESC,
   TOP_FOLDER_NAME,
   LIB_ITEM_ID,
   LIB_ITEM_NAME,
   PAGE_NAME
)
AS
   SELECT   CAST (FROM_TZ (ORIGINAL_TIME, 'UTC') AT TIME ZONE 'Europe/London' AS DATE) AS EVENT_DATE_TIME,
            MACHINE AS IP_ADDRESS,
            USER_ID,
            USER_NAME AS TIBCO_USER_NAME,
            SUBSTR(USER_NAME, 1, INSTR(USER_NAME, '@') - 1) AS USER_NAME, 
            SUBSTR(USER_NAME, INSTR(USER_NAME, '@') + 1) AS USER_DOMAIN,
            LOG_CATEGORY,
            LOG_ACTION,
            CASE
               WHEN LOG_ACTION = 'set_page' 
                    THEN 'Set Active Page'
               WHEN LOG_CATEGORY = 'auth_wp' AND LOG_ACTION IN ('login', 'logout') 
                    THEN 'WP ' || INITCAP (CAST (LOG_ACTION AS VARCHAR2 (30)))
               WHEN LOG_CATEGORY = 'auth_pro' AND LOG_ACTION IN ('login', 'logout') 
                    THEN 'Client ' || INITCAP (CAST (LOG_ACTION AS VARCHAR2 (30)))
               WHEN LOG_ACTION = 'load_content' 
                    THEN 'Client Open DXP from Library' 
               WHEN LOG_CATEGORY = 'file_pro' AND LOG_ACTION = 'load' 
                    THEN 'Client load DXP from file'
               WHEN LOG_CATEGORY = 'library_wp' AND LOG_ACTION = 'load'
                    THEN 'WP Open Uncached Report'
               WHEN LOG_CATEGORY = 'library_wp' AND LOG_ACTION = 'clone'
                    THEN 'WP Open Cached Report'
               WHEN LOG_CATEGORY = 'library_wp' AND LOG_ACTION = 'close'
                    THEN 'Close Report'
            END AS LOG_ACTION_DESC,
            SUBSTR (ID2, 2, INSTR (ID2, '/', 2) - 2) AS TOP_FOLDER_NAME,
            CASE WHEN LOG_CATEGORY NOT LIKE 'auth%' THEN ID1 END AS LIB_ITEM_ID,
            ID2 AS LIB_ITEM_NAME,
            CASE WHEN LOG_ACTION = 'set_page' THEN ARG1 END AS PAGE_NAME
     FROM   ACTIONLOG
    WHERE   ( (LOG_CATEGORY IN ('analysis_wp', 'analysis_pro')
               AND LOG_ACTION = 'set_page')
             OR (LOG_CATEGORY = 'auth_wp'
                 AND LOG_ACTION IN ('login', 'logout'))
             OR (LOG_CATEGORY = 'auth_pro'
                 AND LOG_ACTION IN ('login', 'logout'))
             OR (    LOG_CATEGORY = 'library'
                 AND LOG_ACTION = 'load_content'
                 AND ID2 NOT LIKE '/RelatedItems/AnalysisStates/%')
             OR (LOG_CATEGORY = 'file_pro' AND LOG_ACTION = 'load')
             OR (LOG_CATEGORY = 'library_wp' AND LOG_ACTION IN ('load', 'clone', 'close')))
            AND SUCCESS = 1
            AND USER_NAME NOT IN ('scheduledupdates@SPOTFIRESYSTEM', 'monitoring@SPOTFIRESYSTEM');

Trigger Definition: 

CREATE OR REPLACE TRIGGER ACTION_LOG_ID_T
BEFORE INSERT ON ACTIONLOG  
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpCodeStep         NUMBER;
err_num             NUMBER;

BEGIN

  tmpCodeStep := 1.0;
  
  SELECT ACTION_LOG_ID_SEQ.NEXTVAL
  INTO   :new.ACTION_LOG_ID
  FROM   dual;

  tmpCodeStep := 1.1;
  
  IF :new.USER_NAME IS NOT NULL THEN

          tmpCodeStep := 1.2;

          SELECT usrs.USER_ID
          INTO   :new.USER_ID
          FROM   USERS   usrs
         WHERE   UPPER(usrs.USER_NAME || '@' || usrs.DOMAIN_NAME) = UPPER(:new.USER_NAME);
         
  END IF;  

  tmpCodeStep := 1.3;
  
    EXCEPTION
      WHEN NO_DATA_FOUND THEN 
          err_num := SQLCODE;
         :new.TRIGGER_LOG := '1_' || TO_CHAR(err_num) || '_' || TO_CHAR(tmpCodeStep);
      
      WHEN OTHERS THEN   
          err_num := SQLCODE;
         :new.TRIGGER_LOG := '2_' || TO_CHAR(err_num) || '_' || TO_CHAR(tmpCodeStep);

END;

Table changes: 

ALTER TABLE ACTIONLOG ADD(USER_ID  VARCHAR2(36 BYTE));

ALTER TABLE ACTIONLOG ADD(TRIGGER_LOG  VARCHAR2(1000 BYTE));

 

Update Data Source passwords

Purpose: Oftenly Spotfire administrators need to programatically change/update the Connection URL, DB user, DB user password of Data Sources. As of version 7.11 there is no programatic/API/command way of doing this. This may typically happen when a Data Source is exported/imported ofrom one environment to another one (Spotfire does not import the Data Source password so Spotfire Administrators/Developers need to update the password after the import) but may also be needed in a number of different scenarios:

  1. When deploying applications to a Production Server environment for the first time and the data source does not exist
  2. During the standard development process when moving reports between different Spotfire Server environments
  3. When database credentials change for whatever reason
  4. When a new Spotfire Server environment is created and you need to migrate reports
  5. When creating or updating Database Aliases (as these are stored in the Data Source object in the Spotfire metadata database)
  6. Certain companies enforce password changes to database accounts (this is not a good practice but it's usually hard to change company InfoSec policies, see here)
  7. When refreshing a Spotfire metadata database schema from Production to get a "cloned" version of your Production Library

Our main use case was (7) above. We make a copy of the Production library to a UAT database and we wanted to be able to change the data source details after the database cloning so that the data sources in the UAT environment wouldn't point to the Production databases. Spotfire stores Data Sources in a BLOB column in the LIB_DATA table in the DATA column in the Spotfire metadata database. The BLOB itself it's an XML file which you can download and via with any Oracle SQL client that can handle BLOBs (like TOAD). The main XML tags on the Data Source XML are called connection-url, user and password. The password is encrypted and unfortunatelly TIBCO does not want share the details of how to decrypt this value. This means that to use the method I am showing you have to first save the Connection URL, DB user, DB user password manually in an existing Data Source so that you can obtain the encrypted password value. This means that while you can automate/script the Data Source password it will only work if you know desired Data Source Connection URL, DB user, DB user password in advance. This prerequisite may not make this solution usable to all the use cases you may have but it's better than nothing, which is what you have now. 

The first step for this process is to update your Data Source password with the desired DB connection URL, DB user and DB user password you want to be able to apply programatically via a script. Once you do that you need to identify the ITEM_ID for the Data Source you just updated. The Data Sources view on this wiki page will allow you to query the Spotfire metadata database and find the relevant ITEM_ID for your Data Source. Once you have the ITEM_ID you can use the following SQL (update the ITEM_ID with yours) to obtain the resulting Data Source BLOB object:

Obtain the Data Source BLOB object: 

SET SERVEROUTPUT ON;

DECLARE

  lob_in BLOB;
  i INTEGER := 0;
  lob_size INTEGER;
  buffer_size INTEGER := 1000;
  buffer RAW(32767);

BEGIN

  SELECT DATA, DBMS_LOB.GETLENGTH(DATA)
    INTO LOB_IN, LOB_SIZE
    FROM LIB_DATA
   WHERE ITEM_ID = 'e0f5d96c-29e4-4747-a646-bc96482d983a';

  FOR I IN 0 .. (lob_size / buffer_size) LOOP
    BUFFER := DBMS_LOB.SUBSTR(lob_in, buffer_size, i * buffer_size + 1);
    DBMS_OUTPUT.PUT('DBMS_LOB.APPEND(lob_out, hextoraw(''');
    DBMS_OUTPUT.PUT(RAWTOHEX(BUFFER));
    DBMS_OUTPUT.PUT_LINE('''));');
  END LOOP;

END;

This script uses DBMS output so you need an Oracle SQL client that supports that (TOAD does). The DBMS output of this SQL statement will be something like this:

DBMS_LOB.APPEND(lob_out, hextoraw('E7FF1ABF48037454E4694D58E3CF9DA19DAB6618F24CCE94558F6FAA7AD0CC50.........'));

This SQL script converts the raw data of the BLOB into HEX encoding so that it can be used it on SQL scripts. For simplicity I have shortened the resulting HEX text, yours should be a lot longer. Once you have the resulting HEX you can use the following SQL script to update your Data Source details in any other Spotfire metadata database environment. The only prerequisite is that the same Data Source exists in the environment you want to run the script and that it has the same ITEM_ID. You should replace the ITEM_ID value with your relevant ITEM_ID and the DBMS_LOB.APPEND line with the one you generated on the previous step:

Update desired Data Source: 

DECLARE
  LOB_OUT BLOB;
  LOB_SIZE INTEGER;

BEGIN

  SELECT DATA, dbms_lob.getlength(data)
  INTO LOB_OUT, LOB_SIZE
  FROM LIB_DATA
  WHERE ITEM_ID = 'e0f5d96c-29e4-4747-a646-bc96482d983a'
  FOR UPDATE;

  DBMS_LOB.ERASE(lob_out, LOB_SIZE);

  DBMS_LOB.TRIM (lob_out, 0);

  DBMS_LOB.APPEND(lob_out, hextoraw('E7FF1ABF48037454E4694D58E3CF9DA19DAB6618F24CCE94558F6FAA7AD0CC50.........'));

  COMMIT;

END;
/
EXIT;

That's it! You can now automate/script the whole process and use it when required!

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