Report - where licenses are innstalled

janw71

Hello,

I'm trying to prepare an report consisting of license list and on what server the licenses are installed.
Did anybody already report like above? Do you have any clue how the query should look like?

BR
Jan

vqh

Hello Jan,

I build a small query example for you where you can retrieve all installed licenses for the object type "Server".
You can create a report via SQL-Editor and paste this query into the textarea.

Query:

SELECT
obj_main.isys_obj__id AS 'id',
obj_main.isys_obj__title AS 'LC__UNIVERSAL__TITLE###1',
j2.isys_obj_type__title AS 'LC__REPORT__FORM__OBJECT_TYPE###1',
j10.isys_obj__title AS 'LC__UNIVERSAL__TITLE#LC__CMDB__CATG__APPLICATION_OBJ_APPLICATION###1',
j11.isys_obj_type__title AS 'LC__REPORT__FORM__OBJECT_TYPE#LC__CMDB__CATG__APPLICATION_OBJ_APPLICATION###1',
j13.isys_obj__title AS 'LC__UNIVERSAL__TITLE#LC__CMDB__CATG__LIC_ASSIGN__LICENSE###1',
j14.isys_obj_type__title AS 'LC__REPORT__FORM__OBJECT_TYPE#LC__CMDB__CATG__LIC_ASSIGN__LICENSE###1',
j6.isys_cats_lic_list__key AS 'LC__CMDB__CATS__LICENCE_KEY#LC__CMDB__CATG__LIC_ASSIGN__LICENSE###26'

FROM isys_obj AS obj_main
INNER JOIN isys_cmdb_status AS obj_main_status ON obj_main_status.isys_cmdb_status__id = obj_main.isys_obj__isys_cmdb_status__id
INNER JOIN isys_obj_type AS j2 ON j2.isys_obj_type__id = obj_main.isys_obj__isys_obj_type__id
INNER JOIN isys_catg_application_list AS j4 ON j4.isys_catg_application_list__isys_obj__id = obj_main.isys_obj__id
INNER JOIN isys_connection AS j3 ON j3.isys_connection__id = j4.isys_catg_application_list__isys_connection__id
INNER JOIN isys_obj AS job3 ON j3.isys_connection__isys_obj__id = job3.isys_obj__id
INNER JOIN isys_cats_lic_list AS j6 ON j6.isys_cats_lic_list__id = j4.isys_catg_application_list__isys_cats_lic_list__id
INNER JOIN isys_obj AS j8 ON j8.isys_obj__id = j6.isys_cats_lic_list__isys_obj__id
INNER JOIN isys_obj AS j10 ON j10.isys_obj__id = job3.isys_obj__id
INNER JOIN isys_obj_type AS j11 ON j11.isys_obj_type__id = j10.isys_obj__isys_obj_type__id
INNER JOIN isys_obj AS j13 ON j13.isys_obj__id = j6.isys_cats_lic_list__isys_obj__id
INNER JOIN isys_obj_type AS j14 ON j14.isys_obj_type__id = j13.isys_obj__isys_obj_type__id

WHERE TRUE

AND ( (j2.isys_obj_type__id != '60' ) AND (j2.isys_obj_type__id != '60' ) ) AND ( (j8.isys_obj__id != '' AND j8.isys_obj__id IS NOT NULL ) AND (j2.isys_obj_type__id = '5' ) ) ORDER BY obj_main.isys_obj__title ASC ;

I had to modify the query a little because it showed me all keys of the assigned license. I hope that this report is helpful for you.

best regards
Quyen

janw71

Sorry for late answer.
Your query helped a lot. Thanks.
Jan