how to query BO DB repository

[bo_rapor]

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?

[bernard timbal]

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...

[bo_rapor]

Then, is only way sdk?

[bernard timbal]

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

[Sebastien Goiffon]

Did you try the security viewer?

[bo_rapor]

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

[Sebastien Goiffon]

No sorry only for Xir2 ...

[marklee]

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

[sticky]

YOU ARE A GOD!!!!!!!!!!!!!!!!!!

[bo_rapor]

waow. thnx.

this is also desert;

replace(convert(char(10),lastmodifytime),' ','') as lastmodifytime,

[bo_rapor]

hi,

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

[bo_rapor]

do you have any idea about logging description of objects?

[bo_rapor]

And also;

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

Thnx

[bo_rapor]

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?

[exile]

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