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”
Does anyone know where I can find the documentation that gives information about the fields belonging to the ci_infoojbects, ci_systemobjects & ci_appobjects tables? I can always do a select * from … to get a list of fields but this doesn’t tell me what they contain.
The challenge is that not all fields apply to all types of objects. Below is a list of the “kinds” that are available in each table – I don’t guarantee that it is complete, but it should help you get started:
InfoObjects
———–
Agnostic
Category
CrystalReport
DFS.Parameters
Excel
FavoritesFolder
Flash
Folder
Hyperlink
Inbox
LCMJob
LicenseRestriction
MON.Probe
Pdf
PersonalCategory
PlatformSearchScheduling
Powerpoint
Publication
ScopeBatch
Txt
Webi
Xcelsius
XL.XcelsiusEnterprise
AppObjects
———-
AAD.Plugin
AdminTool
AlertingApp
AppFoundation
BEX.BExWeb
BIP.CafApplication
BIWidgets
CCIS.DataConnection
ClientAction
ClientActionSet
ClientActionUsage
CMC
CommonConnection
CRConfig
DataSearchUniverseDataAccessProvider
Designer
DFS.ConnectorConfiguration
Discussions
DSL.MetaDataFile
Folder
InformationDesigner
InfoView
LCM
LCMOverride
MetaData.BusinessFilter
MetaData.BusinessView
MetaData.DataDBField
MetaData.DataFoundation
MetaData.DataProcedure
MetaData.MetaDataRepositoryInfo
MOB_Mobile
MON.ManagedEntityStatus
MON.MBeanConfig
MON.MonAppDataStore
MON.Monitoring
MultitenancyManager
OpenDocument
Overload
Pioneer
PlatformSearchApplication
PlatformSearchApplicationStatus
PlatformSearchContentExtractor
PlatformSearchContentStore
PlatformSearchContentSurrogate
PlatformSearchDeltaIndex
PlatformSearchIndexEngine
PlatformSearchQueue
PlatformSearchSearchAgent
PlatformSearchServiceSession
QaaWS
ReportConvTool
RepositoryPromptGroup
RestWebService
StreamWorkIntegration
TransMgr
Universe
UpgradeManagementTool
VisualDiff
VisualDiffApp
VMS
WebIntelligence
WebService
XL.XcelsiusApplication
SystemObjects
————-
AlertNotification
AuditEventInfo
AuditEventInfo2
Calendar
Connection
CryptographicKey
CustomMappedAttribute
CustomRole
DependencyRule
DeploymentFile
EnterpriseNode
Event
Folder
HotBackup
Install
LicenseKey
LogicalGroup
MetricDescriptions
MON.Subscription
Server
ServerGroup
Service
ServiceCategory
ServiceContainer
User
UserGroup
-Dell
Thanks Dell,
My particular problem at hand is to identify which user groups are imported from SAP BW and which ones we created in the Launchpad. I’m therefore looking for a flag that will give me this information.
However as a general point, it would be great if the field descriptions were documented somewhere.
Richard
The only place you might find the field descriptions would be in either the .NET or Java Developer Guides (which are available at help.sap.com/bobip). However, neither list is complete.
To get the “source” information for a user, you’ll need to get the SI_ALIASES property. SI_ALIASES is a property bag that will contain one item for a user group (for Users it can have more than one…). Inside the property bag you want to look at the first part of SI_NAME for item 1. It will be something like “secEnterprise”, “secSAP”, etc. Unfortunately, I haven’t found a way to get just that piece of information through a query, so you need to get the full SI_ALIASES property and parse it yourself.
Here’s a snippet of Java code that I use to pull the SI_NAME value of the alias:
IUserGroup grp = (IUserGroup) infoObj;
IUserGroupAliases aliases = grp.getAliases();
for(IUserGroupAlias alias : aliases){
if (aliasName.isEmpty()){
aliasName = alias.getName();
} else {
aliasName += “;” + alias.getName();
}
}
-Dell