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:
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
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.
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!!
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.
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).
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
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.
Hi Julian..thanks for replying..I tried this on three differnt browser but without any success…
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.
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
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
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
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 3000si_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.
Hello,
How to find out the newly added/removed users list in CMC using query builder.
Please help me on this.
Regards
Divya
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
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.
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
Hi Zach, I had to check the Query Builder Guide to confirm this… sorry to tell you but “CASE” is not a support syntax of Query Builder or the BO SDK query language.
Thank you so much Julian for your quick response.