How to extract a list of library items from the Spotfire database? – Revvity Signals

Date Posted: 2017-10-08
Product: TIBCO Spotfire®

Problem:

How to extract a list of library items from the Spotfire database?

Solution:

How to extract a list of folder and sub-folder permissions from Spotfire so that it can be used for any auditing/tracking purpose?

The following procedure is a workaround to get this information from the Spotfire Database. This deals with running queries directly against the Spotfire Database, which is not recommended. If you intend to go this route, make sure to use a read-only user to avoid any accidental modifications to the database. Make sure to make a backup of the Spotfire Database before running any queries directly against the Spotfire database.

1). To list all of the analysis file names along with their item IDs, you can use the following SQL statement:

SELECT title, item_id, parent_id

FROM LIB_ITEMS

WHERE item_type IN (

SELECT type_id

FROM LIB_ITEM_TYPES

WHERE file_suffix = 'dxp');

A sample response entry would be:

Title: cars

Item_ID: 3444ceb5-0e3f-442b-8dcf-556be39852c5

Parent_ID: a13b65e3-a359-41b1-8188-13c33120f261

Parent_ID is the folder identifier, which is important because this is where permissions are given to specific groups.

2). To list the groups which have permissions set on the folder above, use the following query:

SELECT group_id, permission FROM lib_access WHERE item_id = 'a13b65e3-a359-41b1-8188-13c33120f261';

This will give the group_id along with the permission setting. A sample output from the above command would be:

GROUP_ID P

------------------------------------ ----

19e7e430-9997-11da-fbc4-0010ac110215 O

19e7e430-9997-11da-fbc4-0010ac110215 R

19e7e430-9997-11da-fbc4-0010ac110215 W

19e7e430-9997-11da-fbc4-0010ac110215 X

58fba8e4-b7ad-4560-b4c6-08708ae93391 R

58fba8e4-b7ad-4560-b4c6-08708ae93391 W

58fba8e4-b7ad-4560-b4c6-08708ae93391 X

fed6b2b0-a9e1-11da-8ed2-0010ac110222 O

fed6b2b0-a9e1-11da-8ed2-0010ac110222 R

fed6b2b0-a9e1-11da-8ed2-0010ac110222 W

fed6b2b0-a9e1-11da-8ed2-0010ac110222 X

In the results displayed above, we see three unique group_id values, with the folder permissions for each of them. To extract the actual group name for each group_id value in the above list, use this query (replacing <group_id> with the actual group_id value of interest):

SELECT group_name FROM groups WHERE group_id = '<group_id>';

3). To determine which users are members of the groups listed above you can use the group_members table:

SELECT member_user_id FROM group_members WHERE group_id = '<group_id>';

The username is found as user_name in the users table, using this query:

SELECT user_name FROM users WHERE user_id = '<member_user_id>';

Using the above dependency information between the tables should allow you to build a view of reports per user.

Related articles