A Favorite from Query Builder – Active User List

Sometimes you can’t get Business Objects’ Central Management Console (CMC) to return the “Metrics” page and you need to know how many users are logged in to the system. Other times the CMC “Metrics” page works, but in the case of system administration, you may want to know who is logged in to your Business Objects XI CMS InfoStore. In either case, your best bet is to turn to BO Query Builder for the information you need.

In an effort to avoid artificial suspense building let me give you the query first and then tell you a little about it if you still have the time to read further:

blank space creating image

SELECT TOP 3000
*
FROM
CI_SystemObjects
WHERE
si_kind = 'Connection' AND
si_parent_folder = 41 AND
si_authen_method != 'server-token'
ORDER BY
si_name

blank space creating image

About the Query

This query returns all of the information available in the CMS InfoStore for all users that are currently logged in. The results are presented alphabetically by user name. IMPORTANT NOTE: the results will not be filtered by distinct user; therefore if a user account is used by multiple people or multiple applications are used you can expect to see multiple logins. The field si_lastlogontime might be useful in making sense of multiple entries.

At the moment I am writing this I started to wonder if scheduled jobs created by a user result in a login returned by this query. I will look in to this and follow-up if someone doesn’t already test this out and leave a comment.

I invite you to play with this query and try to figure out what is so magical about “si_parent_folder = 41“. Please share your thoughts and discoveries by leaving a comment here. I also recommend that you take a look at our newly published “Business Objects Query Builder Guide“, it is most likely “The Most Complete Business Objects XI Query Builder Guide Ever Written” and it will help you to discover and master the secrets of Business Object’s Query Builder, such as the one discussed in this article.

Enjoyed this post? Share it!

 

88 thoughts on “A Favorite from Query Builder – Active User List

  1. Hi,

    This is my first comment on your blog!! I am very new to this BO tech and I hav been visiting your blog quite frequently. Its really good.

    I need to find out which user has modified the report. In CMC, we can just see the modified date. Is it possible using Query builder? If not, is there any other way we can get this info?

    Thanks!!

  2. Hi,

    I don not know whether it is correct to ask here or not.

    When I run a query in query builder I came to know some users SI_LASTLOGONTIME is null.

    is it really true? or I need to try it any other way to get the users last login time.

    Query: select si_name from CI-sysobjects where SI_lastlogontime = null

    I got some user names for that query.

    Actually I am sure that those users had logeed into BO environment at some point

    can you help me regarding why I am not able to get those users last login time.

  3. Hi Prem, it is possible that at any given patch level the internal functions that update this field may not be working correctly. You are correct to assume that a null value should mean that the user has never logged in. I suggest you test a few of those users by logging in as them and then rechecking their SI_LASTLOGONTIME. However, even if you see it correctly update this doesn’t mean that it was functioning correctly in the past.

    Another way to look at this would be to have some basic auditing (logon, refresh, view) enabled. Auditing is outside of Query Builder, but you could simply query our auditing tables for ANY records for users you suspect. In fact, if you have a complete list of BusinessObjects users you could query the Auditing Tables (Audit_Event is really the only one you would need for this query) against your list of users and return all users that have no auditing records. These would be the users that have not logged in to the system (since the table was last purged).

  4. Hi All,

    I want to list all users. I type the following query in query builder.

    select * from CI_SystemObjects where si_kind = ‘user’

    However, when i submit the query, I am automatically logged out from the query builder. Want to know if the result of the query is saved anywhere or if there is any problem with the QB…Is it possible that QB is not configured properly..Does it needs to be configured or is it configure by default when BO is installed. I am using BO XIR2

  5. Hi KK, the query result is not saved anywhere. I don’t know why you are being logged out. I suggest you try different web browsers. You may also need to restart your web application server. The only other thing you could try is to replace “*” with some properties/fields such as si_id and si_name; however, just “*” should be fine, but it is something you could try.

    By the way, BusinessObjects does not require you to configure Query Builder, in fact, the only configuration that I am aware of is one that will enable QB to support path queries, but this is DEFINITELY not needed.

  6. Hi Julian..thanks for replying..I tried this on three differnt browser but without any success…

  7. Hi KK, thanks for the update. Please try (1) to restart the “adminlauch” (XI R2) or “AdminTools” (XI 3.1/BI 4.0) web application and failing this (2) please try to redeploy the adminlaunch/AdminTools web application.

  8. I see in the comments that some people are looking into getting the number of named licenses. one of your commenters said maybe this query would work:

    SELECT SI_NAME, SI_USERFULLNAME, SI_CREATION_TIME, SI_LASTLOGONTIME
    FROM CI_SYSTEMOBJECTS
    WHERE SI_KIND = ‘User’ AND SI_CREATION_TIME>= ‘2010.01.01’

    would you know if this is OK? also, since it was 2 yrs since it was last asked… maybe there’s a better way to retrieve the users with a license assigned to them.

    thank you

  9. Hi Pablo,

    In BusinessObjects’ object model the “User” objects has a property called “SI_NAMEDUSER”. If this property’s value is set to “TRUE” then the user is a named user.

    Try using a query like this in Query Builder:

    SELECT
    si_name,
    si_userfullname,
    si_creation_time,
    si_lastlogontime
    FROM
    CI_SystemObjects
    WHERE
    si_nameduser = 1

  10. Hi,

    Question is: How can we identify the active users and reports. If we don’t have any monitoring and auditing tools. I am using BO XI R2. I want to delete
    inactive users for more than last 3 Months.

    Please suggest.

    Thanks,
    Nikita

  11. Hi Nikita, you ask a great question which highlights a major omission from this article. You don’t need to have monitoring or auditing enabled on a Business Objects system to determine this. The following Query Builder query will give you a list of users that have not used the system since August 2012

    SELECT TOP 3000
    si_name,
    si_lastlogontime
    FROM
    CI_SystemObjects
    WHERE
    si_name NOT IN ('Administrator','Guest') AND
    si_kind = 'User' AND
    si_lastlogontime < '2012-08-01' ORDER BY si_name

    Of course, you should change '2012-08-01' to meet your needs. Please reply back with your observations. By the way, I have heard some folks report that this attribute "si_lastlogontime" is not always accurate based on their testing (such as only updates with every other use of the BO system), but I have not looked into it.

  12. Hello,

    How to find out the newly added/removed users list in CMC using query builder.

    Please help me on this.

    Regards
    Divya

  13. Hello,
    When i am trying to run this query.it gets error message.Could you please check and provide me the correct query.
    Error message: There was an error retrieving data from the server: An error occurred at the server : Not a valid query.
    SELECT COUNT(SI_ID) FROM CI_SYSTEMOBJECTS
    WHERE SI_KIND=’User’ and SI_CREATION_TIME >= ’2010-01-01-00-00? and SI_CREATION_TIME < '2011-01-01-00-00'

    Thanks in advance!!

    Regards
    Divya

  14. Hi Divya, could this be the problem “’2010-01-01-00-00?”, the question mark?

    BTW, you will never be able to query for recently deleted users as they will be gone. The best you could possibly do is compare your auditing data to your CMS user data (accessible by Query Builder). Also, I wonder if auditing data would capture user deletion detail. Look there and please report back.

  15. I’m trying to run a query and order by SI_ENDTIME but I want to put a case statement in so that if SI_ENDTIME IS NULL those records will show up at the top.

    Something Like
    ORDER BY CASE WHEN SI_ENDTIME IS NULL THEN SI_StartTime ELSE Si_EndTime END DESC

    but I can’t figure out how to use a case anywhere

  16. Hi Julian,

    Thank you so much for helping the BO forums and engineer like me who has limited knowledge on Business Objects.

    My client wants to know the number of active users at a given point in time.
    So i took this Query and they are impressed.

    However, they want to know ~~ can we schedule this query every hour on the system to know the number of users logged in? If yes, could you please help me how to set that up?

    Thank you!!

  17. Auditing data will never give you an accurate up-to-date picture. The best option would be to create a scheduled process to extract the QueryBuilder’s query’s output to a text file and either email this, or load it to a database where a report could be then run. The extract would require a BOE SDK script.

  18. I am trying to build a list of users on my system and what group they belong to. I have rules in place that a user will only belong to one group at any one time so I am struggling with finding the name of the group the user belongs to. I have started with this query (from above)
    SELECT * FROM CI_SystemObjects
    WHERE si_nameduser = 1
    order by SI_NAME
    and am trying to figure out which object (or how to get the object) that gives me the name of the Group that each user belongs to.

    Thanks in advance!

  19. Hi Julian,

    Good Day!

    Kindly let me know is there any way to find out the list of users deleted in a particular day
    Or
    the list of users deleted and the time it got deleted.

    Thanks,
    Sandhya

  20. Hi Sandhya, if auditing is enabled for this event, the BO CMS should log these actions to the auditing logs and then to the tables.

  21. Haii I wanted to know how to write a query in querybuilder to know the userlist for the particular universes only..??

  22. Hi nazma, the user list for a universe can either be interpreted as (1) the list of users who have used the universe or (2) the list of users who currently have access to the universe. If you are after #1 then you will need to look to the auditing database of your CMS. If you are after #2 then good luck to you, security queries in Business Objects are next to impossible and I avoid them at all costs.

  23. Hi,

    Could you please let me know what is the query to to find out how many times users have logged onto BO in a month from the query builder? or is there any object from activity universe to create a report?

    Thanks in Advance

  24. Hi Nisha, usage datais almost already only available from the auditing data. In CMC you should be able to enable use logon/logoff, but I think it is a default setting. I am not certain if the data collected is highly accurate, but it should be relatively accurate to itself.

  25. Hi All,

    Can any one tell me how would I pull the list of users who have saved the reports in their my favorites along with the report name?

    Please let me know, if you have any idea on this.

    Thanks inadvance

    Priyanka G P

  26. Hi All,

    Please let me know how to pull the user’s list who have saved the reports in my favorites, along with the report names.

    Is that possible?

    Please help.

    Thanks inadvance

    Priyanka G P

  27. hi
    can you please help me with below query
    How can we identify the active reports for the last 6 months?

  28. Hi,

    I refer to the message from Greg (October 4th, 2013) cause it wasn’t answered so far.

    “I am trying to build a list of users on my system and what group they belong to. I have rules in place that a user will only belong to one group at any one time so I am struggling with finding the name of the group the user belongs to.”

    Is this possible? Cause I can display the Users and User Groups separately, but not at once by using e.g. “grooup by” syntax. Something like “SELECT username, usergroup FROM si_systemobjects GROUP BY user”, to display users and usergroups the user belongs to. And also vice versa to display usergroups containing the list of users of each usergroup.

    Thanks in advance,
    Artur

  29. Hi Guys

    I was wondering how to query for active / Inactive accounts.

    Cant find anything on the web.

    Thanks

  30. Hi,

    Question is :
    How can i find:
    a)Max number of unique logins &
    b) max number concurrent loggings in the last 6 months.?

    Is it possible to find the same using query builder?

  31. Hi,
    would you please help me with a query to find the users who have Edit access rights in SAP BO through Query Builder

Leave a comment

Your email address will not be published. Required fields are marked *