Home → General →

Asset Inventory

• Edit

Timothy Gilman

24 Jun, 2014 05:25 PM

Hello,

I have been asked to come up with an inventory and responsible owner of nearly every asset in our instance of Cascade Server. Specifically, we need the information for pages, files and blocks (especially XHTML and Text). For starters, we are looking for the following info of each asset:

Asset Id
Asset type
system path
system name
display name
keywords
content type (if applicable)
creation date
publishing status (if applicable)
indexing status (if applicable)
last update date
groups and individuals who have access to the file
number of edits in the last 12 months

My thinking is to start by executing sql queries on the database to get a list of Ids and Types for current assets, and then possibly use Web Services to get more information about those assets.

But before I get started, has anyone done anything similar before? Any suggestions? Any code/sql to share?

Thanks,

Timothy

1. 1 Posted by Wing Ming Chan on 24 Jun, 2014 10:34 PM

   

   Hi Timothy,

   You might want to check out my code library, specifically AssetTree and the various Asset classes.

   Basically, you can examine any asset type(s) using AssetTree::traversal, and within a child of an asset tree, you can retrieve the corresponding object, and through the object, any information like keywords and so on using various get methods.

   Wing

   • Edit

2. 2 Posted by Ryan Griffith on 11 Jul, 2014 03:17 PM

   

   Hi Timothy,

   I wanted to note that you can also build this out pretty easily within Cascade Server using Index Blocks by including:

   • Regular Content
   • System Metadata
   • User Metadata
   • Folder Access Rights

   The only thing you won't get would be Content Type, Block Types (to limit only Text and SD Blocks) and number of edits within the year.

   Alternatively, you could probably use the Locator Tool to traverse the Site, which could possibly give you more information, such as include when indexing/publishing. This would avoid the issue where assets are set not to be indexed.

   That being said, I was able to come up with the following query that obtains most of the items you are looking for:

   select 
       fc.id, 
       fc.assetType, 
       fc.blockType, 
       fc.cachePath, 
       fc.name, 
       fc.displayName, 
       fc.keywords, 
       (
           select 
               ct.name
           from
               cxml_contenttype ct
           where
               ct.id = fc.contentTypeId
       ) contentType,
       m.creationDate, 
       m.createdBy, 
       m.lastModifiedDate, 
       m.lastModifiedBy, 
       fc.shouldBePublished, 
       fc.shouldBeIndexed, 
       (
           select 
               count(a.id) 
           from 
               cxml_audit a 
           where 
               fc.id = a.entityId 
               and a.action = 'edit' 
               and a.tstamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 1 YEAR))*1000
       ) editCount 
   from 
       cxml_foldercontent fc, 
       cxml_entitymetadata m
   where 
       (
           (fc.assetType = 'BLO' and fc.blockType in ('STRUCTUREDDATA', 'TEXT')) 
           or fc.assetType in ('PAG', 'FIL')
       ) 
       and fc.isCurrentVersion=1 
       and fc.metadataId = m.id
   

   The major issue is with permissions, which are calculated programmatically within the application. Another issue is with the publish and index status. Depending on your definition of the two, they could both be inherited.

   Also, with indexing, there are other other ways an asset could be indexed or not. For example, a grandparent folder could be set not to be indexed, but the asset could still be indexed if an Index Block is set to start at the asset's parent folder. Another example would be if an asset's start date hasn't been reached, or it's end date has passed.

   Please let me know if you have any questions.

   Thanks!

   • Edit

3. Ryan Griffith closed this discussion on 18 Aug, 2014 07:42 PM.