Retrieve changed pages from EpiServer database with sql script

Angelica Stenström

Vote:

I'm wondering how I can get hold of the changed date for a published page with sql.

We are using EPiServer.CMS.Core.10.4.3, EPiServer.Find.Framework.12.4.3

In code we do the following:

var query = searchInstance.Search();

query = query.Filter(x => x.Changed.InRange(DateTime.MinValue, todaysDate.AddMonths(-10))).FilterForVisitor();

query = query.Filter(x => !x.Ancestors().Match(startPage.ArchivePage.ID.ToString()));

....

(IRemindable inherits IContent and has a property DateTime Changed)

#182897

Sep 29, 2017 13:32

Vote:

Why do you need to do it in SQL?

#182976

Oct 02, 2017 22:14

Angelica Stenström

Vote:

The customer wants to receive a list of pages that hasn't been modified since a given date from production as a one time thing.

#182995

Oct 03, 2017 9:34

Vote:

You could work something out starting with [tblContentLanguage]. It has the datetime columns you need.

#183025

Oct 03, 2017 13:48

Stefan Holm Olsen

Vote:

Hi Angelica
I wrote this quick SQL query that returns the Content ID of pages matching your criteria.

It looks after a property called Changed, like you mentioned. If it is less than or equal to 10 months ago, it is returned.
I did not test it with language branches.

DECLARE @MinDate datetime
SET @MinDate = DATEADD(m, -10, GETDATE())
PRINT @MinDate

SELECT c.pkID
	FROM tblContent c
	WHERE
		c.ArchiveContentGUID IS NULL AND
		c.pkID IN
			(SELECT cp.fkContentID
			FROM tblContentProperty cp
			INNER JOIN tblPropertyDefinition pd ON pd.pkID = cp.fkPropertyDefinitionID
			WHERE pd.Name = 'Changed' AND cp.Date IS NOT NULL AND cp.Date <= @MinDate)

#183026

Oct 03, 2017 13:54

Angelica Stenström

Vote:

Thanks!

I will try to do that! :)

#183027

Oct 03, 2017 13:56

Try our conversational search powered by Generative AI!

Retrieve changed pages from EpiServer database with sql script