Retrieve changed pages from EpiServer database with sql script

Calling all developers! We invite you to provide your input on Feature Experimentation by completing this brief survey.

AI OnAI Off

Home / Forums / Developers (Main Products) Forum / CMS 7.5 - 11 /

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

This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.