Ektron Metrics Script

This is a compilation of some of the scripts on this site to get numbers to report to management

SET NOCOUNT ON

DECLARE @month INT
	,@year INT
	,@start_date DATETIME
	,@end_date DATETIME

BEGIN /* Set start and end dates to last month range */
	SET @month = (
			SELECT CASE
					WHEN MONTH(GETDATE()) = '1'
						THEN '12'
					ELSE MONTH(GETDATE()) - 1
					END
			)
	--SELECT @month AS Month_To_Report
	SET @year = (
			SELECT CASE
					WHEN MONTH(GETDATE()) = '1'
						THEN YEAR(GETDATE()) - 1
					ELSE YEAR(GETDATE())
					END
			)
	--SELECT @YEAR AS Year_To_Report
	SET @start_date = CAST(CAST(@month AS VARCHAR) + '/01/' + CAST(@year AS VARCHAR) AS DATETIME)
	-- @start_date AS Start_Date
	SET @end_date = (
			SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @start_date) + 1, 0))
			)
	--SELECT @end_date AS End_Date
	SET @start_date = '03/01/2014'
	SET @end_date = '03/31/2014'
END /* Set start and end dates to last month range */

BEGIN /* Show Date Range */
	SELECT (CONVERT(VARCHAR(8), @start_date, 1) + ' - ' + CONVERT(VARCHAR(8), @end_date, 1)) AS 'Date Range'
END /* Show Date Range */

BEGIN /* Account Registrations */
	SELECT COUNT(*) AS 'Account Registrations'
	FROM users u
	WHERE (
			u.[date_created] >= @start_date
			OR u.[date_created] IS NULL
			)
		AND (
			u.[date_created] <= @end_date
			OR u.[date_created] IS NULL
			)
		AND u.[user_deleted] = 0
END /* Account Registrations */

BEGIN /* Total User Count */
	SELECT COUNT(*) AS TotalUserCount
	FROM user_to_group_tbl utgt1
	INNER JOIN users u ON u.user_id = utgt1.user_id
	WHERE (
			u.date_created <= @end_date
			OR u.date_created IS NULL
			)
END /* Total User Count */

BEGIN /* Account Status - Active */
	SELECT COUNT(*) AS "Account Status - Active"
	FROM user_to_group_tbl utgt1
	INNER JOIN users u ON u.user_id = utgt1.user_id
	WHERE (
			u.date_created <= @end_date
			OR u.date_created IS NULL
			)
		AND u.[user_deleted] = 0
END /* Account Status - Active */

BEGIN /* Account Login Status <= 120 Days */
	SELECT COUNT(*) AS "Login Status - <= 120"
	FROM user_to_group_tbl utgt1
	INNER JOIN users u ON u.user_id = utgt1.user_id
	WHERE (
			u.date_created <= @end_date
			OR u.date_created IS NULL
			)
		AND u.[user_deleted] = 0
		AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 120
END /* Account Login Status <= 120 Days */

BEGIN /* Account Login Status <= 90 Days*/
	SELECT COUNT(*) AS "Login Status - <= 90"
	FROM user_to_group_tbl utgt1
	INNER JOIN users u ON u.user_id = utgt1.user_id
	WHERE (
			u.date_created <= @end_date
			OR u.date_created IS NULL
			)
		AND u.[user_deleted] = 0
		AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 90
END /* Account Login Status <= 90 Days*/

BEGIN /* Account Login Status <= 30 Days */
	SELECT COUNT(*) AS "Login Status - <= 30"
	FROM user_to_group_tbl utgt1
	INNER JOIN users u ON u.user_id = utgt1.user_id
	WHERE (
			u.date_created <= @end_date
			OR u.date_created IS NULL
			)
		AND u.[user_deleted] = 0
		AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 30
END /* Account Login Status <= 30 Days */

BEGIN /* User Group - User Details */
	SELECT DISTINCT ug.usergroup_name AS "Ektron User Group"
		,(
			SELECT COUNT(*)
			FROM user_to_group_tbl utgt1
			INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
			INNER JOIN users u ON u.user_id = utgt1.user_id
			WHERE utgt.usergroup_id = utgt1.usergroup_id
				AND (
					u.date_created <= @end_date
					OR u.date_created IS NULL
					)
			) AS "Total Users"
		,(
			SELECT COUNT(*)
			FROM user_to_group_tbl utgt1
			INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
			INNER JOIN users u ON u.user_id = utgt1.user_id
			WHERE utgt.usergroup_id = utgt1.usergroup_id
				AND (
					u.date_created <= @end_date
					OR u.date_created IS NULL
					)
				AND u.[user_deleted] = 0
			) AS "Account Status - Active"
		,(
			SELECT COUNT(*)
			FROM user_to_group_tbl utgt1
			INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
			INNER JOIN users u ON u.user_id = utgt1.user_id
			WHERE utgt.usergroup_id = utgt1.usergroup_id
				AND (
					u.date_created >= @start_date
					OR u.date_created IS NULL
					)
				AND (
					u.date_created <= @end_date
					OR u.date_created IS NULL
					)
				AND u.[user_deleted] = 0
			) AS "Registered Witin The Month"
		,(
			SELECT COUNT(*)
			FROM user_to_group_tbl utgt1
			INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
			INNER JOIN users u ON u.user_id = utgt1.user_id
			WHERE utgt.usergroup_id = utgt1.usergroup_id
				AND (
					u.date_created <= @end_date
					OR u.date_created IS NULL
					)
				AND u.[user_deleted] = 0
				AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 120
			) AS "Login Status - <= 120"
		,(
			SELECT COUNT(*)
			FROM user_to_group_tbl utgt1
			INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
			INNER JOIN users u ON u.user_id = utgt1.user_id
			WHERE utgt.usergroup_id = utgt1.usergroup_id
				AND (
					u.date_created <= @end_date
					OR u.date_created IS NULL
					)
				AND u.[user_deleted] = 0
				AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 90
			) AS "Login Status - <= 90"
		,(
			SELECT COUNT(*)
			FROM user_to_group_tbl utgt1
			INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
			INNER JOIN users u ON u.user_id = utgt1.user_id
			WHERE utgt.usergroup_id = utgt1.usergroup_id
				AND (
					u.date_created <= @end_date
					OR u.date_created IS NULL
					)
				AND u.[user_deleted] = 0
				AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 30
			) AS "Login Status - <= 30"
	FROM user_to_group_tbl utgt
	INNER JOIN usergroups ug ON utgt.usergroup_id = ug.usergroup_id
	ORDER BY "Ektron User Group"
END /* User Group - User Details */

BEGIN /* # Of Templates */
	SELECT COUNT(*) AS "# Of Templates"
	FROM templates_tbl
END /* # Of Templates */

BEGIN /* Template Usage */
	SELECT DISTINCT template_id AS "Template ID"
		,count(*) AS "Usage"
	FROM content
	GROUP BY template_id
	ORDER BY "Template ID"
END /* Template Usage */

BEGIN /* Total Content Items */
	SELECT count(*) AS "Total Content Items"
	FROM content
	WHERE [date_created] <= @end_date
END /* Total Content Items */

BEGIN /* Total Content Items By Type */
	SELECT 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 104
				THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
			WHEN 1111
				THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
			WHEN 3333
				THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
			END AS 'Content Type'
		,COUNT(*) AS 'Total Content Written'
	FROM content c
	WHERE [date_created] <= @end_date
	GROUP BY c.content_type
	ORDER BY 'Content Type'
END /* Total Content Items By Type */

BEGIN /* Total Content Items By Type And Status */
	SELECT 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 104
				THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
			WHEN 1111
				THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
			WHEN 3333
				THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
			END AS 'Content Type'
		,CASE
			WHEN [content_status] = 'A'
				THEN 'Approved'
			WHEN [content_status] = 'O'
				THEN 'Checked Out'
			WHEN [content_status] = 'I'
				THEN 'Checked In'
			WHEN [content_status] = 'S'
				THEN 'Submitted for Approval'
			WHEN [content_status] = 'M'
				THEN 'Marked for Deletion'
			WHEN [content_status] = 'P'
				THEN 'Pending Go Live Date'
			WHEN [content_status] = 'T'
				THEN 'Awaiting Completion of Associated Tasks'
			WHEN [content_status] = 'D'
				THEN 'Pending Deletion'
			END AS ContentStatus
		,COUNT(*) AS 'Total Content Written'
	FROM content c
	GROUP BY c.content_type
		,c.content_status
	ORDER BY 'Content Type'
		,ContentStatus
END /* Total Content Items By Type And Status */

BEGIN /* Total Content Items By Type And Author */
	SELECT c.user_id AS 'Content Author ID'
		,(first_name + ' ' + last_name) AS 'Content Author'
		--,c.content_type AS 'Content Type'
		,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 104
				THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
			WHEN 1111
				THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
			WHEN 3333
				THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
			END AS 'Content Type'
		,COUNT(*) AS 'Total Content Written'
	FROM content c
	INNER JOIN users u ON u.user_id = c.user_id
	WHERE c.[date_created] <= @end_date
	GROUP BY c.content_type
		,c.user_id
		,u.first_name
		,u.last_name
	ORDER BY 'Content Author'
		,'Content Author ID'
END /* Total Content Items By Type And Author */

BEGIN /* Total Content Items By Status */
	SELECT CASE
			WHEN [content_status] = 'A'
				THEN 'Approved'
			WHEN [content_status] = 'O'
				THEN 'Checked Out'
			WHEN [content_status] = 'I'
				THEN 'Checked In'
			WHEN [content_status] = 'S'
				THEN 'Submitted for Approval'
			WHEN [content_status] = 'M'
				THEN 'Marked for Deletion'
			WHEN [content_status] = 'P'
				THEN 'Pending Go Live Date'
			WHEN [content_status] = 'T'
				THEN 'Awaiting Completion of Associated Tasks'
			WHEN [content_status] = 'D'
				THEN 'Pending Deletion'
			END AS ContentStatus
		,COUNT([content_status]) AS Total
	FROM [content] c
	WHERE c.[date_created] <= @end_date
	GROUP BY [content_status]
	ORDER BY ContentStatus
END /* Total Content Items By Status */

BEGIN /* Total File Items By File Type and Status */
	SELECT (
			CASE
				WHEN handle LIKE '%.%'
					THEN reverse(left(reverse(handle), charindex('.', reverse(handle)) - 1))
				ELSE ''
				END
			) AS FileType
		,CASE
			WHEN [content_status] = 'A'
				THEN 'Approved'
			WHEN [content_status] = 'O'
				THEN 'Checked Out'
			WHEN [content_status] = 'I'
				THEN 'Checked In'
			WHEN [content_status] = 'S'
				THEN 'Submitted for Approval'
			WHEN [content_status] = 'M'
				THEN 'Marked for Deletion'
			WHEN [content_status] = 'P'
				THEN 'Pending Go Live Date'
			WHEN [content_status] = 'T'
				THEN 'Awaiting Completion of Associated Tasks'
			WHEN [content_status] = 'D'
				THEN 'Pending Deletion'
			END AS ContentStatus
		,COUNT(*) AS 'Total Assets'
	FROM AssetDataTable
	INNER JOIN content c ON c.asset_id = AssetDataTable.id
	GROUP BY (
			CASE
				WHEN handle LIKE '%.%'
					THEN reverse(left(reverse(handle), charindex('.', reverse(handle)) - 1))
				ELSE ''
				END
			)
		,[content_status]
	ORDER BY FileType
		,ContentStatus
END /* Total File Items By File Type and Status */