SQL query to display count of trusted scripts for all the analysis files that contains IronPython scripts

SQL query to display count of trusted scripts for all the analysis files that contains IronPython scripts

This article provides an example SQL query which helps find the count of trusted scripts for all dxp files in the database that contain IronPython scripts. Execute the following query against TIBCO Spotfire Server database and it will return the "Trusted Script Count" along with the analysis name with their respective GUIDs.

SELECT 
    par.TITLE as [Folder]
    ,li.Title as [DXP Title]
    ,li.ITEM_ID as [DXP GUID]
    ,COUNT(*) as [Trusted Script Count]
FROM LIB_PROPERTIES p
JOIN LIB_ITEMS li on li.ITEM_ID = p.ITEM_ID
JOIN LIB_ITEMS par on li.PARENT_ID = par.ITEM_ID
WHERE PROPERTY_NAME = 'TrustedScripts'
GROUP BY li.Title, li.ITEM_ID, PAR.TITLE
ORDER BY [Trusted Script Count] DESC

Here is a sample output that is returned:
 

Folder	DXP Title	DXP GUID	Trusted Script Count
Automation	MyDxp1	50f619f9-368d-470e-bff8-a0672a4f67b0	4
MyFolder1	MyDxp2	01ae51d8-553c-4fd7-9696-ced245cb60d9	3
MyFolder1	MyDxp3	67110684-93bd-4af7-b570-a9059f7fa81d	3

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 TIBCO 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.