Custom reporting from the database - Last Updated and Last Updated By

tomlinson's Avatar

tomlinson

03 Dec, 2014 12:37 AM

Hi,

I've been asked to create a report similar to the Stale Content report that would list all pages in the site, including the Path, Last Updated and Last Updated By fields, like they appear in the Stale Content report.

I know that the cxml_foldercontent table contains page content, and I assume the Path is just the cachePath. However, I can't find any fields that correspond to Last Updated or Last Updated By. Where are those fields? Are they pulled from the most recent entry in the cxml_audit table for an edit action and the entityId?

Thanks!

  1. 1 Posted by Wing Ming Chan on 03 Dec, 2014 01:04 PM

    Wing Ming Chan's Avatar

    Hi,

    You need to look at cxml_entitymetadata. The id it contains is the metadataId in cxml_foldercontent. A join with these tables will give you what you want.

    I have documented most of the tables (with column names) in Tables.

    Wing

  2. 2 Posted by Wing Ming Chan on 03 Dec, 2014 01:07 PM

    Wing Ming Chan's Avatar

    The URL given above is not quite right. Here it is again: Tables.

    Wing

  3. 3 Posted by Ryan Griffith on 03 Dec, 2014 01:25 PM

    Ryan Griffith's Avatar

    Hi,

    Curious, but will the CSV export of the Stale Content report (see very bottom of page) provide what you are looking for? The export contains the following information for each asset within the Site:

    • Path
    • Title
    • Display Name
    • Last Updated
    • Last Updated By
    • Link to open the asset in your Cascade Server instance

    Note: if your report does not contain all assets, you could use the "Last Updated" report and enter a large value in the Content last updated more than field.

    Please let me know if you have any questions.

    Thanks!

  4. 4 Posted by tomlinson on 04 Dec, 2014 05:21 PM

    tomlinson's Avatar

    Thank you Wing, that's exactly what I needed!

    Ryan, I'm trying to get some extra information in addition that isn't included in the Stale Content Report, and I'm trying to only get pages and PDFs, so unfortunately, the Stale Content Report won't be enough, but thanks for the suggestion!

  5. 5 Posted by Wing Ming Chan on 05 Dec, 2014 05:24 PM

    Wing Ming Chan's Avatar

    After a second thought, I have remove the Tables page from my site and reserve it for my own use only. But table information can still be found here.

    Wing

  6. Ryan Griffith closed this discussion on 19 Jan, 2015 04:26 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac