Retrieving Quality Center user login statistics

In the Quality Center Site Administration console there is a Site Analysis tab which gives you a break-down of the QC license usage over time. This is useful if you want to understand the overall usage patterns of Quality Center but it does not give detailed information about the individual users or their login statistics (such as the projects each user has logged in to, total login count or individual login times).
This information is recorded but to get it we need to dig around in the QC Site Admin database. The table that contains this information is called SESSIONS_HISTORY and contains the following fields:
- Session ID (int) - Unique ID (Primary key).
- Start_Time (DateTime) - Date and time that the user logged in.
- End_Time (DateTime) - Date and time that the user logged out. If null it means the session is active.
- Domain_Name (Varchar 255) - Domain of the project which the user logged in to.
- Project Name (Varchar 255) – Project that the user logged in to.
- User_Name (Varchar 60) - User name.
- Client_Type (Varchar 50) - Client type (either a normal user or a QTP test execution client).
A simple query to extract session information from this table from a given date, would be:
You may want to know when the last login was for each user, together with the total number of times that user logged in to Quality Center since a given date, ordered by user. To get this information your query should look like this:
It would be very useful if we could, for example, have each user’s email address included in this report. All user-related information is contained in the USERS table, so to do this we need to join the USERS table with the SESSIONS_HISTORY table. The query will now look like this:
You may not always have direct access to the database through a query tool such as Query Analyzer. In that case you can use the Quality Center Site Administration console. Open the console and expand the project tree until you are able to select a table. When you select a table an edit box will appear where you can paste a query. Click the Execute SQL button to execute the query.
The query above will give an error when executed in this way because the tables that we refer to in the query reside in the QC Site Admin database and not the project database that we are connected to. To get around this problem you need to prefix the Site Admin database before each table referenced in the query, like this:
We can now see who has logged in to Quality Center since a given date, as well as the projects that they logged in to, the last date and time that they logged in to each project, the total number of logins to each project since the given date and their email address.
Tech tips from JDS

Browser Console
Read More

Glide Variables
Read More

Understanding Database Indexes in ServiceNow
Read More

Fast-track ServiceNow upgrades with Automated Testing Framework (ATF)
Read More

ServiceNow Catalog Client Scripts: G_Form Clear Values
Read More

Is DevPerfOps a thing?
Read More

The benefits of performance testing with LoadRunner
Read More

Monitoring Atlassian Suite with AppDynamics
Read More

5 quick tips for customising your SAP data in Splunk
Read More

How to maintain versatility throughout your SAP lifecycle
Read More

How to revitalise your performance testing in SAP
Read More

Reserve and import data through Micro Focus ALM
Read More

How to effectively manage your CMDB in ServiceNow
Read More

ServiceNow and single sign-on
Read More

How to customise the ServiceNow Service Portal
Read More

Integrating a hand-signed signature to an Incident Form in ServiceNow
Read More

Integrating OMi (Operations Manager i) with ServiceNow
Read More

Implementing an electronic signature in ALM
Read More

Service portal simplicity
Read More

Learning from real-world cloud security crises
Read More

Static Variables and Pointers in ServiceNow
Read More

Citrix and web client engagement on an Enterprise system
Read More

Understanding outbound web services in ServiceNow
Read More

How to solve SSL 3 recording issues in HPE VuGen
Read More

How to record Angular JS Single Page Applications (SPA)
Read More

Calculating Pacing for Performance Tests
Read More

Vugen and GitHub Integration
Read More

Filtered Reference Fields in ServiceNow
Read More

ServiceNow performance testing tips
Read More

Monitor Dell Foglight Topology Churn with Splunk
Read More

Straight-Through Processing with ServiceNow
Read More

Splunk: Using Regex to Simplify Your Data
Read More

ServiceNow Choice List Dependencies
Read More

Tips for replaying RDP VuGen scripts in BSM or LoadRunner
Read More

Incorporating iSPI metric reports into MyBSM dashboard pages
Read More

Using SV contexts to simulate stored data
Read More

Recycle Bin for Quality Center
Read More

Agile Performance Tuning with HP Diagnostics
Read More

Understanding LoadRunner Virtual User Days (VUDs)
Read More

Problems recording HTTPS with VuGen
Read More

Generating custom reports with Quality Center OTA using Python
Read More

Asynchronous Communication: Scripting For Cognos
Read More

How to fix common VuGen recording problems
Read More

URL Attachments in Quality Center
Read More

Restore old License Usage stats after upgrading Quality Center
Read More

Changing LoadRunner/VuGen log options at runtime
Read More

Restricting large attachments in Quality Center
Read More

Retrieving Quality Center user login statistics
Read More

A comparison of open source load testing tools
...
Read More

Worst practices in performance testing
Read More