Example of Link 3 Reports

DECLARE
	@lStatusTypesIdFailed INT,
	@lStatusTypesIdIgnored INT;

SELECT @lStatusTypesIdFailed = st.StatusTypesId FROM [[:ConfigurationDB:]].Tracking.tStatusTypes st WHERE st.StatusTypeName = 'Failed';
SELECT @lStatusTypesIdIgnored = st.StatusTypesId FROM [[:ConfigurationDB:]].Tracking.tStatusTypes st WHERE st.StatusTypeName = 'Ignored';

;WITH FailedDocuments AS (
	SELECT 
		d.DocumentsId,
		d.CreatedDate,
		st.StatusTypeName AS [DocumentStatus]
	FROM 
		[[:TransactionalDB:]].[Tracking].[tDocuments] d
		JOIN [[:ConfigurationDB:]].[Tracking].tStatusTypes st ON d.StatusTypesId_LatestDocumentStatus = st.StatusTypesId
	WHERE 
		(st.StatusTypesId = @lStatusTypesIdFailed OR st.StatusTypesId = @lStatusTypesIdIgnored)
		AND  d.CreatedDate > GETUTCDATE()-@iDaysBack
)
SELECT 
	d.DocumentsId,
	d.CreatedDate,
	d.DocumentStatus,
	et.Area,
	ec.ExceptionCategoryName,
	et.ErrorCode,
	et.ExceptionCause,
	ei.ExceptionMessage,
	et.ExceptionSolution
FROM 
	FailedDocuments d
	LEFT JOIN [[:TransactionalDB:]].ErrorHandling.tExceptionInstances ei ON ei.DocumentsId = d.DocumentsId
	LEFT JOIN [[:ConfigurationDB:]].ErrorHandling.tExceptionTypes et ON ei.ExceptionTypesId = et.ExceptionTypesId
	LEFT JOIN [[:ConfigurationDB:]].[ErrorHandling].[tExceptionCategories] ec on ec.ExceptionCategoriesId = et.ExceptionCategoriesId
ORDER BY
	d.CreatedDate;

/*
DECLARE
	@iStartDate DATETIME2(0);

SET @iStartDate = DATEADD(MONTH, -12, GETUTCDATE());
*/

;WITH HighTransactionDistribution AS (
	SELECT
		ck.ConfigurationKeysId,
		ck.KeyName,
		cku.KeyUsageName,
		dc.DistributionsId,
		dc.Value AS HighTransactionDistribution
	FROM
		[[:ConfigurationDB:]].Presentation.tConfigurationKeys ck
		JOIN [[:ConfigurationDB:]].Presentation.tConfigurationKeyUsages cku ON cku.ConfigurationKeyUsagesId = ck.ConfigurationKeyUsagesId
		JOIN [[:ConfigurationDB:]].DistributionModel.tDistributionConfigurations dc ON dc.ConfigurationKeysId = ck.ConfigurationKeysId
	WHERE
		cku.KeyUsageName = 'Distribution_Advanced'
		AND ck.KeyName = 'BBLINK_HighTransactionDistribution'
		AND dc.DistributionsId IS NOT NULL
)
SELECT
	CAST(DATEADD(MONTH, DATEDIFF(MONTH, '2020-01-01', d.CreatedDate), '2020-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS DATE) AS [Month],
	FORMAT(COUNT(*), 'N0', 'da-dk') AS [Document Count], /* Number of created for the MONTH */
	FORMAT(SUM (
		CASE
			WHEN (hi.DistributionsId IS NULL OR hi.HighTransactionDistribution = 'False') THEN 1
			ELSE 0
		END
	), 'N0', 'da-dk') AS [Standard Document Count],
	FORMAT(SUM (
		CASE
			WHEN hi.DistributionsId IS NOT NULL AND hi.HighTransactionDistribution = 'True' THEN 1
			ELSE 0
		END
	), 'N0', 'da-dk') AS [High-Transaction Document Count]
FROM
	[[:TransactionalDB:]].Tracking.tDocuments d
	LEFT JOIN HighTransactionDistribution hi ON hi.DistributionsId = d.DistributionsId
WHERE
	d.CreatedDate > @iStartDate
GROUP BY
	DATEDIFF(MONTH, '2020-01-01', d.CreatedDate)
ORDER BY
	DATEDIFF(MONTH, '2020-01-01', d.CreatedDate);

-- Count changes per user, and include last successfully login date.
;WITH LastLogins AS (
    SELECT
        --CreatedDate,
        CAST(CreatedDate AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS DATETIME) AS CreatedDate,
        JSON_VALUE(JsonLog, '$.Username') AS Username,
        JSON_VALUE(JsonLog, '$.Message') AS Message,
        ROW_NUMBER() OVER (PARTITION BY JSON_VALUE(JsonLog, '$.Username') ORDER BY CreatedDate DESC) AS RowNumber
        ,ale.UsersId
    FROM
		[[:TransactionalDB:]].Logging.tAuditLogEntries ale
	WHERE
		[Action] = 'user login'
)
SELECT
    u.DisplayName,
    u.EmailAddress,
    cl.LogAction AS [Action],
    COUNT(*) AS [ActionCount],
    MIN(ll.CreatedDate) AS LastLogin,
    MIN(ll.Message) AS LoginMessage
FROM
    [[:ConfigurationDB:]].Logging.tChangeLogs cl
    JOIN [[:ConfigurationDB:]].Users.tUsers u ON u.UsersId = cl.CreatedBy
    LEFT JOIN LastLogins ll ON ll.UsersId = u.UsersId
WHERE
    1 = 1
    --AND cl.CreatedBy <> 10000
    AND ll.RowNumber = 1
GROUP BY
    u.DisplayName,
    u.EmailAddress,
    cl.LogAction
ORDER BY
    u.DisplayName,
    cl.LogAction;