I have already shared complete details on the Query builder CMS tables in the below blog
https://dwbicastle.com/2014/01/27/query-builder-in-bo-browse-query-bo-repository/
here added additional queries to get more details from the CMS.
To get cluster server details:
SELECT SI_CLUSTER_MEMBERS FROM CI_SYSTEMOBJECTS WHERE SI_ID=4
To get public folder and their subfolder details.
SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID=23
Public folder along with sub folders name, Description and ID
SELECT SI_NAME,SI_DESCRIPTION, SI_ID, SI_CUID FROM CI_INFOOBJECTS WHERE SI_PARENTID=23
To get the number of reports / document and number of sub folder in a particular folder.
SELECT SI_NAME,SI_CHILDREN FROM CI_INFOOBJECTS WHERE SI_PARENT_CUID=’Adr8scNuVFdIulMyYWJUTVI’
SELECT SI_NAME,SI_DESCRIPTION, SI_ID, SI_CUID,SI_CHILDREN FROM CI_INFOOBJECTS WHERE SI_PARENTID=3162
Individual Document / Report level details
SELECT * FROM CI_INFOOBJECTS WHERE SI_ID=171579
Document / Report Name, Description, Webi / Deski, Name of Data Provider, Data providers Source & number of Data providers:.
SELECT SI_NAME,SI_DESCRIPTION,SI_KIND, SI_PROCESSINFO.SI_FULLCLIENTDATAPROVIDERS FROM CI_INFOOBJECTS WHERE SI_ID=171579
Crystal Report – Query Builder results.
SELECT * FROM CI_INFOOBJECTS WHERE SI_ID=829259
Crystal reports – Query Builder Results
Available Universe connection Details
SELECT * FROM CI_AppObjects where SI_KIND=’MetaData.DataConnection’
To find the universe connection name and the database version
SELECT SI_NAME, SI_METADATA_PROPERTIES FROM CI_AppObjects where SI_KIND=’MetaData.DataConnection’
To get the complete Server Details
SELECT * FROM CI_SYSTEMOBJECTS
To get the particular user details – User Name, email Address, tagged user groups and whether the user is named user or not.
SELECT SI_USERFULLNAME,SI_EMAIL_ADDRESS,SI_NAMEDUSER,SI_ALIASES,SI_USERGROUPS FROM CI_SYSTEMOBJECTS where SI_USERFULLNAME=’bouser1’
Report refreshed today
Select * from CI_INFOOBJECTS where SI_LAST_RUN_TIME=’2018.05.09’
Failed instance Details
Select * from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME=2 and SI_ENDTIME=’2018.05.10’
Failed report and error Details
Select * from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME=2 and SI_SCHEDULEINFO.SI_ENDTIME=’2018.05.10′
Scheduled reports output format (Excel, Pdf,Txt)
Select * from CI_INFOOBJECTS where SI_KIND =‘Excel’
To list all the Universes
Select * from CI_APPOBJECTS where SI_KIND=’Universe’
To get the complete event information for specific trigger event Names
Select * from CI_SYSTEMOBJECTS where SI_KIND like ‘Event%’ and SI_NAME like ‘Sales Trigger%’
To get the customized calendar event
select * from CI_SYSTEMOBJECTS where SI_KIND like ‘Calendar%’
I have covered as much as possible to get the BO universe, reports, server, schedule& database results using query builder. Please feel free to post your comments & queries and will try to answer your queries.
Hi, Please could you suggest if there is a query to pull through the list of webi report based on a particular in particular universe using query builder.
Hi, Please could you suggest if there is a query to pull through the list of webi report based on a particular Class in the universe using query builder.