SAP Business Objects great Admin tool to view and query the admin auditing tables

A little known and little discussed tool included in the SAP BusinessObjects suite is something called Query Builder.  This simple webapp can be used to query your BusinessObjects repository to get all kinds of information not readily available in other places such as the Central Management Console.

The BusinessObjects repository is a database that contains all the information about the reports, universes, and security that make up your deployment.  Unfortunately, the data contained in this repository is stored in a binary format, so you can’t query it with conventional SQL tools.  That’s where Query Builder comes in.  Using queries that are very similar to SQL, you can tap the information hidden away there.  Since the repository is what drives the entire BusinessObjects system, there is a lot to explore.

A Quick Tour of Query Builder

To access the Query Builder, point your web browser to your BusinessObjects server.  Query Builder can be found at the following URL:  http://[server]:[port]/AdminTools/.  Log on as an Administrator to get full access to all the repository objects.  From here you can begin to construct your query.  There are three “tables” that you can query:

• CI_INFOOBJECTS
  Contains objects that are often used to build the user desktop, such as favorites folders and reports.
• CI_SYSTEMOBJECTS
  Contains objects that are often used to build the admin desktop and internal system objects, such as servers, connections, users, and user groups.
• CI_APPOBJECTS
  Contains objects that represent BusinessObjects Enteprise applications. For example, the InfoView and Desktop Intelligence objects are stored in this table.

A query can be as simple as this:

SELECT * FROM CI_INFOOBJECTS

This will return the details for all of the “InfoObjects” in your repository — all documents, folders, and other content.  You can filter this list using a WHERE clause just like you would in SQL. Using some of the basic properties, you can refine your query.

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
returns all WebIntelligence documents

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE ‘Monthly%’
returns all content starting with the word “Monthly”

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_NAME LIKE ‘Monthly%’ AND SI_RUNNABLE_OBJECT=1
returns a list of WebIntelligence documents that have a name starting with the word “Monthly” and are scheduled

To get all the reports created date , modified date , size of the report for auditing purposes

select SI_ID, SI_NAME,SI_SIZE,SI_UPDATE_TS,SI_CREATION_TIME,SI_STARTTIME,SI_ENDTIME,SI_KIND  from CI_INFOOBJECTS where SI_KIND='Webi'  Order BY SI_SIZE DESC

--- To get the all events of the documents refreshed or reports refreshed 

SELECT
  DETAIL_TYPE.Detail_Type_Description,
  AUDIT_EVENT.Start_Timestamp,
  AUDIT_EVENT.Duration,
  AUDIT_EVENT.User_Name,
  DBMS_LOB.SUBSTR (AUDIT_DETAIL.Detail_Text, 1000, 1),
  AUDIT_EVENT.Event_ID
FROM
  DETAIL_TYPE INNER JOIN AUDIT_DETAIL ON (DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID)
   INNER JOIN AUDIT_EVENT ON (AUDIT_DETAIL.Event_ID=AUDIT_EVENT.EVENT_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.SERVER_CUID)
   INNER JOIN EVENT_TYPE ON (AUDIT_EVENT.Event_Type_ID=EVENT_TYPE.Event_Type_ID)
   INNER JOIN V_DATE_HIERARCHY_PLUS  AuditEventDate ON (trunc(AUDIT_EVENT.START_TIMESTAMP)=AuditEventDate.DATE_FROM)
WHERE
  (
  EVENT_TYPE.Event_Type_Description  IN  ('Document Refreshed')
  AND  AuditEventDate.DIFF_DAYS  BETWEEN  -7 AND -2
  )



SELECT SI_ID, SI_CUID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_KIND, SI_AUTHOR, SI_OWNER
FROM CI_INFOOBJECTS
WHERE SI_KIND = 'Webi'
AND SI_AUTHOR='Administrator'


 SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME ='EFASHION'")

I think that this query is not for "to find the number of users in a group" but for
"all reports connected to a universe" as already mentioned few posts above.
- just typo

2.,
SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS
WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")



General Queries

To get BO Repository Information


SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4


To get BO File Repository Server Information


SELECT * FROM CI_SYSTEMOBJECTS
WHERE SI_KIND = 'SERVER' AND SI_NAME LIKE '%FILEREPOSITORY%'


To get the all the public folders (Non System Folders)


SELECT * FROM CI_INFOOBJECTS
WHERE SI_PARENTID=23 AND SI_NAME!='REPORT CONVERSION TOOL'  AND
SI_NAME!= 'ADMINISTRATION TOOLS' AND SI_NAME!= 'AUDITOR'


Some More queries 


To list all the WebI reports with prompts


SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,
SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,
SI_PROCESSINFO.SI_PROMPTS  FROM CI_INFOOBJECTS
WHERE  SI_KIND = 'WEBI' and SI_INSTANCE = 0 and
              SI_PROCESSINFO.SI_HAS_PROMPTS=1


To extract all the report names from specific folder


SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES
FROM CI_INFOOBJECTS 
WHERE SI_KIND = 'WEBI' AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER]


To get Reports those are spanning multiple universes


SELECT SI_ID, SI_KIND, SI_NAME FROM CI_INFOOBJECTS  WHERE SI_UNIVERSE.SI_TOTAL>1


Scheduled reports queries


To list all the events and corresponding event file location


SELECT SI_ID, SI_NAME, SI_FEATURES FROM CI_SYSTEMOBJECTS WHERE SI_KIND= 'Event'


To list all Scheduled reports based on event


SELECT SI_NAME, SI_SCHEDULEINFO  FROM CI_INFOOBJECTS
WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0


To list reports those are not scheduled


SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER 
FROM CI_INFOOBJECTS 
WHERE SI_KIND = 'WEBI' AND SI_CHILDREN = 0 AND SI_SCHEDULEINFO.SI_SCHED_NOW = 0


To get the list of all reports scheduled daily excluding Paused


SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,
SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS
FROM CI_INFOOBJECTS
WHERE SI_SCHEDULE_STATUS !=8  AND SI_RECURRING = 1


To get the list of reports scheduled by a particular user


SELECT * FROM CI_INFOOBJECTS
WHERE SI_OWNER = '<USER NAME>' AND SI_RECURRING = 1


Universe queries
To Show count of reports per Universe
SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_WEBI.SI_TOTAL > 0
To retrieve all Web Intelligence reports connected to a Universe
SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS 
WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME =’EFASHION’")


To Show all universes using a specific connection


SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS
WHERE CHILDREN("SI_NAME='DATACONNECTION-UNIVERSE' ", "SI_NAME='TEST'")


To list all Webi reports that uses the connection (multiple universes)


SELECT * FROM CI_APPOBJECTS, CI_INFOOBJECTS WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'", "CHILDREN('SI_NAME=''DATACONNECTION-UNIVERSE'' ', 'SI_NAME=''TEST'' ')")  AND SI_KIND='WEBI'

User/UserGroups queries

To find the number of users in a group

SELECT SI_NAME,SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS
WHERE SI_KIND = 'USERGROUP' AND SI_NAME='ADMINISTRATORS' 

To extract all the users from specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS 
WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")