List all bookmarks in the Spotfire database for a specific analysis

List all bookmarks in the Spotfire 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 client as 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 client, you will need to query the Spotfire database for the information. As the structure there might be a bit unfamiliar, this article shows the steps needed without having to learn the structure of things in the database. The first thing you'll want to do is find out what unique identifier (GUID) your analysis has in the Spotfire database.

The following steps will allow you to get the GUID:

1. Open the TIBCO Spotfire installed client, and log in.
2. Browse to the analysis of interest in your 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 .

Now it is time to make use of the above GUID to extract the available bookmarks from the Spotfire database.

To query the Spotfire database for the bookmarks of an analysis, follow the below steps:

• Start any client that lets you log in towards the database, and create and execute queries towards it.
• Enter the below query, which allows you to find the bookmark parent item for your analysis, but make sure to enter your own GUID:

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 like the following:

item_id	title
4921e23a-30ab-49a2-a80e-9c0f3505e898	AnalyticItems
4c62a993-f118-4286-b3f6-1817be2f5584	Bookmarks
cac1705d-835a-4009-badf-1f562106220e	EmbeddedResources

• You want to copy the GUID of the "Bookmarks" item, and use it in the query below:

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 the all the bookmarks that are in the library, that are tied to the analysis in question.