AdminTools – Query Language

Query Language

BusinessObjects
Enterprise has a query language that allows users to query the system for information.  The following is documentation on how to use this tool.

Tool is accessed in IE using the URL: servername:port/AdminTools

The query language is similar to SQL but not all SQL options are supported.  Data is split across multiple tables with the most used being ci_infoobjects,
ci_systemobjects and ci_appobjects.

The following are a list of simple queries to get information from the system.  All queries return a max of 1000 rows by default.

Query Language
BusinessObjects Enterprise has a query language that allows users to query the system for information.  The following is documentation on how to use this tool.
Tool is accessed in IE using the URL:  servername:port/AdminTools
The query language is similar to SQL but not all SQL options are supported.
Data is located in three tables ci_infoobjects, ci_systemobjects and ci_appobjects.
Sample Queries
Scheduling information
select si_name, si_id, si_kind, si_scheduleinfo from ci_infoobjects where si_schedule_status = 9

Logon Information – Crystal Reports
select si_name, si_id, si_kind, si_processinfo.si_logon_info from ci_infoobjects where SI_PROGID=’CrystalEnterprise.Report’ and SI_INSTANCE=0

Logon Information – Webi
select si_name, si_id, si_kind, si_processinfo.si_logon_info from ci_infoobjects where SI_PROGID=’CrystalEnterprise.Webi’ and SI_INSTANCE=0

Query to get calendars
select si_name, si_kind, si_template_days from ci_systemobjects where si_kind = ‘calendar’

Query to get events
select si_name, si_kind, si_features from ci_systemobjects where si_kind = ‘Event’

Get All Webi reports
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Webi’ And SI_INSTANCE=0

Get All Crystal reports
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Report’ And SI_INSTANCE=0

Get All universes
Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND =’Universe’

Get All Users
SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=’CrystalEnterprise.USER’

Get all groups
Select * from CI_SYSTEMOBJECTS Where SI_KIND=’UserGroup’

Get all folders
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Folder’

Query limitations
By design, querying by type ids will not always return results.
By default the query will only scan 1000 rows of data.
In order to scan the full database use the “select Top XXX” where XXX = a number higher than the current number of objects (2000 works for now)
fields will be returned in the order they are stored, not the order requested.
Fields like “si_aliases” are “property bags” and will return all the fields in the property bag
Fields in the si_scheduleinfo and si_processinfo can be returned by prefixing the fields with the collection name
eg: si_processinfo.si_logon_info
si_processinfo.si_prompts

List of available Fields per table
To document the fields for each table, you can run a query that returns a few records.
Field names for the ci_infoojbects
Select * from ci_infoobjects where si_name = “your logon id”

Field names for the ci_systemobjects table
Select * from ci_systemobjects where si_name = “your logon id”

Field names for the ci_appobjects table
Select * from ci_appobjects where si_name = “a valid universe name”