Query Builder in BO – Additional Queries

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.