How to query BO DB repository

system · January 3, 2009, 10:32am

I want to query BO dB repository like Microsoft Reporting Services.
I now BO repository has encrypted binary data. Maybe there is a way to do that.

Does anybody know?

bo_rapor (BOB member since 2009-01-03)

system · January 3, 2009, 10:58am

Sorry but you can’t query repository as it was in v4/5/6. A solution is to use the query builder tool but results are not very exploitable. So the solution is to use SDK to achieve it…

bernard timbal (BOB member since 2003-05-26)

system · January 6, 2009, 7:33am

Then, is only way sdk?

bo_rapor (BOB member since 2009-01-03)

system · January 11, 2009, 8:24pm

Yes, only sdk if you considere (like me) that Query Builder is not useable

bernard timbal (BOB member since 2003-05-26)

SebastienGoiffon · January 16, 2009, 1:46pm

Did you try the security viewer?

system · January 16, 2009, 1:54pm

how can i reach security viewer?
is it a tool or is it in BOXIR3.0?

bo_rapor (BOB member since 2009-01-03)

SebastienGoiffon · January 16, 2009, 1:58pm

No sorry only for Xir2 …

system · January 22, 2009, 3:19pm

Hi, I managed to work out how to decrypt the infoobjects table.

For Oracle you could use the following to build a universe but run it at your own risk. I have only used it against test and development environments just in case it causes and issue. Also, I wouldn’t rely on it working when upgrading Webi.

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’,’’’’)
,’|<','0') ,'|>’,‘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

Cheers

marklee (BOB member since 2008-12-12)

system · January 23, 2009, 1:22am

YOU ARE A GOD!!!

sticky (BOB member since 2007-04-20)

system · January 23, 2009, 7:55am

waow. thnx.

this is also desert;

replace(convert(char(10),lastmodifytime),’ ‘,’’) as lastmodifytime,

bo_rapor (BOB member since 2009-01-03)

system · January 23, 2009, 11:09am

hi,

thank you for your help. I want to add folder descriptions in my report.
do you column name of that?

bo_rapor (BOB member since 2009-01-03)

system · February 5, 2009, 3:08pm

do you have any idea about logging description of objects?

bo_rapor (BOB member since 2009-01-03)

system · February 5, 2009, 3:10pm

And also;

We are using nt authentication for bo. How can we report nt group names in our audit report?

Thnx

bo_rapor (BOB member since 2009-01-03)

system · February 12, 2009, 2:23pm

And one more question:

We can find universes with TypeID = 324, and groups with TypeID = 20.

How can we releate Universes with Groups?
This means; we want to say “x” group has right for “b” and “c” universes.

Is it possible?

bo_rapor (BOB member since 2009-01-03)

system · July 16, 2009, 11:57pm

Hey,

1st of all, thank you so for this code correction. I was going nuts with the code from businessobjectstips.com where the commenter missed the ‘0’ (zero replacement).

I have a question.

What is the TYPEID for Crystal Reports?

Is it 2?

I seemed to get the parameter objects related to Crystal Reports when I restrict by TypeID 2 only.

Thanks

exile (BOB member since 2008-05-03)

system · October 2, 2009, 9:20pm

It looks like for SI_Kind =‘CrystalReport’ the TYPEID/SI_OBTYPE is 2 but please verify

bdharma (BOB member since 2008-07-07)

system · October 25, 2010, 11:45am

That would be great!

Cybersoft (BOB member since 2007-12-08)

system · December 4, 2012, 3:33am

hi,

that work is awesome, was wondering and trying to understand how did you manage to decode the characters, is there a way or pattern you have…

how did you match the characters with Letters/Numbers?

appreciate if you could help me out here…really appreciate…

thanks,
-wasy

wasy.wasu (BOB member since 2006-09-18)

system · November 27, 2013, 3:32am

Does anyone has an updated decryption logic or character match table for BO 4.0 and BO 4.1?

The included query runs against my BO 4.0 CMS database but is not correctly showing all the object names.

ErikR (BOB member since 2007-01-10)

system · December 3, 2013, 4:56pm

i was able to change few of them and this is working fine for me…i can see all of the names properly.

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(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
to_char(objname)
,’"’,’"’)
,’)’,‘a’)
,’+’,‘b’)
,’-’,‘c’)
,’/’,‘d’)
,‘1’,‘e’)
,‘3’,‘f’)
,‘5’,‘g’)
,‘7’,‘h’)
,‘9’,‘i’)
,’:’,‘i’)
,’;’,‘j’)
,’=’,‘k’)
,’?’,‘l’)
,‘A’,‘m’)
,‘C’,‘n’)
,‘E’,‘o’)
,‘G’,‘p’)
,‘I’,‘q’)
,‘K’,‘r’)
,‘M’,‘s’)
,‘O’,‘t’)
,‘Q’,‘u’)
,‘S’,‘v’)
,‘U’,‘w’)
,‘W’,‘x’)
,’[’,‘y’)
,‘Y’,‘y’)
,’]’,‘z’)
,’|<',0) ,'|>’,1)
,’|'||'@',2) ,'|’||‘B’,3)
,’|'||'D',4) ,'|’||‘F’,5)
,’|'||'H',6) ,'|’||‘J’,7)
,’|'||'L',8) ,'|’||‘N’,9)
,’`|’,’$’)
,’!@’,’ ‘)
,‘Bo’,’.’)
,‘m}d’,’–’)
,‘m}l’,’,’)
,‘m}w’,’;’)
,‘m}z’,’:’)
,‘o}H’,’+’)
,’!m|N’,’’)
,‘m|N’,’’)
,‘m~T’,’?’)
,‘B|c’,’(’)
,’!B|d’,’)’)
,‘m|Z’,’-’)
,‘B|e’,’[’)
,‘B|’,’]’)
,‘B~s’,’&’)
,‘B~w’,’%’)
,‘B~v’,’#’)
,‘B~p’,’’)
,‘o~&’,’~’)
,‘m~L’,’!’)
,’!B{B’,’’)
,’{’,’’)
,’!’,’’)
,’"’,’’)
,’@’,’’||’’)
Object_name,

also, these are the types i was able to identify for my use.

case
when TypeID = 1 then ‘Folders’
when TypeID = 8 then ‘Shortcut’
when TypeID = 16 then ‘BO Servers Nodes’
when TypeID = 18 then ‘Favorites Folders’
when TypeID = 19 then ‘users’
when TypeID = 20 then ‘groups’
when TypeID = 22 then ‘Calendars’
when TypeID = 45 then ‘Categories’
when TypeID = 47 then ‘Personnal Categories’
when TypeID = 48 then ‘inboxes’
when TypeID = 57 then ‘Access Levels’
when TypeID = 59 then ‘Clusters’
when TypeID = 260 then ‘PDF Instances’
when TypeID = 266 then ‘Excel Report’
when TypeID = 268 then ‘Profiles’
when TypeID = 273 then ‘Relication list’
when TypeID = 275 then ‘Universes’
when TypeID = 291 then ‘Federation Jobs’
when TypeID = 291 then ‘Replication Jobs’
when TypeID = 295 then ‘Remote Connections (Federation)’
when TypeID = 319 then ‘Webi report’
when TypeID = 322 then ‘LCM Jobs’
when TypeID = 333 then ‘Probes’
when TypeID = 342 then ‘Universes (.unx)’
when TypeID = 363 then ‘Connections’
when TypeID = 365 then ‘Events’
when TypeID = 392 then ‘Platform Search Scheduling’
else ‘Null’
end object_type,

hope this helps…

let me know.

-wasy

wasy.wasu (BOB member since 2006-09-18)