Ektron List Widgets On Content

This is another Ektron audit script that will help list PageBuilder content items that have a widget placed on them.

The script below is very similar to Ektron Alias With Folder Name

SET NOCOUNT ON

DECLARE @Domain VARCHAR(50)

SET @Domain = '/'

SELECT c.[content_id] AS 'Content ID'
	,c.[content_title] AS 'Content Title'
	--,c.[content_html] AS 'Content HTML'
	--,c.template_id AS 'Template ID'
	,tt.template_filename AS 'Template Filename'
	,List.title AS 'Widget'
	,
	--c.[content_status] AS 'Content Status Code'
	CASE
		WHEN c.[content_status] = 'A'
			THEN 'Approved'
		WHEN c.[content_status] = 'O'
			THEN 'Checked Out'
		WHEN c.[content_status] = 'I'
			THEN 'Checked In'
		WHEN c.[content_status] = 'S'
			THEN 'Submitted for Approval'
		WHEN c.[content_status] = 'M'
			THEN 'Marked for Deletion'
		WHEN c.[content_status] = 'P'
			THEN 'Pending Go Live Date'
		WHEN c.[content_status] = 'T'
			THEN 'Awaiting Completion of Associated Tasks'
		WHEN c.[content_status] = 'D'
			THEN 'Pending Deletion'
		END AS 'Content Status'
	,c.[date_created] AS 'Content Creation Date'
	,
	--c.[content_type]  AS 'Content Type Code',
	CASE content_type
		WHEN - 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - AllTypes'
		WHEN 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - Content'
		WHEN 2
			THEN CAST(content_type AS VARCHAR(5)) + ' - Forms'
		WHEN 3
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Content'
		WHEN 4
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Forms'
		WHEN 7
			THEN CAST(content_type AS VARCHAR(5)) + ' - Library Item'
		WHEN 8
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 9
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Assets'
		WHEN 12
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Media'
		WHEN 99
			THEN CAST(content_type AS VARCHAR(5)) + ' - Non Library Content'
		WHEN 101
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 103
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 106
			THEN CAST(content_type AS VARCHAR(5)) + ' - PNG Assets'
		WHEN 104
			THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
		WHEN 1102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archived PDF Assets'
		WHEN 1111
			THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
		WHEN 3333
			THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
		ELSE CAST(content_type AS VARCHAR(5))
		END AS 'Content Type'
	,
	--c.[approval_method] AS 'Approval Method',
	--c.[searchable] AS 'Searchable Code',
	CASE c.[searchable]
		WHEN 1
			THEN 'Yes'
		WHEN 0
			THEN 'No'
		END AS 'Searchable'
	,CASE
		WHEN uam.[urlaliasnm] IS NULL
			THEN 'No Alias'
		ELSE (@Domain + uam.[urlaliasnm])
		END AS Alias
	,
	--(@Domain + uam.[urlaliasnm]) AS 'Alias URL',
	(@Domain + uam.[urltargetnm]) AS 'Actual URL'
	,
	--c.folder_id AS 'Folder ID',
	--cft.[folder_name] as 'Folder Name',
	cft.FolderPath AS 'Folder Path'
--cft.FolderIdPath AS 'Folder ID Path'
FROM [content] c
INNER JOIN [content_folder_tbl] cft ON c.folder_id = cft.folder_id
LEFT OUTER JOIN [UrlAliasMapping] uam ON uam.TargetID = c.content_id
LEFT OUTER JOIN templates_tbl tt ON c.template_id = tt.template_id
INNER JOIN (
	SELECT title
	FROM widget_types
	--WHERE title LIKE '%HP%'
	) List ON c.content_html LIKE '%' + List.title + '%'
--WHERE template_filename LIKE '%pb%'
ORDER BY cft.FolderPath
	,c.content_id
	,List.title