Querying the Busines Objects XI CMS InfoStore Database Tables

Posted on January 25, 2009 by Julian 68 Comments

The question has come to my mind many times over the years and this month it has come to my inbox.
Thanks to Pluto for inspiring me to write this article.

The question: Is it possible to peer into the Business Objects XI CMS InfoStore (a.k.a. database repository) without Query Builder or the BOXI SDK????

The answer: No

All of my research and experience tells me the following:
Much of the meta data, the useful part especially, is stored in the CMS InfoStore in an encrypted binary format. No one has been able to decrypt this format yet, but I can’t really say that anyone has tried very hard. I certainly haven’t.

Some Things You Can Do with the CMS InfoStore’s Database Tables

The CMS InfoStore is mostly held in the database table named “CMS_INFOOBJECTS5”. Some other less significant data is held in the following tables: CMS_RELATIONS5, CMS_IDNUMBERS5, CMS_ALIASES5, and CMS_VERSION_INFO.

The structure of the CMS_INFOOBJECTS5 table in Oracle is as follows:
OBJECTID INTEGER NOT NULL, PARENTID INTEGER NOT NULL, TYPEID INTEGER NOT NULL, OWNERID INTEGER NOT NULL, LASTMODIFYTIME VARCHAR2(32 BYTE) NOT NULL, OBJFLAGS INTEGER NOT NULL, USERFLAGS INTEGER, SCHEDULESTATUS INTEGER, NEXTRUNTIME VARCHAR2(32 BYTE), ALIASES RAW(255), CRC VARCHAR2(32 BYTE) NOT NULL, PROPERTIES BLOB NOT NULL, SI_GUID VARCHAR2(56 BYTE), SI_CUID VARCHAR2(56 BYTE), SI_RUID VARCHAR2(56 BYTE), SI_INSTANCE_OBJECT INTEGER, SI_PLUGIN_OBJECT INTEGER, SI_TABLE INTEGER, SI_HIDDEN_OBJECT INTEGER, SI_NAMEDUSER INTEGER, SI_RECURRING INTEGER, SI_RUNNABLE_OBJECT INTEGER, SI_TYPEID_MACHINE INTEGER, SI_KEYWORD VARCHAR2(255 BYTE), SI_KEYWORDISTRUNCATED INTEGER, LOV_KEY VARCHAR2(18 BYTE), OBJNAME VARCHAR2(255 BYTE), OBJNAMEISTRUNCATED INTEGER
The “properties”, “aliases”, and “objname” fields are where most of the goodies are to be found and these are the fields that are encrypted or in some way pretty much unusable to us without the SDK or Query Builder.

A New Hope for CMS_INFOOBJECTS5

Don’t completely give up on querying this table directly. There are still quite a few valuable data that can be retrieved from the table. Firstly, you should know that the following fields are indexed and there for with a large CMS InfoStore database you will want to use these for filtering your query:
PARENTID OWNERID TYPEID LASTMODIFYTIME NEXTRUNTIME SCHEDULESTATUS OBJECTID 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_TYPEID_MACHINE SI_KEYWORD SI_KEYWORDISTRUNCATED LOV_KEY OBJNAME (this is encrypted though) OBJNAMEISTRUNCATED
Not all of these fields will have data, but some will and those that sporadically have data might meet your needs perfectly. I don’t really have a strong background in querying this table directly. So we are going to have to crack this nut together. Hopefully we can share our progress in the comments of this post and through updates from me to this article.

A Sample Use of a Direct Query on the CMS InfoStore

For example, I may want to extract a list of all si_cuid (the most unique identifier of an object) and objectid for objects that were created or modified on January 24th. To do this I would use the following query:
SELECT objectid, si_cuid FROM CMS_INFOOBJECTS5 WHERE lastmodifytime LIKE ('2009 01 24%')
If you look to the table structure you will see that the field lastmodifytime is of a character type and therefore it can be queried as presented above.

Why would you want to do this query directly on the database and not in Business Objects’ Query Builder?

Short answer: performance and possibility. What I mean to say is that you can control more precisely the performance of the query you write against the table. In fact, a similar query done through Query Builder might perform so slow that you it would not be possible except if you ran it directly against the database table. The truth is that many valid reasons to directly query the database will still end up with you taking the output of that query and then running it through the SDK or Query Builder to obtain all of the properties corresponding to that output.

What other database tables can I query?

CMS_RELATIONS5 and CMS_VERISON_INFO seem to have the only other bit of useful data. CMS_VERSION_INFO has a single row with the repositories version info; I am not certain that you can detect service pack or fix pack level here, in fact, I think you cannot. The CMS_RELATIONS5 table seems to be interesting, but I have not come up with a useful reason to query it beyond tinkering.

For your reference the CMS_RELATIONS5 table has the following structure in Oracle:
PARENTID INTEGER NOT NULL, CHILDID INTEGER NOT NULL, ISMEMBER INTEGER NOT NULL, ORDINAL INTEGER, RELATIONSHIPID INTEGER NOT NULL
None of the data appears to be encrypted. It also appears to have almost a 1-to-1 ratio between its record count and the count of the objects in the repository as reported in Business Objects Central Management Console. The value of this table might increase if we were to discover the meaning of different relationshipid values.

Conclusion: Where do we go from here?

I feel that I have only given you enough information to advance your tinkering on the true back-end. Honestly, this is a new frontier for me. I hope to post more information here as I find the time to experiment with directly with the database or if I hear from anyone else that has experience they would like to share. I invite you to share your findings as you play with querying the database. Use BO Query Builder concurrently to help you understand more about what you are seeing. If you find you need some help with Query Builder then I do recommend that you take a look at our Business Objects Query Builder Guide

Enjoyed this post? Share it!

68 thoughts on “Querying the Busines Objects XI CMS InfoStore Database Tables”

tf on April 7, 2009 at 11:21 am said:

select
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
translate(replace(objname,’BE’,’.’),’)+-/13579;=?ACEGIKMOQSUWY]!”{‘,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’),
‘`|>’,’1′),
‘`|@’,’2′),
‘`|B’,’3′),
‘`|D’,’4′),
‘`|F’,’5′),
‘`|H’,’6′),
‘`|J’,’7′),
‘`|L’,’8′),
‘`|N’,’9′),
‘M|N’,’_’),
‘M|Z’,’-‘),
‘@’,’ ‘),
‘`|<‘,’0’),
‘B~P’,’/’),
‘M}Z’,’:’),
‘B|C’,'(‘),
‘B|D’,’)’),
‘B~K’,’@’)
trans,cms.objname,cms.si_cuid,
cms.parentid,
decode((select typeid from CMS_INFOOBJECTS5 cmsp where cmsp.objectid = cms.parentid),311,’Webi Report’,308,’Deski’,1,’Folder’,(select typeid from CMS_INFOOBJECTS5 cmsp where cmsp.objectid = cms.parentid)) parent_typeid,
cms.objectid,to_date(substr(cms.lastmodifytime,1,19),’YYYY MM DD HH24 MI SS’)+1/24 lastmodifytime
,decode(typeid,311,’Webi Report’,308,’Deski’,1,’Folder’,typeid) typeid
from CMS_INFOOBJECTS5 cms

It’s not complete but should bring back something useful

For SQL Server try

select
objectid,
parentid,
ownerid,
lastmodifytime,
upper(left(objectname,len(objectname)-2)) 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’)
,’!@’,’ ‘)
,’B~S’,’&’)
,’!BO’,’.’)
,’B|C”‘,'(‘)
,’!B|D’,’)’)
,’M|Z’,’-‘)
,’M}L’,’,’)
,’M|N’,’_’)
,’M}Z’,’:’)
,’!B{B’,””)
,’`|’,’1′)
,’`|@’,’2′)
,’`|B’,’3′)
,’`|D’,’4′)
,’`|F’,’5′)
,’`|H’,’6′)
,’`|J’,’7′)
,’`|L’,’8′)
,’`|N’,’9′)
,'{‘,”)
,’!’,”)
,'”‘,”)
,’@’,”)
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 Type,
*
FROM [BOXI_CMS].[dbo].[CMS_InfoObjects5]
) BORepository
Dan P. on May 7, 2009 at 6:20 pm said:

I cut and pasted the code above into SQL Server and there are a ton of Formatting issues/problems. Did you try the query in SQL Server?
shh on May 28, 2009 at 8:57 pm said:

The above code is formatted for Oracle. The following code, which I adapted from the same poster on another blog will display some key fields for all recurring isntances in SQL Server:

SELECT TOP 100 PERCENT ObjectID, ParentID, OwnerID, LastModifyTime, UPPER(LEFT(ObjectName, LEN(ObjectName) – 2)) AS ObjectName, Type,
SI_RECURRING, Properties, SI_TABLE, SI_KEYWORD, ScheduleStatus, NextRunTime, SI_INSTANCE_OBJECT, SI_RUNNABLE_OBJECT, TypeID,
ObjFlags, UserFlags, Aliases, CRC, SI_GUID, SI_RUID, SI_PLUGIN_OBJECT, SI_HIDDEN_OBJECT, SI_NAMEDUSER, SI_TYPEID_MACHINE,
SI_KEYWORDIsTruncated, LOV_KEY
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’), ‘!@’, ‘ ‘), ‘B~S’, ‘&’), ‘!BO’, ‘.’), ‘B|C”‘, ‘(‘), ‘!B|D’,
‘)’), ‘M|Z’, ‘-‘), ‘M}L’, ‘,’), ‘M|N’, ‘_’), ‘M}Z’, ‘:’), ‘!B{B’, ””), ‘`|’, ‘1’), ‘`|@’, ‘2’), ‘`|B’, ‘3’), ‘`|D’, ‘4’), ‘`|F’, ‘5’), ‘`|H’, ‘6’), ‘`|J’, ‘7’), ‘`|L’,
‘8’), ‘`|N’, ‘9’), ‘{‘, ”), ‘!’, ”), ‘”‘, ”), ‘@’, ”) AS ObjectName,
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(SI_KEYWORD
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’), ‘!@’, ‘ ‘), ‘B~S’, ‘&’), ‘!BO’, ‘.’), ‘B|C”‘, ‘(‘), ‘!B|D’,
‘)’), ‘M|Z’, ‘-‘), ‘M}L’, ‘,’), ‘M|N’, ‘_’), ‘M}Z’, ‘:’), ‘!B{B’, ””), ‘`|’, ‘1’), ‘`|@’, ‘2’), ‘`|B’, ‘3’), ‘`|D’, ‘4’), ‘`|F’, ‘5’), ‘`|H’, ‘6’), ‘`|J’, ‘7’), ‘`|L’,
‘8’), ‘`|N’, ‘9’), ‘{‘, ”), ‘!’, ”), ‘”‘, ”), ‘@’, ”) AS SI_KEYWORD2,
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, LastModifyTime, ObjFlags, UserFlags, ScheduleStatus, NextRunTime,
Aliases, 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_TYPEID_MACHINE, SI_KEYWORD, SI_KEYWORDIsTruncated, LOV_KEY,
ObjName, ObjNameIsTruncated
FROM dbo.CMS_InfoObjects5) AS BORepository
WHERE (SI_RECURRING = 1) AND (NOT (SI_INSTANCE_OBJECT = 1)) AND (ScheduleStatus = 9) OR
(ScheduleStatus = 8)
ORDER BY UPPER(LEFT(ObjectName, LEN(ObjectName) – 2))

***************
ALSO, as a bonus, the following code is the Crystal Syntax for a formula to convert the NextRunTime field to a DateTime field in Crystal Reports.

Enjoy!
shh on May 28, 2009 at 8:57 pm said:

Local NumberVar YYYY;
Local NumberVar MO;
Local NumberVar DD;
Local NumberVar Hours;
Local NumberVar mm;
Local NumberVar ss;
Local NumberVar hh;

YYYY := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [1]);
MO := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [2]);
DD := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [3]);
Hours := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [4]);
mm := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [5]);
ss := ToNumber(Split({CMS_InfoObjects5.NextRunTime}, ” “) [6]);
hh := (If(Hours + 20 > 24) then (Hours – 4) Else (Hours + 20));

DateTime(YYYY,MO,DD,hh,mm,ss)
Chris W on July 28, 2009 at 8:06 pm said:

Hi,

I stumbled on this posting while searching for ways to query the Business Objects database schema. I’m specifically focused on trying to list the crystal reports, folders in which they are located and the sql (SELECT) stmt generated by Crystal for each report.

I’m using BO 12 and SQL Server 2005.

Is there any additional advice/guidance/scripts you would be willing to share or direct me to?

Thanks,
Chris.
Julian on July 29, 2009 at 5:05 am said:

Hi Chris,

I could totally be wrong, but the only place I know to get SQL is in the auditing data (if you have it enabled for Document Refresh). I am not sure if this captured for Crystal Reports though, I can only speak of WebI reports. Also as of BO XI R2 SP4 the path to the document is captured with each refresh (in the auditing data). So it would seem that I am saying the best place to get exactly what you need is in the auditing data. Of course, the limiter there is that the report actually had to be refreshed at least once after the auditing was enabled.

Regards,Julian
Chris W on July 29, 2009 at 12:34 pm said:

Thanks, I appreciate the advice and the prior postings/sample SQL on this topic.

Regards,
Chris.
Marsbeck on November 26, 2009 at 8:57 am said:

Hi,
Does someone know how to get ‘kind’ Information in InfoObject to know if a line result is for an User, a group, Universe, Document,…. ?

Thanks
Julian on November 26, 2009 at 4:48 pm said:

If I understand the question correctly…

There is a direct correlation between the database table field CMS_InfoObjects5.typeid and the logical field si_obtype. One day I went through all of the “kind” to find their “obtype” and then I mapped these to the “typeid”. This mapping table is in the Business Objects Query Builder guide that we sell here on this web site, so I won’t post the whole table. But I can tell you that CI_InfoObjects.si_kind "webi" = CI_InfoObjects.obtype "267" = CMS_InfoObjects5.typeid "267". If you need help mapping any other kinds let me know, or you could buy our little Query Builder guide and get teh complete mapping table and much more (a shameless plug, I know).
Marsbeck on November 30, 2009 at 2:57 am said:

Hi,

Does someone has an idea about how to reset password of administrator account directly in le CMS database. I’m thinking that, if it is possible to decrypt CMS informations, maybe it is possible to do that.

Thanks.
Julian on November 30, 2009 at 9:38 am said:

It is not possible to decrypt it, but you can blunt force reset it by deleting the record corresponding to the Administrator account (possibly just the password). This resets it to blank, or rather, no password. See this for more details:

http://www.ehow.com/how_5005044_reset-business-objects-administrator-password.html
Julian on November 30, 2009 at 10:27 am said:

Also more discussion on the topic here:

http://www.forumtopics.com/busobj/viewtopic.php?t=87029
Asha Stephen on October 21, 2010 at 6:53 am said:

I am begineer to BO..I wanted to know about CMS..While googling,I got this link..But I couldn’t understand anything from these posts..My inadequate knowledge might be the reason for me not to understand that,,Still I would be grateful If anyone explains in my level of understanding..

Thanks..
Julian on October 25, 2010 at 11:03 pm said:

Hi Asha, we all have to start somewhere. I will try and put together a small introductory article to help you and other like you get orienting more quickly to Business Objects XI’s general architecture. Thanks for the idea.
Ishaq on March 16, 2011 at 9:23 am said:

I have used the below tool to get reports from the CMS.

CMS/Connect Community Edition today!
http://www.infolytik.com

Thanks
link2boe on October 13, 2011 at 4:41 am said:

HI Ishaq,

I also tested this one and was impressed on 1st look.
Then I tested and had few queries; to my surprise, I got responded to my queries even before a ES rep does.

Nice, small tool to make life simpler.

Cheers,
PT
smruthi on February 8, 2012 at 10:57 pm said:

Hi, I need to find users list who have scheduled reports for future dates using query builder.
Thanks in advance!!

Regards, Smruthi
Julian on February 9, 2012 at 7:29 am said:

Hi Smruthi, Query Builder has some limits when trying to query two kinds of objects. Fortunately, the “report” objects have an “si_owner” object that indicates the user that created that object. Therefore, you know what your SELECT clause should be:
SELECT si_id, si_name, si_owner
Now you need to filter these results; you don’t want all reports. You said that you wanted “scheduled reports” that are in the future. That’s a filter, right there, or in the case of a Query Builder that is your WHERE clause:

First filter just to WebI reports (you could customize this as you wish to include “DeskI” or “Crystal Reports”):
WHERE si_kind = 'WebI'

Next filter to just scheduled instances:
AND si_instance = 1 AND si_recurring = 1

That is really all that you need, if the Business Objects report is a recurring instance than it is scheduled for the future. You might want more in the SELECT clause like the next run time or other properties.
smruthi on February 12, 2012 at 1:14 am said:

Hi julian,

Thanks for your reply:)

I need to create a list of how many users have been scheduling reports to public folders during last three months.

can u please help me to find the above requirement using query builder.

i am not aware of writing query..

Thanks in advance
link2boe on February 13, 2012 at 5:23 am said:

Hi Smruthi,

I will certainly leave this for Julian; however, what you are looking for requires nested query.

You firstly need to develop a query which will list all folders and sub-folders under ‘Public Folder’. Collect this information and use it in where clause of second query.

Second query would be pulling owner name of all reports of a definate kind. Similar to

select si_name, si_owner from ci_infoobjects where SI_Instance=1 and si_kind in (‘Crystalreport’,’webi’,’fullclient’) and si_owner not in (‘administrator’)
Julian on February 13, 2012 at 10:16 pm said:

Nice query link2boe!

smruthi, Query Builder does not really allow a nested query so you are going to have to do some manual filtering yourself. If you could get all of the object IDs for the public folders then you might be able to look at the output of the query link2boe has offered and just manually filter out…

This is not an easy request. I think you could create a schedule to a public folder and then analyze it quite a bit in Query Builder. You might find a trend that you could reuse on a broader scale. I wish your request were easier, but its not.
smruthi on February 13, 2012 at 10:29 pm said:

Hi Link2boe and Julian,

Thanks for your prompt reply:)

i am not familiar with querybuilder 🙁

how to find object id for the public folders…i mean in public folders having folders and folders having so many subfolders.
do you want me to find object id for the public folders manually or do you want me write any query in querybuilder.if yes can u please let me know how to write the query for find the objectid for the public folders.

Thanks in advance!!!
Julian on February 13, 2012 at 10:35 pm said:

We have a guide to teach Query Builder, so I am not going to attempt to do it here. However, try this query:

SELECT si_name, si_id FROM CI_InfoObjects WHERE si_kind = 'Folder'

That should get you all of the folders. The trouble here is that it will get you even the folders that are not public. Business Objects does not make this easy for you. Even I would have to play around for a half hour with this before I found a good way to do this. Unfortunately, I don’t have such time to give to you. 🙁
Saranya on February 15, 2012 at 1:43 am said:

Hi Julian,

How to identify using which client tool the user is connected to CMS e.g via designer, Deski, Webi Rich client etc?

Thanks.
Julian on February 15, 2012 at 8:13 pm said:

Hi Saranya, I don’t think that this attribute is captured in the auditing date, but I invite you to take a close look at all Audit_Detail records for a logon event and see if there is anything there. I can tell you that Query Builder won’t help at all with this request.
Leafieldthree on February 25, 2012 at 4:30 am said:

Hi does anyone know if it is possible query the cms to show which objects in a universe are contained in which reports? I’ve also been trying to query for SQL held for each object in the universe. I used to be able to do this in 6.5 but have not been able to in XI R3.
Julian on February 25, 2012 at 7:30 am said:

I REALLY need to write an article about this. I think this is the MOST POPULAR question that I see. For some reason, Google doesn’t help folks find the answer, so perhaps a dedicated article would help.

The Answer: No, it is not possible in BusinessObjects XI or BI. The only way this is possible is through the auditing tables, but this requires that (1) document refresh auditing be enabled and (2) that someone has refreshed the report.
Amit Kadam on March 21, 2012 at 10:05 am said:

how to find the list of instances of deleted reports
Julian on March 21, 2012 at 8:41 pm said:

Hi Amit, if a parent report is deleted all of the instances are deleted. Once deleted you cannot query them. Sorry.
pleino on April 9, 2012 at 3:46 pm said:

Hi,
any knowledge or example about how to read the contents of the field CMS_infoobjects6.properties?
I’d like to query the instances and find out which ones of them had CleanupAfterScheduling checkbox checked when the schedule was made…
Thanks!
Julian on April 12, 2012 at 3:07 am said:

Hi pleino, there is very little valuable information that you can obtain from directly querying the CMS_InfoObjects5 or CMS_InfoObjects6 physical tables. Most useful data in the table are encrypted. However, using either the Query Builder web application or the Business Objects Enterprise Software Development Kit (SDK) you can query the decrypted logical tables and obtain the information you seek.
Divya on July 6, 2012 at 3:01 am said:

Hi,

Is it possible to extract the information of tables that are used in universes?
My requirement is to check which all universes are using a particular table.

Thanks
Arasu on July 20, 2012 at 2:51 am said:

Hi,
we have windows2003 based BOXIR2 infrastructure which has 4 servers in cluster Server1, server2, server3 and server4.In this server1 and server3 has event server, each server has 300 file paths mapped. Server1 has some issues in hardware..we are planning to move file paths from server1 to server3. Its very difficult to open each event and change the server.. so we would like to know..is there any way to change the server using SDK or bat files to change the server… thanks in advance.
Julian on July 20, 2012 at 8:28 pm said:

Hi Arasuu, you should be able to do with with the BO SDK, but I have not modified and even read events before with the SDK. Please look into this deeper and report back your findings. Thanks!
Julian on July 29, 2012 at 7:39 am said:

Hi Divya, it is not possible to query the tables used in a Business Objects universe. This metadata is solely contained in the universe and NOT in the CMS InfoStore (the query-able metadata data source).
DCF on October 20, 2012 at 9:30 am said:

Hi all.
It is possible to extract dims and kpis of universes or businesslayer and allocated infoobjects from bo_cms?

Tranks

DCF
Julian on October 22, 2012 at 6:30 am said:

Hi DCF, there really is not much that you can retrieve from the CMS InfoStore with regards to the fields of a report or universe. As I have recommended to others in the past, you either need to have some sort of Excel macro using the COM SDK that will pull in universe metadata or you need to look to your auditing data, which captures all of the fields used in a report (but does not flag them as dimension, measure, or detail).
Milan on February 20, 2013 at 7:11 am said:

Hi Julian, I believe there must be a function to decrypt the data in the field ‘cms.objname’ since Query Builder shows the decrypted data. Do you have any information regarding this function.
Julian on March 1, 2013 at 11:00 pm said:

Hi Milan, Query Builder uses the BO SDK to decrypt the CMS’s metadata. Query Builder is just a handy SDK app that allows you to submit a query and receive the output on screen. I promise you that there is no DB function capable of doing this, just the BO SDK.
Jash on April 16, 2013 at 5:47 am said:

Is there a way to obtain the FRS path for the contents of a deleted folder without using SDK?

Situation is as below:
We had a situation where a user deleted a folder accidentally and now wants it restored. Unfortunately, while we can easily get the CMS DB restored from backup to a separate server, we do not have a test environment for BO at our disposal.
link2boe on April 19, 2013 at 8:31 am said:

HI Jash,

This answer will leave me curious as to what you are intending to, by finding the FRS path of deleted items, they would not be available there; only folder structure will remain.

To restore, you have to pull the backups of both FRS and CMS repo from same time.

To answer: You can point your CMS to recently restored CMS DB. And see the object properties of objects which were deleted from CMC. (repoint your repository to correct db when done)
If above is not an option then try finding Repository Master from somewhere.

http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=142346855#HowtobrowseCMSrepository-RepositoryMaster

Cheers,
PT
Steven on June 17, 2013 at 7:46 am said:

Hi All,

I Must elude to the fact that the information on this site is completely inaccurate. IT IS possible to decrypt the format of the OBJNAME field; it is not in BINARY format it requires a correctly ordered set of replace(replace()) statements. All info I have come across online so far do this in the wrong order.. for example:

let’s simplify:

say the OBJNAME field contains this text ‘ABCD’
and you write a replace function like this:

replace( replace(OBJNAME,'A','2') ,'2','4')

you end up replacing the A with 4 instead of the 2 as the innermost nested statement is evaluated first, working outwards. What happens is this:

Initial : ABCD
Step 1 : replace(OBJNAME,'A','2') = 2BCD
Step 2 : replace('2BCD','2','4') = 4BCD

Hope this makes sense… and I have managed to compile a complete set of the replace statements to do this correctly which I can post here later.

Thanks for reading
Steven
Julian on June 17, 2013 at 8:16 am said:

Hi Steven, as you probably expect I am VERY eager to see the work you have done here. PLEASE do share a real-life example of how this works. We’d all love to see how you could pull information such as document name, document owner, creation date, and universe with just a single SQL statement (albeit one with many nested REPLACE functions) executed from a regular SQL client tool.
Steven on June 17, 2013 at 8:56 am said:

Firstly; to remove any possibility of missing anything.. The SQL statement that I will post here (next post) will be structured to run on an ORACLE-based CMS platform. Some minor edits may be required for this to work if you run your CMS on any other platform such as SQLServer etc. THIS IS NOT A QUERY BUILDER query.
Steven on June 17, 2013 at 9:09 am said:

Ok… Here’s a trimmed down version and It’ll give the following columns:

Universe Name
Universe CUID
Document Name
Document_Owner (BOXI Username)
Document_CUID
Document_LastModifyTime

This can of course be fleshed out to include other fields other than OBJNAME, But I’ve included a few examples of the nested replace functions, but keeping the query to a readable size.

SELECT DISTINCT upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(UNIVERSES.objname,1,instr(UNIVERSES.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) Universe_Name, UNIVERSES.SI_CUID Universe_CUID, upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(Reports.objname,1,instr(Reports.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) Document_Name, upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_OWNERS.objname,1,instr(REPORT_OWNERS.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) Document_Owner, Reports.SI_CUID Document_CUID, Reports.LASTMODIFYTIME Document_LastModifyTime FROM BOXISYS.CMS_INFOOBJECTS6 UNIVERSES, BOXISYS.CMS_INFOOBJECTS6 Reports, BOXISYS.CMS_INFOOBJECTS6 REPORT_OWNERS, BOXISYS.CMS_RELATIONS6 Uni_to_Report WHERE ( UNIVERSES.OBJECTID=Uni_to_Report.CHILDID ) AND ( Uni_to_Report.PARENTID=Reports.OBJECTID ) AND ( UNIVERSES.typeid=280 ) AND ( Reports.typeid in ('2','308','303','260','309') ) AND ( Reports.OWNERID=REPORT_OWNERS.OBJECTID )
Steven on June 18, 2013 at 7:58 am said:

Guys,

I also see a request for finding out what objects are used by a given report. This can be done also, but it does use third-party file explorer tools.

The data is NOT stored anywhere in the CMS, but stored in the report document itself in the FRS.

To obtain this information I use an application called Agent Ransack, which is a file searching utility.

You need to point the app at your BOXI FRS root folder with the following options:

File Name: *.wid
Containing Text:
Look In: (this should be your FRS root folder on your server) it will look something like \Input\

Now…

There should be a contents view window docked at the bottom of the screen, use this to view the contents of the file. It will only display lines containing the text ; therefore only show the object names used by the report.

You can then export these results into a csv file for further analysis if required

Hope this is helpful

There may be other tools out there that allow you to do this; but this works well for me

Regards
Steven
Steven on June 18, 2013 at 8:01 am said:

ah…. the Containing Text item above doesn’t show…. it should be like this

(Less than sign)Name(Greater Than sign)

also, I should point out that the filenames returned are the filenames within the FRS which are like ~ce4145437612376.wid format; but I’m sure someone can figure out how to map the Report name to the filename 🙂 – anyone???

Steven
Steven on June 18, 2013 at 10:46 am said:

Anyone like to know a way to get the full report path from the CMS Database? have a look at the below query (be warned IT’S HUGE !!:

SELECT DISTINCT replace(replace(replace(replace(( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_10.objname,1,instr(REPORT_PATH_10.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_9.objname,1,instr(REPORT_PATH_9.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_8.objname,1,instr(REPORT_PATH_8.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_7.objname,1,instr(REPORT_PATH_7.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_6.objname,1,instr(REPORT_PATH_6.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_5.objname,1,instr(REPORT_PATH_5.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_4.objname,1,instr(REPORT_PATH_4.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_3.objname,1,instr(REPORT_PATH_3.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_2.objname,1,instr(REPORT_PATH_2.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) )||'\'|| ( upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_PATH_1.objname,1,instr(REPORT_PATH_1.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) ),'\\','\'),'\\','\'),'\\','\'),'\\','\'), Reports.OBJECTID, Reports.SI_CUID, upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(Reports.objname,1,instr(Reports.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')), upper(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(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( substr(REPORT_OWNERS.objname,1,instr(REPORT_OWNERS.objname,';',-1)-2) ,'!@{',' '),'!@',' '),'"',''),'BE{','.'),'!BE','.'),'A|N','_'),'A}]',':'),')','a'),']','z'),'B|-','('),'B|1','['),'B|3',']'),'-','c'),'1','e'),'3','f'),'5','g'),'7','h'),'9','i'),'`|','1'),'`|@','2'),'`|B','3'),'`|D','4'),'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'`{?','$'),'A|Z','-') ,'B~U','%'),'E~&','~'),'B~M','&'),'B|/',')'),'B~S','#'),'B{B',''''),'/','d'),'A}?',','),'E}T','>'),'B~G','/'),'@{',' '),'B~=','@'),'G','p'),'C','n'),'O','t'),'E','o'),'=','k'),'E}R','='),'?','l'),'K','r'),'+','b'),';','j'),'A~T','£'),'A','m'),'I','q'),'Q','u'),'S','v'),'U','w'),'W','x') ,'M','s'),'!','')) FROM BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_10, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_9, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_8, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_7, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_6, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_5, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_4, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_3, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_2, BOXISYS.CMS_INFOOBJECTS6 REPORT_PATH_1, BOXISYS.CMS_INFOOBJECTS6 Reports, BOXISYS.CMS_INFOOBJECTS6 REPORT_OWNERS WHERE ( Reports.typeid in ('2','308','303','260','309') ) AND ( Reports.OWNERID=REPORT_OWNERS.OBJECTID ) AND ( REPORT_PATH_1.PARENTID=REPORT_PATH_2.OBJECTID(+) ) AND ( REPORT_PATH_2.PARENTID=REPORT_PATH_3.OBJECTID(+) ) AND ( REPORT_PATH_3.PARENTID=REPORT_PATH_4.OBJECTID(+) ) AND ( REPORT_PATH_4.PARENTID=REPORT_PATH_6.OBJECTID(+) ) AND ( REPORT_PATH_6.PARENTID=REPORT_PATH_7.OBJECTID(+) ) AND ( REPORT_PATH_7.PARENTID=REPORT_PATH_8.OBJECTID(+) ) AND ( REPORT_PATH_8.PARENTID=REPORT_PATH_9.OBJECTID(+) ) AND ( REPORT_PATH_9.PARENTID=REPORT_PATH_10.OBJECTID(+) ) AND ( REPORT_PATH_10.PARENTID=REPORT_PATH_5.OBJECTID(+) ) AND ( Reports.PARENTID(+)=REPORT_PATH_1.OBJECTID )
Steven on June 18, 2013 at 12:16 pm said:

Another little gem for you. the characters at the end of the OBJNAME string do mean something. It seems to be a sort of CRC-type check that tells you the length of the resulting string after decryption. example:

)/A9C9MOK)OEK!;K
decrypts to
Administrator

the !;K on the end tells us that the resulting string length should be 13. See below:

Sequentially running downwards like so.

!;A = 3 !;B = 4 .. .. !;Y = 27 !;Z = 28 !;] = 29 !;^ = 30 !;` = 31 .. ..
missing a few that I haven’t had time to get yet, then the sequence starts again at:
!;{A = 58 !;{B = 59 .. .. !;{Y = 82 !;{Z = 83

Hope someone finds this useful

Regards
Steven
Marek on August 16, 2013 at 1:24 pm said:

Great work in decoding the strings.

Could anyone send me the queries for MSSQL to email bobjsql@seznam.cz (bobjslq at seznam.cz),please ?

Obviously, it is not possible to copy/paste from this site.

I have to correct the SQL, but some characters are still wrong – example after decoding “countrY o}T region o}T citY_dc_1”.

I was not able to work with decoding from Steven at all :(, but only from user TF.

Regards,
Marek