Example

---

Solution Details

Step 1: Create a Site Entry + CSElement that outputs JSON

Site Entry & CSElement Setup 

The SiteEntry should be uncached so that the most recent configuration information is returned at all times.

In the CSElement set the contenttype accordingly:

Step 2: Write the CSElement Logic

The basic logic, that we repeat for each asset or setting that we are interested in, is to query the DB using <ics:sql> and then loop over the resultset with <ics:listloop>. For example:

<ics:sql sql="SELECT name,updateddate FROM Template WHERE status != 'VO'"
    listname="TemplateList" table="Template" />

"templates": [
    <ics:listloop listname="TemplateList">
	{"name":"<ics:listget listname="TemplateList"  fieldname="name"/>",
	"modified":"<ics:listget listname="TemplateList"  fieldname="updateddate"/>"},
    </ics:listloop>
],

A comprehensive list of SQL queries to fetch each configuration asset/settings can be seen in the appendix at the end of this article.

For the generation of the JSON data structure you could use Jettison (the library ships with the 11.1.1.8 version of the product), native Java 7 capabilities or (as the above example demonstrates) you could roll-your-own JSON output but that is not advised.

Step 3: Create an HTML Report

The JavaScript logic looks something like this..

1) Create a list of JSON feeds to fetch:

ENVS['dev-mgmngt'] = 'http://dev-mngmnt.example.com/sites/ContentServer?d=&pagename=settings.json';
ENVS['dev-dlvry'] = 'http://dev-dlvry.example.com/sites/ContentServer?d=&pagename=settings.json'; 
ENVS['test-mngmnt'] = 'http://test-mngmnt.example.com/sites/ContentServer?d=&pagename=settings.json'; 
ENVS['test-dlvry'] = 'http://test-dlvry.example.com/sites/ContentServer?d=&pagename=settings.json';  

2) Create a function to get the JSON feeds:

function getDataForEnvironment(url){
  return $.ajax({
    type: 'GET',
    url: url,
    dataType: 'jsonp',
    beforeSend: function (jqXHR, settings){
      jqXHR.originalEnv = env;
      jqXHR.originalUrl = url;			
    },		
    success: function(json, status, jqXHR) {
      console.log('....success fetching: ' + jqXHR.originalUrl);
      // store the returned data in ALLDATA
      ALLDATA[jqXHR.originalEnv] = json;
    },
    error: function(jqXHR, status, e) {
      console.log('....ERROR: Failed to get data from [' + url + '] ' + status + ' ' + e);
    }
  });
}

3) Fetch each JSON feed:

for (var env in ENVS) {
  console.log('Fetching data for env [' + env +'].');
  var promisedData = getDataForEnvironment(ENVS[env]);	
  promisedData.success(function (data) {});
} 

4) For each configuration asset or setting create a table in the report

For example, CSElements:

1) Get a list of unique CSElement names from all of the returned JSON data.
2) For each unique CSElement name, create a row in the table 
3)   Select 1 environment to represent the master or ideal state
     (e.g. "Everything should be like Production Delivery")
4)   For each environment, compare the last modified date of this envs CSElement to the master.
     Highlight any differences in last modified date or missing CSElements.
5)   Repeat... 

---

Appendix

This section contains various SQL statements that can be used to retrieve configuration settings from the DB. 

Templates 

<ics:sql
      sql="SELECT name,updateddate FROM Template WHERE status != 'VO'"
      listname="TemplateList"
      table="Template" />

CSElements

<ics:sql
      sql="SELECT name,updateddate FROM CSElement WHERE status != 'VO'"
      listname="CSEList"
      table="CSElement" />

Start Menus

<ics:sql sql="select sm.id, sm.cs_name, sm.cs_description, sm.cs_assettype,
      sm.cs_assetsubtype, sm.cs_itemtype, smr.cs_rolename, p.name
      from StartMenu sm, StartMenu_Sites sms, StartMenu_Roles smr, Publication p
      where sm.id=sms.ownerid and sm.id=smr.cs_ownerid and sms.pubid=p.id
      order by sm.id"
      listname="startList"
      table="Publication,StartMenu,StartMenu_Roles,StartMenu_Sites"/> 

Publishing Configurations

<ics:sql
      sql="select id, name, description, type, dest, factors from PubTarget"
      listname="pubTargetList"
      table="PubTarget" />

Tree Tabs

<ics:sql
      sql="select tt.id, tt.title, tt.tooltip, p.name as pubname, ttr.cs_rolename, ttsect.name as sectname
      from TreeTabs tt, TreeTabs_Roles ttr, TreeTabs_Sect ttsect,TreeTabs_Sites ttsites
      LEFT JOIN Publication p  on p.id=ttsites.pubid
      where p.id is not null and tt.id=ttsites.ownerid and ttsites.pubid=p.id and
      tt.id=ttr.cs_ownerid and tt.id=ttsect.ownerid
      order by tt.id"
      listname="treeTabList"
      table="TreeTabs,TreeTabs_Roles,TreeTabs_Sect,TreeTabs_Sites,Publication" /> 

Filters

<ics:sql
      sql="select name,description,classname from Filters"
      listname="filtersList"
      table="Filters" />

Attribute Types

<ics:sql
      sql="select id,valuetype,name,updateddate from AttrTypes where status != 'VO'"
      listname="AttrList"
      table="AttrTypes" />

WebReference Patterns

<ics:sql
      sql="select id,webroot,pattern,assettype,name,params,publication from WebReferencesPatterns"
      listname="WebRefList"
      table="WebReferencesPatterns" />

Device Groups

<ics:sql
      sql="select id,devicegroupsuffix,updateddate,name from DeviceGroup"
      listname="DeviceList"
      table="DeviceGroup" />

Site Entries

<ics:sql
      sql="select se.id,se.name,se.pagename,se.cselement_id,se.updateddate,cse.rootelement from SiteEntry se
            LEFT JOIN CSElement cse on cse.id = se.cselement_id where se.status != 'VO'"
      listname="SiteList"
      table="SiteEntry,CSElement" />

Webroots

<ics:sql
      sql="select id,name,rooturl,updatedby,updateddate from WebRoot"
      listname="webrootList"
      table="WebRoot" />

Page Definitions

<ics:sql
      sql="select pd.id, pd.name, pd.updatedby, pd.updateddate, pd.description, pdt.attributeid,
            pa.name as nameattr, pdt.requiredflag, pdt.ordinal 
      from PageDefinition pd, PageDefinition_TAttr pdt, PageAttribute pa where pd.status != 'VO'
            and pa.id=pdt.attributeid and pdt.ownerid=pd.id order by pd.id,pdt.ordinal"
      listname="pageDefList"
      table="PageDefinition,PageAttribute,PageDefinition_TAttr" />

FW_Application

<ics:sql
      sql="select id,name,updateddate from FW_Application where status != 'VO'"
      listname="FWList" table="FW_Application" />

Custom Elements

<ics:sql
      sql="select elementname from ElementCatalog where elementname like 'CustomElements%'"
      listname="elementList"
      table="ElementCatalog" />