Some important queries in Business Objects Query builder

It is a day to day practice to query metadata. We always seek some query which listed down list of universe present in our system or which report is using which universe.

Below are the query I regularly use in my projects. Hope these will be helpful for you.

Universe vs report.
select si_id, si_name from ci_infoobjects, ci_appobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME='<universe name>'") and si_kind = 'Webi'
-----------------------------------------------------------
To get all universe data including a WebI report list 
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME='CCE Mass Channel'


Then, use the list of IDs to get a list of report names that use this Universe 
SELECT SI_NAME,SI_DESCRIPTION, SI_ID,SI_AUTHOR,SI_PARENT_FOLDER,SI_UNIVERSE,SI_HAS_PROMPTS
FROM CI_INFOOBJECTS
WHERE SI_ID IN (nnnnn,nnnnn,nnnnn)
-------------------------------------------------------
How do I get a list of all connections matching a substring 
SELECT SI_NAME
FROM CI_APPOBJECTS
WHERE SI_KIND='MetaData.DataConnection' AND SI_NAME LIKE '%CFD%'
-------------------------------------------------------
To list all WebI reports when objects exceed 1000 
SELECT SI_TARGETID,SI_KIND,SI_UPDATE_TS,SI_NAME,SI_ID,SI_SENDABLE,SI_OWNER,SI_FILES,SI_DESCRIPTION,SI_UNIVERSE,SI_PARENT_FOLDER,SI_OWNERID,
SI_SUBMITTERID,SI_STARTTIME,SI_ENDTIME,SI_WEBI_PROMPTS,
FROM CI_INFOOBJECTS
WHERE SI_KIND='Webi' and SI_NAME > 'L'


then use <= 'L' 
-----------------------------------------------------------
To list all report folders containing a string 
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE '%Dealer%' AND SI_KIND='Folder'


For Universe folders, use CI_APPOBJECTS 
------------------------------------------------------------
To list all Webi reports containing a string 
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE '%Dealer%' AND SI_KIND='Webi'
--------------------------------------------------------------------------------------------------
SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe'

Create a list of active/inactive users with last login time

SI_ALIASES,SI_NAME,

SI_EMAIL_ADDRESS,

SI_USERFULLNAME,

SI_DESCRIPTION,

SI_LASTLOGONTIME,

SI_ENT_USERFULLNAME,

SI_DISABLED,

SI_DESCRIPTION

FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'