How to analyze the file size of library items in Spotfire 11.4 LTS and below

Problem:

Spotfire admins might be interested in analyzing the disk size of library items created by users per organizations in an effort to control the total disk space consumed by library. Is there an easy way to pull in this data and perhaps analyze it using Spotfire?

Resolution:

The following SQL can be ran against the Spotfire server database to retrieve the name, full library path, file size, and the version name of all DXP items in a library.

• The SQL was created for Oracle database and for Spotfire 11.4 LTS and below.
• You need to change the SPOTFIRE_DB_SCHEMA to your organization's Oracle schema.
• SQL can be execute from Spotfire Connector and then analyzed using Spotfire in the way you like.

WITH
List1(ITEM_ID, TITLE, LIBRARY_PATH, PARENT_ID) as(
SELECT
A.ITEM_ID,
A.TITLE,
CAST(A.TITLE AS nvarchar2(2000)) AS LIBRARY_PATH, 
A.Parent_ID
FROM 
SPOTFIRE_DB_SCHEMA.LIB_ITEMS A
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE
lit.DISPLAY_NAME='folder' and A.hidden=0
),
List2(ITEM_ID, TITLE, LIBRARY_PATH,PARENT_ID) as(
SELECT
ITEM_ID,
TITLE,
CAST(TITLE AS nvarchar2(1000)) AS LIBRARY_PATH, 
Parent_ID
FROM List1
WHERE TITLE = 'root'
UNION ALL
SELECT
A.ITEM_ID,
A.TITLE,
REPLACE(CAST((B.LIBRARY_PATH || '/' || A.TITLE) AS nvarchar2(1000)),'root', '') AS LIBRARY_PATH, 
A.Parent_ID
FROM List1 A inner join List2 B ON A.PARENT_ID = B.ITEM_ID
),
List3(item_id, title, parent_id, content_size,display_name) as(
SELECT A.item_id, A.title, A.parent_id, (A.content_size / POWER (2, 20))as content_size ,lit.DISPLAY_NAME
FROM SPOTFIRE_DB_SCHEMA.LIB_ITEMS A
LEFT JOIN SPOTFIRE_DB_SCHEMA.LIB_ITEM_TYPES lit on lit.TYPE_ID=A.ITEM_TYPE
WHERE lit.DISPLAY_NAME in ('analysis', 'poster', 'guide', 'custom', 'dxp', 'column', 'filter', 'join', 'procedure', 'query', 'datasource', 'analysisstate', 'task', 'bookmark', 'embeddedresource', 'analyticitem', 'datafunction', 'dxpscript', 'colorscheme', 'connectiondatasource', 'dataconnection', 'sbdf', 'analyticmodel', 'asjob', 'mod')
)select * from List3