BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

[XI 3.1] Audit Report


 
Search this topic... | Search XI Server Discussion... | Search Box
Register or Login to Post    Forum Index -> Server Administration, Installation, Upgrades -> XI Server Discussion  Previous TopicPrint TopicNext Topic
Author Message
SivakumarS
Principal Member
Principal Member



Joined: 10 Aug 2006

Posts: 463
Location: Singapore


flag
PostPosted: Wed May 05, 2010 2:14 am 
Post subject: Audit Report

Hi All,

I want to create a BO report with User, Group, Folder & Object detail. But it can't be done using default tables (meta data). I dont have any idea to do it, any one can you please provide me the work around or any other way to achieve it?

Thank you.

Warm Regards

_________________
thumbsup.gifseeya.gif SIVA seeya.gifthumbsup.gif
People forget how fast you did a job, People remember how well you did it...
Back to top
andu
Senior Member
Senior Member



Joined: 13 Apr 2005

Posts: 47



PostPosted: Wed May 05, 2010 2:39 am 
Post subject: Re: Audit Report

Here is one query based on xi3.x CMS MSSQL repository that might help you going on:

Code:


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'), '!@', ' '), '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'), '{', ''), '!', ''), '"', ''), '@', '') 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, 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          dbo.CMS_InfoObjects6) AS BORepository
Back to top
SivakumarS
Principal Member
Principal Member



Joined: 10 Aug 2006

Posts: 463
Location: Singapore


flag
PostPosted: Wed May 05, 2010 2:47 am 
Post subject: Re: Audit Report

Thanks for your detail, mine is Oracle 10g database.
_________________
thumbsup.gifseeya.gif SIVA seeya.gifthumbsup.gif
People forget how fast you did a job, People remember how well you did it...
Back to top
SivakumarS
Principal Member
Principal Member



Joined: 10 Aug 2006

Posts: 463
Location: Singapore


flag
PostPosted: Wed May 05, 2010 4:38 am 
Post subject: Re: Audit Report

This query is not same for Oracle, anyone can you send it for Oracle database ?
_________________
thumbsup.gifseeya.gif SIVA seeya.gifthumbsup.gif
People forget how fast you did a job, People remember how well you did it...
Back to top
datasmith
Forum Member
Forum Member



Joined: 07 Nov 2006
ASUG Icon
Posts: 19
Location: Mississauga, Ontario


flag
PostPosted: Tue Jul 20, 2010 1:19 pm 
Post subject: Re: Audit Report

I've been looking forever for a way on how to decrypt the contents of the ObjName column in the CMS_InfoObjects6 table. Thanks for the post. Very, very useful. icon_biggrin.gif
Back to top
pmm150
Senior Member
Senior Member



Joined: 14 Nov 2008

Posts: 82



PostPosted: Wed Jul 25, 2012 7:11 am 
Post subject: Re: Audit Report

I am looking for the Oracle SQL syntax for the following CMS_InfoObjects6 "SI query":

SELECT SI_EMAIL_ADDRESS, SI_FORCE_PASSWORD_CHANGE, SI_NAME, SI_ID, SI_USERGROUPS, SI_USERFULLNAME, SI_ALIASES, SI_DESCRIPTION, SI_LASTLOGONTIME, SI_PASSWORDEXPIRE FROM CI_SYSTEMOBJECTS Where SI_KIND='User'

Has anyone done this already?

Thanks!
Back to top
Roland Jentsch
Senior Member
Senior Member



Joined: 07 Feb 2006

Posts: 80



PostPosted: Mon Apr 08, 2013 3:18 am 
Post subject: Re: Audit Report

you should use the querybuilder (http://localhost/AdminTools/querybuilder/query.jsp), because the most fields are in binary format in the database! your query SQL won't work correctly ...
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Server Administration, Installation, Upgrades -> XI Server Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0491 seconds using 17 queries. (SQL 0.0033 Parse 0.0341 Other 0.0117)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo