This is the fifth part in the Citrix Configuration Logging Series. In part 1, we discussed what Citrix Configuration Logging was.  In part 2, we discussed how to prepare the database to log configuration changes. In part 3, we discussed how to set up the Citrix XenApp farm for Configuration Logging, in part 4, we looked at the “out of the box” reporting tools. In this part, we will look at the back end database schema.

Schema on the Surface

Here is what the database schema looks like on the surface.

ConfigLogSchema_thumb1

Just 3 tables – looks pretty easy…  But, if you look at some of the data in those tables, things become less obvious.  Let’s break each table down:

CtxLog_AdminTask_LogEntry – Every change to the XenApp farms creates a new row here.
LogEntry_RecordID Unique Identifier (primary key)
SiteId I honestly don’t know why this is here.  It seems like it might be some kind of farm identifier, but you can only have one farm per database.
LogEvent This holds events that happen on the log (database) as a whole.  This is a numeric value that corresponds to an enumeration.  Possible values are:
  • 0= None
  • 1= Created
  • 2= Cleared
DateTime Date/Time the change occurred.
LogonUserName The user that made the change.
LogonUserId The SID of the user that made the change.
LogonHostName Hostname of server that joins the farm.
LogonHostId SID of a server that joins the farm.
HostName IMA server used to make the change – remember that every change has to go through IMA.
HostId SID of the host HostName above.
Status Status of the change.  This is a numeric value that corresponds to an enumeration.  Possible values are:
  • 0 = Success
  • 1 = Neither success nor failure
  • 2 = Failure

CtxLog_AdminTask_Object – Object(s) changed.
Object_RecordID Unique Identifier (primary key)
SiteId Again – don’t know why this is here.
LogEntry_RecordID Foreign key to CtxLog_AdminTask_LogEntry table.
SequenceID Another one I’m not sure about.
AdminTaskType Enumeration - type of task performed:
  • 0 = None
  • 1 = Created
  • 2 = Modified
  • 3 = Removed
ObjectType Enumeration:
  • 0 = Application
  • 1 = Application Isolation Environment (AIE)
  • 2 = AIE Application
  • 4 = Farm
  • 5 = File Type Association
  • 6 = Folder
  • 7 = Installation Manager Application
  • 8 = Printer
  • 9 = Server
  • 10 = Server Group
  • 11 = User
  • 12 = Policy
  • 13 = Monitoring Profile
  • 14 = Load Manager
  • 15 = Virtual IP Farm Range
  • 16 = Virtual IP Server Range
  • 17 = Print Driver
  • 18 = Database
  • 19 = Zone
ObjectName Name of the object changed.
ObjectUid Internal object ID.  More specifically, this value comes from the object’s ID property in MFCOM.
PropertyList XML field.  Holds before and after values.
AdminTaskFormatResID ID of field in language specific resource file.

CtxLog_AdminTask_ReferenceList – Some objects reference other objects.  For instance, a published application can reference many server objects.  This table keeps track of changes to referenced objects.
ReferenceList_RecordID Unique Identifier (primary key)
SiteId -
Object_RecordID Foreign key to CtxLog_AdminTask_Object table.
SequenceID -
ObjectType Same as parent table.
ObjectNamesOriginal Tab delimited list of the names of the original referenced objects.
ObjectIdsOriginal Tab delimited list of internal object IDs of the original referenced objects.
ObjectNamesAdded Tab delimited list of the names of the added referenced objects.
ObjectIdsAdded Tab delimited list of internal object IDs of the added referenced objects.
ObjectNamesRemoved Tab delimited list of the names of the removed referenced objects.
FormatResId_Added Resource IDs of added objects.
FormatResId_Removed Resource IDs of removed objects.

Identifying Changes

As stated above, the PropertyList field in the CtxLog_AdminTask_Object table is a XML field.  This field maps out the before and after values of each property of an object after a change.  Here is an excerpt of what a PropertyList field looks like:

<?xml version="1.0"?>
<propertylist>
  . . .
  <property nameresid="290042">
    <valuelist original="0">
      <value>
        <valstr>Notepad - test</valstr>
      </value>
    </valuelist>
    <valuelist original="1">
      <value>
        <valstr>Notepad</valstr>
      </value>
    </valuelist>
    . . .

Notice that each property has a value where original=”0” or original=”1”.   If the two values are different, that is a change.  Original=”1” is the before value and original=”0” is the after value (that seems backwards to me).  So, from the excerpt above, we can see that “Notepad” was renamed to “Notepad – test”.

Resource IDs

Several of the fields have “ResID” somewhere in their name.  This is short for Resource ID.  The values in these fields are numeric and correspond to a language specific Resource File.  For instance, the nameresid in the excerpt above is 290042.  This maps to “Display Name” in the en-US resource file; however, 290042 maps to “Anzeigename” in the de-DE resource file.  The resource file(s) used to decode the numbers can be found on the computer running the AMC at:

%ProgramFiles%\Common Files\Citrix\Access Management Console – Report Center\Reports\ConfigurationLoggingReport

The English resources are located in ConfigurationLoggingReport.dll.  Other localized languages can be found in a subdirectory of the path given above.  For instance, the German language resources would be in:

<above path>\de\ConfigurationLoggingReport.resources.dll

This concludes our “behind the scenes” look at the database schema.  Now that we know exactly what information is stored in the database and how to decipher the data, we will look at how to do some custom reporting in the final post in this series.