Skip to Content

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”

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Richard Sharp

    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.

    (0) 
    1. Dell Stinnett-Christy

      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

      (0) 
      1. Richard Sharp

        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

        (0) 
        1. Dell Stinnett-Christy

          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

          (0) 

Leave a Reply