WARNING
The queries shown below are unsupported as they query the Spotfire Metadata admin database. Spotfire may make changes to the 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.
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 steadily 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 categories, Action 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. Spotfire tried to improve the visibility of the data by adding some basic views which are now part of the Action Log installation 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 innefficient 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 attempt 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. Unfortunately, Spotfire 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: Frequently, 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 from 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:
- When deploying applications to a Production Server environment for the first time and the data source does not exist
- During the standard development process when moving reports between different Spotfire Server environments
- When database credentials change for whatever reason
- When a new Spotfire Server environment is created and you need to migrate reports
- When creating or updating Database Aliases (as these are stored in the Data Source object in the Spotfire metadata database)
- 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)
- 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 unfortunately Spotfire 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 programmatically 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!
Recommended Comments
There are no comments to display.