Spotfire Server: How to list all bookmarks that belong to a specific analysis

List all bookmarks in the Spotfire Server database for a specific analysis

There are times where you may need to look at what bookmarks are available for a report, but cannot do so directly from the installed Analyst client, since this will only show the public bookmarks available. It may be for informational purposes, for the report developer or the system administrator, or it might be useful when you suspect that any user created bookmarks might be lost during migration or deployment of new versions of analyses between environments.

As this information is not available through the installed Analyst client, you will need to query the Spotfire  Server database for the information. 

Solution:

As first step, you'll need to find out what unique identifier (GUID) your analysis has in the Spotfire database:

1. Open the Spotfire Analyst installed client, and log in.
2. Browse to the analysis of interest in your Spotfire library.
3. Right click, and choose: Copy Link to Analysis -> Either of the three options -> Library Identifier.
4. If you choose "Links that Let the Recipients Choose Client" the link will look like this: http://serverAddress/spotfire/redirect?analysis=58470b87-d881-4658-b7c3-18d6a5efdb15.
5. The GUID is the last sequence of characters after "analysis=", in this case: 58470b87-d881-4658-b7c3-18d6a5efdb15 .

Next, make use of the above GUID to extract the available bookmarks from the Spotfire database:

• Start any database client that lets you login to the database (Dbeaver, DbVisualizer, etc.), then create and execute queries below.
• This query will find bookmark parent item_id for analysis with parent_id of the analysis in question:

SELECT
   li.item_id,
   li.title
FROM
   lib_items li
   INNER JOIN
   lib_item_types lit
    ON li.item_type=lit.type_id
WHERE    li.parent_id='58470b87-d881-4658-b7c3-18d6a5efdb15' -- Librar GUID for the analysis file you want to check for.

• This will result in a table looking as follows:

• Copy the GUID of the "Bookmarks" item, and use it in this query:

SELECT  *
FROM
   lib_items li
   INNER JOIN
   lib_item_types lit
   ON li.item_type=lit.type_id
WHERE    li.parent_id='4c62a993-f118-4286-b3f6-1817be2f5584' -- Item_id taken from the query above
ORDER BY li.created

• You will now get a table back that contains all the bookmarks that are in the library, which are associated with the analysis in question.