Published: 4 October 2022 .NET 6 CMS12 Modules Optimizely CMS SQL

Dette innlegget er også tilgjengelig på norsk

Bring the Report Center back in Optimizely CMS 12

In Optimizely (formerly Episerver) versions up to CMS 11, we had access to a Report Center with the following reports...

SqlStudio with saved queries, as a replacement for the old report center

These reports were built using the deprecated technology Web Forms and were removed when Optimizely CMS 12 was upgraded to the new and modern .NET 6 platform.

I found several of those old reports very useful but using my addon SqlStudio for Optimizely CMS, I can run SQL queries against the database to get the same results.

For more information on how to save the queries, check out the module's documentation. If you save the queries listed further down in this blog post, your new Report Center could look like this.

SqlStudio with saved queries, as a replacement for the old report center

SQL queries

In the rest of this post, I will list the different SQL queries, that can be used to replace the various reports in the old Report Center. For each query, there are two variants, one for running the report, and one for saving the query for running the report later.

Not Published Pages
Published Pages
Changed Pages
Expired Pages
Simple Addresses
Link Status

Not Published Pages

SQL for displaying the report

SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.StartPublish, 'dd.MM.yyyy') AS "Publication Date",
    FORMAT(cl.Changed, 'dd.MM.yyyy') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language"
FROM 
    tblContent c,
    tblWorkContent wc,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.pkID = wc.fkContentID AND
    wc.fkLanguageBranchID = cl.fkLanguageBranchID AND
    cl.fkLanguageBranchID = lb.pkID AND	
    wc.StartPublish IS NULL

SQL for saving the query

INSERT INTO SqlQueries (Name, Category, Query)
VALUES ('[1] Not Published Pages', 'Report Center', 'SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.StartPublish, ''dd.MM.yyyy'') AS "Publication Date",
    FORMAT(cl.Changed, ''dd.MM.yyyy'') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language"
FROM
    tblContent c,
    tblWorkContent wc,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.pkID = wc.fkContentID AND
    wc.fkLanguageBranchID = cl.fkLanguageBranchID AND
    cl.fkLanguageBranchID = lb.pkID AND	
    wc.StartPublish IS NULL')

Published Pages

Shows pages published during the last 7 days. To change the interval, update the last line of the query.

SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.StartPublish, 'dd.MM.yyyy') AS "Publication Date",
    FORMAT(cl.StopPublish, 'dd.MM.yyyy') AS "Publication Stopped",
    FORMAT(cl.Changed, 'dd.MM.yyyy') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    ct.Name as "Page Type"
FROM 
    tblContent c,
    tblContentType ct,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.fkContentTypeID = ct.pkID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.StartPublish > DateAdd(DD,-7,GETDATE())

SQL for saving the query

INSERT INTO SqlQueries (Name, Category, Query)
VALUES ('[2] Published Pages', 'Report Center', 'SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.StartPublish, ''dd.MM.yyyy'') AS "Publication Date",
    FORMAT(cl.StopPublish, ''dd.MM.yyyy'') AS "Publication Stopped",
    FORMAT(cl.Changed, ''dd.MM.yyyy'') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    ct.Name as "Page Type"
FROM 
    tblContent c,
    tblContentType ct,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.fkContentTypeID = ct.pkID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.StartPublish > DateAdd(DD,-7,GETDATE())')

Changed Pages

Shows pages changed during the last 7 days. To change the interval, update the last line of the query.

SQL for displaying the report

SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.Changed, 'dd.MM.yyyy') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    ct.Name as "Page Type"
FROM 
    tblContent c,
    tblContentType ct,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.fkContentTypeID = ct.pkID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.Changed > DateAdd(DD,-7,GETDATE())

SQL for saving the query

INSERT INTO SqlQueries (Name, Category, Query)
VALUES ('[3] Changed Pages', 'Report Center', 'SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.Changed, ''dd.MM.yyyy'') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    ct.Name as "Page Type"
FROM 
    tblContent c,
    tblContentType ct,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.fkContentTypeID = ct.pkID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.Changed > DateAdd(DD,-7,GETDATE())')

Expired Pages

Shows pages expired during the last 7 days. To change the interval, update the last line of the query.

SQL for displaying the report

SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.StartPublish, 'dd.MM.yyyy') AS "Publication Date",
    FORMAT(cl.StopPublish, 'dd.MM.yyyy') AS "Publication Stopped",
    FORMAT(cl.Changed, 'dd.MM.yyyy') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    ct.Name as "Page Type"
FROM 
    tblContent c,
    tblContentType ct,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.fkContentTypeID = ct.pkID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.StopPublish > DateAdd(DD,-7,GETDATE())

SQL for saving the query

INSERT INTO SqlQueries (Name, Category, Query)
VALUES ('[4] Expired Pages', 'Report Center', 'SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    FORMAT(cl.StartPublish, ''dd.MM.yyyy'') AS "Publication Date",
    FORMAT(cl.StopPublish, ''dd.MM.yyyy'') AS "Publication Stopped",
    FORMAT(cl.Changed, ''dd.MM.yyyy'') AS "Last Changed",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    ct.Name as "Page Type"
FROM 
    tblContent c,
    tblContentType ct,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    c.fkContentTypeID = ct.pkID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.StopPublish > DateAdd(DD,-7,GETDATE())')

Simple Addresses

SQL for displaying the report

SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    cl.ExternalURL as "Simple Address",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    c.VisibleInMenu AS "Visible in Menus"
FROM 
    tblContent c,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.ExternalURL IS NOT NULL

SQL for saving the query

INSERT INTO SqlQueries (Name, Category, Query)
VALUES ('[5] Simple Addresses', 'Report Center', 'SELECT
    c.pkID AS "Page ID",
    cl.Name AS "Page Name",
    cl.ExternalURL as "Simple Address",
    cl.ChangedByName AS "Changed By",
    lb.LanguageID AS "Language",
    c.VisibleInMenu AS "Visible in Menus"
FROM 
    tblContent c,
    tblContentLanguage cl,
    tblLanguageBranch lb	
WHERE
    c.pkID = cl.fkContentID AND
    cl.fkLanguageBranchID = lb.pkID AND
    cl.ExternalURL IS NOT NULL')

Link Status

SQL for displaying the report

SELECT
    cl.fkContentID AS "Page ID",
    cl.Name AS "Page Name",
    lb.LanguageID AS "Language",
    csl.LinkURL as "Broken URL",
    csl.HttpStatusCode AS "Link Status",
    csl.FirstDateBroken AS "Broken Since",
    csl.LastCheckedDate AS "Last Checked"
FROM 
    tblContentSoftlink csl,
    tblLanguageBranch lb,
    tblContentLanguage cl
WHERE
    csl.OwnerLanguageID = lb.pkID AND 
    csl.fkOwnerContentID = cl.fkContentId AND
    csl.OwnerLanguageID = cl.fkLanguageBranchID AND
    csl.FirstDateBroken IS NOT NULL

SQL for saving the query

INSERT INTO SqlQueries (Name, Category, Query)
VALUES ('[6] Link Status', 'Report Center', 'SELECT
    cl.fkContentID AS "Page ID",
    cl.Name AS "Page Name",
    lb.LanguageID AS "Language",
    csl.LinkURL as "Broken URL",
    csl.HttpStatusCode AS "Link Status",
    csl.FirstDateBroken AS "Broken Since",
    csl.LastCheckedDate AS "Last Checked"
FROM 
    tblContentSoftlink csl,
    tblLanguageBranch lb,
    tblContentLanguage cl
WHERE
    csl.OwnerLanguageID = lb.pkID AND 
    csl.fkOwnerContentID = cl.fkContentId AND
    csl.OwnerLanguageID = cl.fkLanguageBranchID AND
    csl.FirstDateBroken IS NOT NULL')

These SQL queries are a starting point that will give you approximately the same result as the reports in the old Report Center. Some of the old reports had more input parameters, but if you need them I'm sure you can add what's needed to make the queries fit your needs.

Update: In Episerver.CMS.UI 12.12.0 the report center is back. However, you can still use this addon to create your own reports.

Good luck!

Found this post helpful? Help keep this blog ad-free by buying me a coffee! ☕