This post collects practical SQL queries for TIBCO Spotfire Server administrators to analyze library content, usage, and users directly from the repository and Action Log databases.
Environment and schema assumptions:
- Spotfire Server repository tables: LIB_ITEMS, LIB_ITEM_TYPES, USERS, GROUPS, GROUP_MEMBERS, LIB_ACCESS
- Optional Action Log database with ACTIONLOG table (enabled via Spotfire Server configuration)
- Standard columns like CONTENT_SIZE, ACCESSED, LAST_LOGIN available in your version
- SQL Server syntax used (adjust functions for Oracle or PostgreSQL)
Always test queries in non-production environment first and adapt to your exact schema and Spotfire version.
1. Library Item Size (Largest Items)
This query lists all library items with their size in MB, sorted by size so you can quickly identify the largest analyses, SBDF files, and other objects.
SELECT
li.ITEM_ID,
li.TITLE,
lit.DISPLAY_NAME AS ITEM_TYPE,
li.CONTENT_SIZE / (1024*1024.0) AS SIZE_MB,
u1.DISPLAY_NAME AS CREATED_BY,
li.CREATED,
u2.DISPLAY_NAME AS MODIFIED_BY,
li.MODIFIED,
li.ACCESSED,
parent.TITLE AS PARENT_FOLDER
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
ON li.ITEM_TYPE = lit.TYPE_ID
LEFT JOIN LIB_ITEMS parent
ON li.PARENT_ID = parent.ITEM_ID
LEFT JOIN USERS u1
ON li.CREATED_BY = u1.USER_ID
LEFT JOIN USERS u2
ON li.MODIFIED_BY = u2.USER_ID
ORDER BY li.CONTENT_SIZE DESC;
Use this to drive cleanup of large, rarely used library items such as old analyses and obsolete SBDF files.
2. Library / Folder Size
This query aggregates CONTENT_SIZE at the folder level, giving you a view of which folders consume the most space.
SELECT
li.PARENT_ID AS FOLDER_ITEM_ID,
parent.TITLE AS FOLDER_TITLE,
SUM(li.CONTENT_SIZE) / (1024*1024.0) AS FOLDER_SIZE_MB,
parent.CREATED,
parent.MODIFIED,
parent.ACCESSED
FROM LIB_ITEMS li
JOIN LIB_ITEMS parent
ON li.PARENT_ID = parent.ITEM_ID
GROUP BY
li.PARENT_ID,
parent.TITLE,
parent.CREATED,
parent.MODIFIED,
parent.ACCESSED
ORDER BY FOLDER_SIZE_MB DESC;
Run this periodically to identify candidate folders for archive or refactoring.
3. Report Utilization (Recently Accessed Reports)
The next query lists analyses (DXP files) that have been accessed in the last 30 days, using the ACCESSED timestamp in LIB_ITEMS.
SELECT
li.ITEM_ID,
li.TITLE,
li.ACCESSED
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX = 'dxp'
AND li.ACCESSED >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY li.ACCESSED DESC;
You can adjust the DATEADD window to whatever "recently used" means in your environment.
4. Idle Reports (Candidates for Archive)
To find analyses that have not been accessed in the last 90 days (or at all), use this query.
SELECT
li.ITEM_ID,
li.TITLE,
li.ACCESSED,
li.MODIFIED
FROM LIB_ITEMS li
JOIN LIB_ITEM_TYPES lit
ON li.ITEM_TYPE = lit.TYPE_ID
WHERE lit.FILE_SUFFIX = 'dxp'
AND (
li.ACCESSED IS NULL
OR li.ACCESSED < DATEADD(DAY, -90, GETUTCDATE())
)
ORDER BY li.ACCESSED,
li.MODIFIED;
This is a good starting point for a "report retirement" process in collaboration with business owners.
5. Groups and Their Members
The next query gives a simple view of which users belong to which Spotfire groups, based on the GROUPS, GROUP_MEMBERS, and USERS tables.
SELECT
g.GROUP_NAME,
u.USER_NAME,
u.DISPLAY_NAME
FROM GROUPS g
JOIN GROUP_MEMBERS gm
ON g.GROUP_ID = gm.GROUP_ID
JOIN USERS u
ON gm.USER_ID = u.USER_ID
ORDER BY
g.GROUP_NAME,
u.USER_NAME;
Use this output to audit access models and compare against your identity provider structure.
6. Access Groups Attached to Folders and Items
To see which groups have permissions on which folders and items, query LIB_ACCESS and join to LIB_ITEMS and GROUPS.
SELECT
parent.TITLE AS FOLDER_TITLE,
li.TITLE AS ITEM_TITLE,
g.GROUP_NAME,
a.PERMISSION AS PERMISSION_MASK
FROM LIB_ACCESS a
JOIN LIB_ITEMS li
ON a.ITEM_ID = li.ITEM_ID
LEFT JOIN LIB_ITEMS parent
ON li.PARENT_ID = parent.ITEM_ID
JOIN GROUPS g
ON a.GROUP_ID = g.GROUP_ID
ORDER BY
parent.TITLE,
li.TITLE,
g.GROUP_NAME;
If your schema has a dedicated "folder" flag or type, add a filter to focus on folders only.
7. Usage Tracking with Action Logs
Once the Action Log feature is enabled, the ACTIONLOG table becomes your main source for high-fidelity usage tracking.
Daily usage summary by category and action:
SELECT
CAST(LOGGED_TIME AS date) AS LOG_DATE,
LOG_CATEGORY,
LOG_ACTION,
COUNT(*) AS EVENT_COUNT
FROM ACTIONLOG
WHERE LOG_CATEGORY IN ('auth', 'library')
GROUP BY
CAST(LOGGED_TIME AS date),
LOG_CATEGORY,
LOG_ACTION
ORDER BY
LOG_DATE,
LOG_CATEGORY,
LOG_ACTION;
This gives you a quick overview of logins, report opens, and other key activities across time.
8. Report Open Events (Action Log Detail)
To analyze which reports are opened most frequently, filter the ACTIONLOG entries on library-related actions.
SELECT
a.OBJECT_NAME AS REPORT_NAME,
COUNT(*) AS OPEN_COUNT
FROM ACTIONLOG a
WHERE a.LOG_CATEGORY = 'library'
AND a.LOG_ACTION = 'open'
GROUP BY
a.OBJECT_NAME
ORDER BY
OPEN_COUNT DESC;
Column names and LOG_ACTION values can differ by version, so check your own table definition first.
9. Unique Users Over a Period
The following query returns the number of distinct users who logged in during the last 90 days, based on Action Log authentication events.
SELECT
COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS_90D
FROM ACTIONLOG
WHERE LOG_CATEGORY = 'auth'
AND LOG_ACTION = 'login'
AND LOGGED_TIME >= DATEADD(DAY, -90, GETUTCDATE());
You can change the 90-day window or group by date to build adoption dashboards in Spotfire itself.
10. Idle Users (No Recent Logins)
If your USERS table maintains a LAST_LOGIN column, you can identify enabled users who have not logged in recently.
SELECT
USER_NAME,
DISPLAY_NAME,
LAST_LOGIN
FROM USERS
WHERE ENABLED = 1
AND (
LAST_LOGIN IS NULL
OR LAST_LOGIN < DATEADD(DAY, -90, GETUTCDATE())
)
ORDER BY LAST_LOGIN;
Where LAST_LOGIN is not reliable, compute last login from the Action Log by taking the max LOGGED_TIME per user for auth/login events.
Building an Admin Dashboard in Spotfire
These queries are ideal building blocks for a dedicated "Spotfire Admin" analysis. Typical approach:
- Create one or more information links (or data connections with custom SQL) using the queries above.
- Build pages for: library inventory, size & cleanup, report utilization, user & group audit, and usage/adoption stats.
- Run the queries read-only and schedule refreshes as needed so that admins always see current metadata without impacting production usage.
Conclusion
Regular monitoring of Spotfire library content, usage patterns, and user access is critical for maintaining a healthy and efficient analytics platform. The SQL queries provided in this post allow administrators to:
- Identify large, underutilized content candidates for retirement
- Track user adoption and engagement
- Audit permissions and group memberships
- Build data-driven governance dashboards
For Spotfire Server administrators responsible for library and user management, these queries form the foundation of an effective metadata and governance toolkit.
Always remember to test in non-production environments and adapt column names and logic to match your specific Spotfire version and database schema.
Happy querying!