Skip to Content
Author's profile photo Manikandan Elumalai

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   

Assigned tags

      27 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Hi Teck,

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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      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

      Author's profile photo raghu nath
      raghu nath

      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

      1. Past one year how many users are using their account.
      2. How many users accounts are ideal for more than one year
      3. How many reports  they are refreshing and viewing
      4. Except personal folders…how many reports are there
      5. 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

      Author's profile photo Ravichandra Kandimalla
      Ravichandra Kandimalla

      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

      Author's profile photo Faisal Mehmood
      Faisal Mehmood

      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>'

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      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.

      Author's profile photo Faisal Mehmood
      Faisal Mehmood

      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

      Author's profile photo Chandrakanth Angannagari
      Chandrakanth Angannagari

      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

      Author's profile photo Masayuki Nagaya
      Masayuki Nagaya

      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,

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Faisal Mehmood
      Faisal Mehmood

      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.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Naveed AKHTAR
      Naveed AKHTAR

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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 ?

      Author's profile photo Former Member
      Former Member

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

      Thank you so much! I love your blog.

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

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

      Author's profile photo pramod matam
      pramod matam

      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

       

       

      Author's profile photo Hetal Kataria
      Hetal Kataria

      Hello,

       

      SELECT SI_NAME FROM CI_INFOOBJECTS WHERE

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