Spotfire Server: Example SQL Queries to help manage Spotfire Server Library content size

Summary:

This article contains example SQL queries which provide insight into the Spotfire Server library content.

Details:

Over time the content in the Spotfire Server library can grow as data is saved in the library (analysis/dxp files with data stored/embedded, sbdf files, etc). The following are some sample SQL queries that can be run against the Spotfire Server database to help a Spotfire administrator get visibility into the library contents, specifically, highlighting the largest objects and largest folders in the library, users who are associated with the most content, and large content which has not been accessed in recent history or ever.

Also, see Searching the Library section for ways to find similar information using the Spotfire library search functionality (for example: content_size:>100MB).

Note: The management of the Spotfire Server database is a responsibility of your local database administrator, but the Spotfire admin user can manage the content saved in the library.

Resolution:

Here are example queries that can be run against a Spotfire Server database which is running on a Microsoft SQL Server. For other database types the queries may have to be modified. All queries have been verified in Spotfire Server 14.0 LTS environment.

------------------------------------
--Largest library items
------------------------------------
select 
    li.CONTENT_SIZE/(1024*1024) as [CONTENT_SIZE (MB)],
    li.ITEM_ID,
    li.TITLE,
    li.DESCRIPTION,
    lit.DISPLAY_NAME,
    created.DISPLAY_NAME as [CREATED BY],
    li.CREATED as [CREATED DATE],
    modified.DISPLAY_NAME as [MODIFIED BY],
    li.MODIFIED as [MODIFIED DATE],
    li.ACCESSED as [ACCESSED DATE],
    li.PARENT_ID,
    parent.TITLE as [PARENT TITLE]
from lib_items li
    join LIB_ITEMS parent on li.PARENT_ID=parent.ITEM_ID
    join LIB_ITEM_TYPES lit on li.ITEM_Type=lit.TYPE_ID
    join USERS created on li.CREATED_BY=created.USER_ID
    join USERS modified on li.MODIFIED_BY=modified.USER_ID
order by li.CONTENT_SIZE desc

------------------------------------
--Largest library folders
------------------------------------
select 
    Sum(li.CONTENT_SIZE)/(1024*1024) as [CONTENT_SIZE (MB)],
    li.PARENT_ID as [FOLDER ITEM_ID],
    parent.TITLE as [FOLDER TITLE],
    created.DISPLAY_NAME as [CREATED BY],
    parent.CREATED as [CREATED DATE],
    modified.DISPLAY_NAME as [MODIFIED BY],
    parent.MODIFIED as [MODIFIED DATE],
    parent.ACCESSED as [ACCESSED DATE]
from lib_items li
    join LIB_ITEMS parent on li.PARENT_ID=parent.ITEM_ID
    join USERS created on parent.CREATED_BY=created.USER_ID
    join USERS modified on parent.MODIFIED_BY=modified.USER_ID
group by li.PARENT_ID,parent.TITLE,created.DISPLAY_NAME,parent.CREATED,modified.DISPLAY_NAME,parent.MODIFIED,parent.ACCESSED
order by Sum(li.CONTENT_SIZE)/(1024*1024) desc

------------------------------------
--Users consuming the most space - by Creator
------------------------------------
select top 1 * from lib_items
select * from lib_item_types
select 
    Sum(li.CONTENT_SIZE)/(1024*1024) as [TOTAL CONTENT_SIZE (MB)],
    Sum(case when lit.FILE_SUFFIX='dxp' then 1 else 0 end) as [# DXP FILES],
    Sum(case when lit.FILE_SUFFIX='dxp' then li.CONTENT_SIZE else 0 end)/(1024*1024)  as [DXP FILE SIZE (MB)],
    Sum(case when lit.FILE_SUFFIX='sbdf' then 1 else 0 end) as [# SBDF FILES],
    Sum(case when lit.FILE_SUFFIX='sbdf' then li.CONTENT_SIZE else 0 end)/(1024*1024)  as [SBDF FILE SIZE (MB)],
    created.DISPLAY_NAME as [CREATED BY]
from lib_items li
    join LIB_ITEM_TYPES lit on li.ITEM_TYPE=lit.TYPE_ID
    join USERS created on li.CREATED_BY=created.USER_ID
group by created.DISPLAY_NAME
order by Sum(li.CONTENT_SIZE)/(1024*1024) desc

------------------------------------
--Users consuming the most space - by Last Modifier
------------------------------------
select 
    Sum(li.CONTENT_SIZE)/(1024*1024) as [TOTAL CONTENT_SIZE (MB)],
    Sum(case when lit.FILE_SUFFIX='dxp' then 1 else 0 end) as [# DXP FILES],
    Sum(case when lit.FILE_SUFFIX='dxp' then li.CONTENT_SIZE else 0 end)/(1024*1024)  as [DXP FILE SIZE (MB)],
    Sum(case when lit.FILE_SUFFIX='sbdf' then 1 else 0 end) as [# SBDF FILES],
    Sum(case when lit.FILE_SUFFIX='sbdf' then li.CONTENT_SIZE else 0 end)/(1024*1024)  as [SBDF FILE SIZE (MB)],
    modified.DISPLAY_NAME as [LAST MODIFIED BY]
from lib_items li
    join LIB_ITEM_TYPES lit on li.ITEM_TYPE=lit.TYPE_ID
    join USERS modified on li.MODIFIED_BY=modified.USER_ID
group by modified.DISPLAY_NAME
order by Sum(li.CONTENT_SIZE)/(1024*1024) desc
------------------------------------

------------------------------------
--Largest content (>10 MB) not accessed recently (never accessed or accessed greater than 1 year ago)
------------------------------------
select 
    li.CONTENT_SIZE/(1024*1024) as [CONTENT_SIZE (MB)],
    li.ITEM_ID,
    li.TITLE,
    li.DESCRIPTION,
    lit.DISPLAY_NAME as [ITEM TYPE],
    created.DISPLAY_NAME as [CREATED BY],
    li.CREATED as [CREATED DATE],
    modified.DISPLAY_NAME as [MODIFIED BY],
    li.MODIFIED as [MODIFIED DATE],
    li.ACCESSED as [ACCESSED DATE],
    li.PARENT_ID,
    parent.TITLE as [PARENT TITLE],li.CONTENT_SIZE
from lib_items li
    join LIB_ITEMS parent on li.PARENT_ID=parent.ITEM_ID
    join LIB_ITEM_TYPES lit on li.ITEM_Type=lit.TYPE_ID
    join USERS created on li.CREATED_BY=created.USER_ID
    join USERS modified on li.MODIFIED_BY=modified.USER_ID
where (li.ACCESSED is NULL or li.ACCESSED < DATEADD(year,-1,GETDATE())) and li.CONTENT_SIZE/(1024*1024) > 10
order by li.CONTENT_SIZE desc



Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by Spotfire to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration. It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.