I have already shared complete details on the Query builder CMS tables in the below blog
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.
2 thoughts on “Query Builder in BO – Additional 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.