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,
Thank you.. which is very useful, but i have an additional requirement on this, in case if i would like to check from past three months who all users are not using the Business objects. what all objects we need to consider. in case of Licence usage, we need to know this on priority basis.
Kindly help me on this..
Thank you..
Hey Rao, how about this?:
SELECT TOP 3000
si_id,
si_name
FROM
CI_SystemObjects
WHERE
si_kind = 'User' AND
si_lastlogontime < = '2010-01-01'
Make sure that it returns less than 3000 records (if not, you need to increase the number). Be patient I don't think that the property "si_lastlogontime" is indexed. Make sure that the date is set to your requirements. Please note, there should be no space between the "<" and the "=" characters, thank WordPress and our WP theme for trying to beautify the code's appearance.
Can any one help me to fetch the list of users those who have Scheduled rights in BO XI r2.
Hi,
Plz tell me how to fetch the list of users who have rights for scheduling Reports in the BO XI r2.
Plz provide me the Query for the same so that i can run it in the Query Builder to get the results.
Thanks.
Hi Madhu and Uday, I am not 100% certain if what you seek is possible with Query Builder. It would require a VERY complex query to traverse a user to all user groups to the rights on those groups to detect if anywhere along the line the right for “scheduling” is granted or denied. Unfortunately, I don’t have the time to build that query for you. In fact, you might want to you look at the free “Security Viewer” tool from BO to see if this meets your needs. Search this site for that term and you will find some guidance on getting the tool.
could you tell me that how to find the In-Activer user list.
Great question! This is not really possible with Query Builder alone. The reason is the BOE SDK query language does not support “minus” or “not exists” functionality. However, you could use Excel to get what you want with a little formatting and some Excel know how.
1) Query all users
2) Query all active users
3) Format both lists into Excel and the difference between #1 and #2 is the list of inactive users!
is it possible to get company name of the user?
Hi Kumar, yes, if the company is stored in the description field of the user (
si_description). Or perhaps it could be derived from the email address (si_email_address). In either case, both fields are not mandatory and flexible.I have a requirement where I need to generate the list of users accessing a particular universe in BO XIR2 through Macros or Query Builder. Kindly help me on this.
Chaithanya, since you said “users accessing a particular universe” so I interpret that you want to know who has accessed a universe and not who has access to a universe. The statements seem similar, but one deals with historical usage of the system and the other deals with security access. For historical usage of the system you must have auditing enabled and you will have to query your systems audit tables.
Thanks Julian for the prompt reply! I was aware of retrieving this data by querying the audit tables. But the issue is we did not have our auditing enabled from the beginning; hence do not get the complete data. Is there any other way possible? Maybe through Query Builer or even a code in MACROS?
Sorry Chaithanya, if you are after data regarding usage of a universe then auditing data is your only option.
If you want to know which users have access to a universe then this should be accessible through either Query Builder or the SDK.
Could you tell me how to find out the list of users who have access to a universe through Query Builder?
Requirement: List of all CReate license usage over last 3 ( or 6 months).
How can we achieve this either using the Activity Universe or the Query Builder?
Thanks for any help on this.
Hi Arnie, I have no idea what “create license usage” is? Please explain.
What I mean is how can I find the list of licenses being used in the past 6 months.Basically want to find how many licenses are being used currently, how many have been new licenses in say last 6 months etc.
Hope I’m clearer now.
Thanks a lot.
Perhaps my knowledge of BO licensing is outdated, but I don’t think that it is possible to track used licenses or new licenses as you are asking. BOE has either named user licenses or concurrent user licenses.
If you have a named user license then you could track license consumption by looking at named user counts, you could also query users by creation date to see how many new users have been added in the last 6 months (or any date range).
If you have concurrent licensing then things get more difficult to track. Adding new users to the system does not consume a license seat, only increasing user concurrency does. If you want to measure user concurrency trends than either you need to run this articles query at regular frequencies, or you need to mine your CMS auditing data (log on, log off audit events) and extract this information from the audit transactional data.
Thanks a ton Julian. You have been a great help.