Plan for failures, hope for success!
Decoding the Crystal Reports Server 2008 Database – CMS_InfoObjects6
I’ve been looking for a way to run some sort of audit or just a peak at what’s included in the Crystal Reports Server 2008 Database. This database controls all the aspects of Crystal Reports Server including the parameters that are used to launch the WACS server, SIA, and the CMS (If you use CRS2008 you know what these terms mean, otherwise you wouldnt be looking at this blog right now)
So, in order to run some sort of audit on the database, you have to go through and look at the CMS_InfoObjects6 table. This table contains all the information on CRS2008 including Nodes, Folders and Reports.
If you look at the database table itself you notice that alot of the fields are in “Binary Data” format which makes it almost impossible to look at, well, almost….
Here is a nice query, that will help you get started in understanding and using the CMS_InfoObjects6 database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<span style="font-family: Courier New;">SELECT ObjectID, ParentID, OwnerID, LastModifyTime, UPPER(LEFT(ObjectName, LEN(ObjectName) - 2)) AS ObjectName, TypeID, Type, SI_CUID FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(ObjName AS varchar(2000)), 'S', 'v'), 'M', 's'), 'A', 'm'), ')', 'a'), '+', 'b'), 'C', 'n'), '-', 'c'), '/', 'd'), 'O', 't'), 'E', 'o'), '1', 'e'), '3', 'f'), 'G', 'p'), '5', 'g'), '7', 'h'), 'W', 'x'), 'U', 'w'), 'Q', 'u'), 'I', 'q'), '9', 'i'), ':', 'i'), ';', 'j'), 'K', 'r'), '=', 'k'), '?', 'l'), '[', 'y'), ']', 'z'), <a href="mailto:'!@'">'!@'</a>, ' '), 'B~S', '&'), '!BO', '.'), 'B|C"', '('), '!B|D', ')'), 'M|Z', '-'), 'M}L', ','), 'M|N', '_'), 'M}Z', ':'), '!B{B', ''''), '`|<', '0'), '`|>', '1'), <a href="mailto:'`|@'">'`|@'</a>, '2'), '`|B', '3'), '`|D', '4'), '`|F', '5'), '`|H', '6'), '`|J', '7'), '`|L', '8'), '`|N', '9'), '{', ''), '!', ''), '"', ''), <a href="mailto:'@'">'@'</a>, '') AS ObjectName, CASE WHEN TypeID = 262 THEN 'Webi Report' WHEN TypeID = 314 THEN 'Deski Report' WHEN TypeID = 283 THEN 'PDF' WHEN TypeID = 267 THEN 'Text' WHEN TypeID = 323 THEN 'Excel' WHEN TypeID = 266 THEN 'Universe' WHEN TypeID = 278 THEN 'Publication' WHEN TypeID = 299 THEN 'Connection' WHEN TypeID = 19 THEN 'User type 19' WHEN TypeID = 18 THEN 'User type 18' WHEN TypeID = 47 THEN 'User type 47' WHEN TypeID = 48 THEN 'User type 48' WHEN TypeID = 8 THEN 'Shortcut' WHEN TypeID = 1 THEN 'Folder' WHEN TypeID = 20 THEN 'Groups' WHEN TypeID = 13 THEN 'Server' WHEN TypeID = 16 THEN 'BO Server' WHEN TypeID = 21 THEN 'Event' WHEN TypeID = 24 THEN 'License Key' ELSE 'Other' END AS Type, ObjectID, ParentID, TypeID, OwnerID, Version, CONVERT(datetime, SUBSTRING(CAST(LastModifyTime AS varchar(32)), 1, 4) + '-' + SUBSTRING(CAST(LastModifyTime AS varchar(32)), 6, 2) + '-' + SUBSTRING(CAST(LastModifyTime AS varchar(32)), 9, 2) + ' ' + SUBSTRING(CAST(LastModifyTime AS varchar(32)), 12, 2) + ':' + SUBSTRING(CAST(LastModifyTime AS varchar(32)), 15, 2) + ':' + SUBSTRING(CAST(LastModifyTime AS varchar(32)), 18, 2) + ':' + SUBSTRING(CAST(LastModifyTime AS varchar(32)), 21, 4), 101) AS LastModifyTime, ScheduleStatus, NextRunTime, CRC, Properties, SI_GUID, SI_CUID, SI_RUID, SI_INSTANCE_OBJECT, SI_PLUGIN_OBJECT, SI_TABLE, SI_HIDDEN_OBJECT, SI_NAMEDUSER, SI_RECURRING, SI_RUNNABLE_OBJECT, SI_PSS_SERVICE_ID, ObjName, ObjName_TR, SI_KEYWORD, SI_KEYWORD_TR, LOV_KEY FROM CMS_InfoObjects6) AS BORepository</span> |
There is really no real magic to this query, and nothing really special. It tells you what the object names are, and converts the lastmodifydate from binary to human readable format. I have yet to figure out how to decrypt the SI_GUID, SI_CUID, SI_RUID and the Properties Field, but I will leave that for another day since the query above gives me the information I was looking for
And before I forget, I have to give credit to Andu over at the BOB forums his post is basically where this query comes from
Comments are closed.