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.