Manikandan Elumalai

April 17, 2013 2 minute read

BusinessObjects Query builder queries – Part III

Dear Folks,

This is the continuation of my previous two blogs on Query builder.

http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries

http://scn.sap.com/community/bi-platform/blog/2013/02/14/businessobjects-query-builder-queries–part-ii

Below are some more queries which I came across in past few days.

Publication

To list all the publications and their associated documents

SELECT SI_ID, SI_NAME, SI_PUBLICATION_DOCUMENTS FROM CI_INFOOBJECTS WHERE SI_KIND=’PUBLICATION’

To list all the WebI reports and their associated publications

SELECT SI_ID, SI_NAME, SI_PUBLICATIONS FROM CI_INFOOBJECTS WHERE SI_KIND=’WEBI’ AND SI_PUBLICATIONS.SI_TOTAL > 0

Users

To list User logon details and the license type

SELECT SI_ID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_LASTLOGONTIME, SI_FAILEDLOGONCOUNT, SI_NAMEDUSER FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

To list all the Users Inbox, Favorites and Personal folders

SELECT SI_ID, SI_NAME, SI_INBOX, SI_FAVORITES_FOLDER, SI_PERSONALCATEGORY

FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

To list recently accessed documents & received alerts for the User (BI 4.x)

SELECT SI_ID, SI_NAME, SI_RECENT_DOCUMENTS, SI_RECEIVED_ALERTNOTIFICATIONS

FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

Shortcut

To list Shortcut along with its Source report

SELECT SI_ID, SI_NAME, SI_SHORTCUT_TARGET FROM CI_INFOOBJECTS WHERE SI_KIND=’Shortcut’

Calendar

To list Calendar objects

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND= ‘Calendar’

Server & Server groups

To list Server and associated Server groups

SELECT SI_ID, SI_NAME, SI_SERVERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’SERVER’

Reports & Schedules

Find all reports schedule with a certain custom calendar

SELECT SI_ID, SI_NAME, SI_KIND, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,SI_NEXTRUNTIME, SI_SCHEDULE_STATUS, SI_STATUSINFO FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_TYPE in (8,9)

Scheduled jobs created by User

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_RECURRING=1 AND

SI_SCHEDULEINFO.SI_SUBMITTER = ‘<user>‘

Program Objects

To list all the Program Objects

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’PROGRAM’

Explorer Workspaces

To list all the Explorer workspaces

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’DATADISCOVERY’

Custom Access Levels

To list all the Custom Roles

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’CUSTOMROLE’

Hope you find this useful and interesting. Thanks for reading. Appreciate your valuable comments.

Query Builder Blog series

Basics

BusinessObjects Query builder – Basics

BusinessObjects Query builder – Best practices & Usability

Sample Queries

BusinessObjects Query builder queries

BusinessObjects Query builder queries – Part II

BusinessObjects Query builder queries – Part III

BusinessObjects Query builder queries – Part IV

BusinessObjects Query builder – Exploring Visualization Objects

BusinessObjects Query builder – Exploring Monitoring Objects

BusinessObjects Query builder – Exploring Lumira & Design studio Objects

Use cases

BusinessObjects Environment assessment using Query builder

BusinessObjects Environment Cleanup using Query builder

BusinessObjects Query builder – What’s New in BI 4.0

29 Comments

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

Former Member

April 25, 2013 at 9:45 am

Hi, is there a way to extract the folder privileges that each users is having?

Manikandan Elumalai

Blog Post Author

April 26, 2013 at 6:04 am

Hi Teck,

This is not possible through a single query instead you need to go for a custom SDK code.

Former Member

May 13, 2013 at 5:24 am

Hi Mani,

I read your all three blogs. They are very informative. Thanks for sharing it.

I had a query, Is it possible to find out which user has deleted any of the objects from BOE?

Thanks,

Aashutosh

Manikandan Elumalai

Blog Post Author

May 13, 2013 at 6:52 am

Hi Aashutosh,

You need to look at Audit Reports as this is not captured in CMS database.

You have to deploy Audit Universe and reports in BO4.0 and the report for your interest would be Objects - Events and Details - by User and Object Name.

Have alook here for Auditor deployment (Universe & Reports)

http://scn.sap.com/community/bi-platform/blog/2013/01/07/sap-businessobjects-40-auditor-configuration-deployment-end-to-end

Regards,

Mani

raghu nath

December 9, 2014 at 10:19 am

No words to speak about your blogs and Documents.

You resolved critical solutions in easy way …

Regarding Audit data base :

I followed your documents and I downloaded zip files which contains macros .still my issues is not resolved .Could you plz help

We are using :

Database : oracle 9i

Sapbo3.1 sp7 patch

we are having round 500+ users and more than 5000+ reports with 100+ universe

Requirement: user is asking for report with detail

Past one year how many users are using their account.
How many users accounts are ideal for more than one year
How many reports they are refreshing and viewing
Except personal folders…how many reports are there
Monthly ,weakly ,daily ,how many reports are refreshing

They need report

Could you plz help in ADUIT universe or Activity universe

Using AdminTools/querybuilder/ie.jsp.. iam able get all users and reports but not detail.As per my requirement

Ravichandra Kandimalla

December 9, 2019 at 10:28 pm

I'm trying to get users failed login attempt in last 1 year using the below.

SELECT SI_ID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_LASTLOGONTIME, SI_FAILEDLOGONCOUNT, SI_NAMEDUSER FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

This is just throwing me the error

Faisal Mehmood

July 29, 2013 at 5:49 am

Hi,I tried tihs query ,, but it is not taking my id,, may i know what exaclty goes here asa replacement of 'user'

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_RECURRING=1 AND

SI_SCHEDULEINFO.SI_SUBMITTER = '<user>'

Manikandan Elumalai

Blog Post Author

July 29, 2013 at 6:14 am

Working fine for me. You may need to use your User credential which you use for login to BO. Make sure you have scheduled atleast a single document to fetch the data.

Faisal Mehmood

July 30, 2013 at 3:27 am

Thanks,,, we use SAP credentials which means 'BIP~321\US****' or no need to BIP~321 ans only user id is good.. may be i can try that out,, Thanks anyway

Chandrakanth Angannagari

December 6, 2013 at 6:08 pm

Hello Mani

thanks for this

I was trying to get only the list of source documents (names) and not just the ids within each publication. What would be the query for the same

Masayuki Nagaya

December 18, 2013 at 9:08 am

Hi,

Is there a way to extract the access privileges / security that each users is having?

For example:

userA : No right to edit WebI report

userB : No right to refresh CR reports

userC : No right to edit Xcelsius contents

Or should I use SDK/API to manager the access privileges / security for group / user?

Regards,

Former Member

December 18, 2013 at 3:34 pm

Hi Mani,

Your blog is very informative. I've a question on how to get list of reports using a specific object from the universe.

Faisal Mehmood

April 10, 2014 at 10:21 pm

Hello Sir, These are very useful queries. I have a question on checking user in connection. We have recently moved to 4.1 from 3.1. We are usign new ID in our connections in migrated legacy olap universes... but still there are few jobs in our bw ssytem that runs under old 3.1 id.. we want to know which connection is using lets say 'OLDID'. is that possible to find out.

Former Member

August 18, 2014 at 11:54 am

Hi,

I need to extract user groupmembership detail passing SI_ID of user as parameter. So query will return all group name a user belongs to.

Former Member

August 19, 2014 at 3:51 am

Hi S.Datta,

You can try the below query :

SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup' AND SELECTUSINGPROPERTY(<SI_ID of user>,SI_ID,SI_USERGROUPS,SI_ID)

Let me know if this is working as desired.

Regards,

Upendra.

Former Member

November 13, 2014 at 7:16 am

Hello Mani,

Can you let me know any query through which we can find the User Preferences in a Infoview or Launcpad , weather he has selected web or Rich Internet Application or Desktop settings for a report to open in View or Modify mode.

Thanks in advance

Veena

Former Member

November 24, 2014 at 9:15 pm

We are in a need of having the list of all users who are all having access to each folder under public folders in CMC.

for suppose, under public folders if there were two folders A and B.

we need a list of all the users along with their rights having access to A folder and similar having access to B folder.

Your help is appreciable.

Thanks,

Harsha

Former Member

April 8, 2015 at 3:51 pm

Hello All,

I need to get the SI_SCOPEBATCH_SCOPE_RECIPIENTVALUESSET and SI_SCOPEBATCH_SCOPE_RECIPIENTINFOS values for the publication. Can you please share the code. This is to get the recipient parameter/info for the published report.

Thanks

Former Member

April 10, 2015 at 5:30 pm

Hello Manikandan,

I always referreed to your Blog Post and it helped me learn from Basics regarding using Query Builder and its limitations.

I have a requirement now to "Get List of Users from some AG Groups who has access to a folder but never ran a report " How can I achive this ?

TIA

Archana Reddy.

Naveed AKHTAR

July 27, 2015 at 8:26 pm

Hi Mani,

Is there any way to extract the Folder level security roles in BO 4.0 SP4, actually the requirement is to map Report/Folder/Role relationship, i have one file give me the information of Report and folder relationship. Looking the next part - Role associate with this folder.

Thanks

Nav

Former Member

October 7, 2015 at 10:25 am

Hi Mani,

can you help me to select only username and activity(disable or enabled)?

I tried to select SELECT SI_ID, SI_NAME, SI_DISABLED FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

But there is not selected field 'SI_DISABLED'.

Thanks

Manikandan Elumalai

Blog Post Author

October 7, 2015 at 12:21 pm

There is no separate property called SI_Disabled. You can try like this

SELECT SI_NAME,SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

This will list the status of each user alias.

Thanks

Mani

Former Member

October 7, 2015 at 12:33 pm

Thanks Mani,

There is not comfortable view by this 'select'.

It select some unnecessary fields, that's is difficult transport to excel.

Is it possible to select only SI_NAME, SI_DISABLED ?

Former Member

October 25, 2016 at 8:43 pm

Hi there. Is there a way to find out which user published a particular Universe?

Thank you so much! I love your blog.

Manikandan Elumalai

Blog Post Author

October 25, 2016 at 8:46 pm

SI_OWNER will be the column which tracks user who published the universe to repository

pramod matam

July 25, 2018 at 7:24 am

Hi Manikandan,

I have a requirement. I have set of webi reports which is built on Bex Queries, I need to get the all report names which is build on top of particular Query using Query Builder. Please let me know if any one knows .

Thanks in advance

Hetal Kataria

September 7, 2018 at 6:39 am

Hello,

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE

SI_PARENTID !='20091' AND SI_WEBI_DOC_PROPERTIES LIKE '%Name of the bex query%'

Chetan Suryanarayana

October 25, 2021 at 6:24 am

Hi experts,

Is there a way we can pull out a list of all the WebI reports from a repository that has a certain field/object used from the Universe/source DB using Query builder?

Monica Tesser

January 2, 2023 at 10:37 am

Hi, can I execute a query in query builder and obtain all the objects available in a universe ? (I mean the source of each label named in the universe) ? a sort of reverse engineering of the universe to get a data catalog. Thanks a lot

BusinessObjects Query builder queries – Part III

Assigned Tags