We’re migrating away from Business Objects Enterprise (XI-R2, not the newer 3.X version) and into SQL Reporting Services (it’s about time!) and I needed an inventory of everything that was still in BOE. The repository is obfuscated so that it’s not human readable, but thanks for some sleuthing and an 8-year-old post from a Business Objects forum, I wanted to share the script I was able to come up with.
The hard part (decoding the obfuscated text from the database into human-readable format) came from a post by “marklee” on a Business Objects board (his post is about 1/4 of the way down):
http://www.forumtopics.com/busobj/viewtopic.php?t=124272
His query was also written for Oracle, so I converted it to one that would run against SQL Server, and then I added some additional content types based on what we had in our database, and then attached a few additional rollups (like showing the root folder, building out the full path to the report, and returning the number of instances of each report that had been generated.
You can download the full script if you’d like, or you can view the contents below. Enjoy!
select objectid, parentid, ownerid, convert(datetime, REPLACE(left(lastmodifytime, 10),' ','-') + ' ' + replace(substring(lastmodifytime, 12, 8), ' ', ':') + '.' + substring(lastmodifytime, 21, 3)) as lastmodifytime, upper(left(objectname,len(objectname)-2)) ObjectName, typeid, type, convert(bit, 0) as IsInstance, si_cuid into #results 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, convert(varchar(100), case when TypeID = 1 then 'Folder' when TypeID = 2 then 'Crystal Report' when TypeID = 260 then 'Web Intelligence Document' when TypeID = 290 then 'Object Package' when TypeID = 265 then 'Report Instance (TXT)' 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 = 319 then 'Excel (XLS)' when TypeID = 259 then 'Word (DOC)' when TypeID = 279 then 'Report Instance (PDF)' when TypeID = 266 then 'Universe' when TypeID = 278 then 'Publication' when TypeID = 299 then 'Connection' when TypeID = 8 then 'Shortcut' 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' 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' else 'Other' end) Type, * FROM [dbo].[CMS_InfoObjects5] where typeid <> 284 -- appears to be some kind of temporary file ) BORepository create clustered index #idx_c_results on #results (objectid) create index #idx_results_parentid on #results (parentid) create index #idx_results_ownerid on #results (ownerid) -- Mark the ones that are instances of another object (children of non-folder) update r set r.IsInstance = 1 from #results r join #results r2 on r.ParentID = r2.ObjectID where r2.TypeID <> 1 and r.TypeID <> 1 -- Define all the levels of the objects and add them to a lookup table DECLARE @ObjectLevel INT = 0 select 23 as objectid, 0 as parentid, 0 as ownerid, GETDATE() as lastmodifytime, convert(varchar(100), '$') as objectname, convert(int, null) as ChildInstances, convert(datetime, null) as LastInstanceTime, 1 as typeid, convert(varchar(100), 'Root Folder') as type, @ObjectLevel as objectlevel into #objects while @@rowcount > 0 begin ;with p as (select parentid, count(*) as instances, max(lastmodifytime) as LastInstanceTime from #results group by parentid) update #objects set ChildInstances = p.instances, LastInstanceTime = p.LastInstanceTime from #objects o join p on o.objectid = p.ParentID where o.ChildInstances is null and o.typeid <> 1 SET @ObjectLevel = @ObjectLevel + 1 insert into #objects select r.objectid, r.parentid, r.ownerid, r.lastmodifytime, r.objectname, null, null, r.typeid, r.type, @ObjectLevel as objectlevel from #results r join #objects o on r.parentid = o.objectid and o.typeid = 1 left join #objects o2 on r.objectid = o2.objectid where o2.objectid is null end -- Set up the object paths select objectid, parentid, lastmodifytime, convert(varchar(4000), '') as ObjectPath, objectname, childinstances, lastinstancetime, typeid, type into #objectswithpath from #objects where typeid <> 1 while @@rowcount > 0 BEGIN update #objectswithpath set parentid = o.parentid, objectpath = o.objectname + '\' + objectpath from #objectswithpath owp join #objects o on owp.parentid = o.objectid END select objectpath, objectname, lastmodifytime, ChildInstances, LastInstanceTime, type from #objectswithpath order by ObjectPath, objectname --select * from #results --select typeid, type, count(*) from #objects group by typeid, type order by count(*) DESC /* select type, case when isnull(ChildInstances,0) = 0 then 0 else 1 end, count(*), max(LastInstanceTime) --objectpath, objectname, lastmodifytime, ChildInstances, LastInstanceTime, type from #objectswithpath where ObjectPath not like '$\REPORTS OFF LINE\%' group by type, case when isnull(ChildInstances,0) = 0 then 0 else 1 end order by type, case when isnull(ChildInstances,0) = 0 then 0 else 1 end */ --select * from #objectswithpath where type = 'Word (DOC)' drop table #objectswithpath drop table #objects drop table #results