Skip to Content

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   

To report this post you need to login first.

24 Comments

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

  1. Aashutosh Hore

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
      1. 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

        (0) 
  2. 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>

    (0) 
    1. Manikandan Elumalai 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.

      (0) 
      1. 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

        (0) 
  3. 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,

    (0) 
  4. 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.

    (0) 
  5. S. Datta

    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.

    (0) 
    1. Bhavanam Reddy

      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.

      (0) 
  6. Veena B S

    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

    (0) 
  7. Harshavardhan Konathala

    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

    (0) 
  8. Raghu Raman Nanduri

    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

    (0) 
  9. Archana Reddy

    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.

    (0) 
  10. 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

    (0) 
  11. Olzhas Omarov

    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

    (0) 
    1. Manikandan Elumalai 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

      (0) 
      1. Olzhas Omarov

        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 ?

        (0) 

Leave a Reply