Digging in to Citrix Configuration Logging: Exploring the Database
This is the fifth part in a series on Citrix XenApp Configuration Logging. This part will focus on the database schema, the information contained in the database, and how to decode certain parts of the data.
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.
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:
|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:
|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:
|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)|
|Object_RecordID||Foreign key to CtxLog_AdminTask_Object table.|
|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.|
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”.
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:
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.