Automate process of collecting ALM license information

[ihpv02oer]

Hi

I want to be able to automate the process of collecting how many licenses are used in ALM. I know I can get the license usage from Site Analysis and export to excel.  And I have been told that setting logmode to debug should make the SQL query be logged.

But when using the SQL query that is generated when checking site analysis and compare it to what is printed in site analysis it doesnt show the same numbers. So my question is how can I automate collecting daily license usage?

[JeremyLeon]

Hello ihpv02oer,

The option offered by ALM is the Site Analysis tab, there you can filter and export the information desired.

You could collet this by OTA API being the same as the ALM feature but developed by you.

I suggest you opening a support ticket to report the behavior you are facing.

Jeremy León Fernández
HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
(Posts and opinions made here are my own and do not reflect the opinions of my employer HP in any way)

[Lanzas]

QC stores the currently used license sits in the SESSION_LICENSE table of the SA schema. For every open QC session the server adds one record for each of the license types consumed (i.e. exposed by the client to the user). The allocated license sits in a given QC session are de-allocated when the user's session is closed, i.e. all its corresponding records in the table are deleted. QC also stores the license usage history in the table SESSION_LICENSE_HISTORY, which is declared exactly the same as the SESSION_LICENSE table. Records are inserted into the table in the same manner as in SESSION_LICENSE table, but are never deleted from it. The same applies for SESSIONS_HISTORY which holds QC sessions history. A record is created there when a record is removed from PROJECT_SESSIONS. From those two tables (SESSIONS_HISTORY and SESSION_LICENSE_HISTORY) QC is able to display the license history in Site Analysis. Based on the above information, your DBA should be able to compose SQL queries to create the query. 

I have found  a sample that should help in SQL 

declare @start_time as datetime

declare @end_time as datetime

set @start_time ='2014-07-15 17:00:00'

set @end_time ='2014-07-15 18:00:00'

declare @ADD_BUGS as int = 100, @ADD_TESTS as int = 101, @ADD_REQS as int = 102, @QC_FULL as int = 103, @SAP_SPI_FULL as int = 402

select  count  (   distinct (  SLH_MUID ))  

from td.SESSION_LICENSE_HISTORY slh

where

  SLH_LICENSE_TYPE in (@ADD_BUGS  ,   @ADD_TESTS,   @ADD_REQS ,   @QC_FULL)  

   and slh.  SLH_SESSION_ID in   (

                select   SESSION_ID

                                 from td.  sessions_history sh

                                 where (  sh  .END_TIME is NULL or sh.  END_TIME >   @start_time)   and   (  sh  .START_TIME is NULL or sh.  START_TIME <=   @end_time)   -- time condtion                                     

                 )

regards.