Tracking Concurrent Users with Query Builder and Auditing Data

Today I received the following request via email:
“Do you have any pointers for me where I can find a lot of useful [Query Builder] queries? One in particular I am looking for is where I can see what is the max concurrent users on our environment by today, week, month and all historical dates…”

Concurrent users is a moving target. It can change from one moment to the next. Recording concurrent users requires defining a regular and precise time interval, and making note of the concurrency at the moment of that interval. If you have followed me closely regarding the strengths and weaknesses of Business Objects’ Query Builder tool then you will know that except for recording a few time stamps for objects there are no historical usage properties available in Query Builder. QB is used to report on the current state of the metadata. It can tell you when a particular report was created and it can tell you when a particular user last logged on to the system, but it can’t give you a more in-depth view into Business Objects system usage.

Query Builder can tell you what the concurrent users are in the present moment, however, it won’t store the result for you. To get this information just run this query in Query Builder while logged in with an admininstrative user.

SELECT
COUNT(si_id)
FROM
CI_SystemObjects
WHERE
si_kind = 'Connection' AND
si_failover_available_until = NULL AND
si_authen_method != 'server-token'


If you wanted to have some kind of historical hourly or daily view then you would need to create some kind of Java app which would run this query regularly and store the result in a database for later retrieval. It should be pointed out that this number likely contains both user interactively logged in and scheduled instances running while using a user’s credentials. Nevertheless, the number for that query above validates well with the data returned by Central Management Console, CMC’s “Sessions” page.

Having said all of this of Query Builder, I feel that I should point out that by using auditing data one may achieve an hourly concurrency number by slightly modifying the meaning of concurrence. Of course, this would require that auditing be enabled (probably at least login, refresh, and edit audit events). What if we were to say that the measure of concurrent users for the hour is defined by how many distinct users used the Business Objects system (in any way) during a one hour period. If this became our definition of concurrency, then we could run the following SQL query against the auditing database:

SELECT
TRUNC(ae.start_timestamp,'HH24'), COUNT( DISTINCT(ae.user_name) )
FROM
Audit_Event ae
WHERE
ae.start_timestamp BETWEEN TO_DATE('01-MAY-2014','DD-MON-YYYY') AND TO_DATE('01-JUN-2014','DD-MON-YYYY')
GROUP BY
TRUNC(ae.start_timestamp,'HH24')
ORDER BY
TRUNC(ae.start_timestamp,'HH24')

Ponder on that one for a little while and you will see that defining concurrent users in this way makes logical sense and it also gives you historical data you can report on now without having to do any Business Objects SDK coding. The output of this query would give you some interesting insight into your system usage, as long as your auditing settings remained constant and functional during the dates queried.

To answer the first question I was asked, “Do you have any pointers for me where I can find a lot of useful [Query Builder] queries” I can say that I do have a nice collection in the Query Builder guide which I wrote and sell on this website. I have quite a few spread out across the many Query Builder articles on this web site. However, I would like to build a web page with most of my favorite Query Builder queries. I’ve wanted this myself when my personal memory and notes have failed me.