IBM License Metric Tool (ILMT)

IBM License Metric Tool (ILMT)

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
  • 1.  Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Fri June 23, 2023 03:08 AM

    Hoping I can gain some traction here, as the general support center stated they do not support custom SQL queries.

    I am trying to query the TEMADB database and pull a list of ILMT users and their roles, as I am being tasked by the access management team at my company to do so daily via JDBC connection.  My internal DB team stated they cannot help due to not knowing the physical model and mapping of the database, so I was wondering if someone here would know how to go about doing that?



    ------------------------------
    Ed
    ------------------------------


  • 2.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Fri June 23, 2023 03:35 AM

    Hi,

    As my colleague suggested, the data can be retrieved only through REST API, not directly from the database:

    https://www.ibm.com/docs/en/license-metric-tool?topic=tool-integrating-rest-api

    Thank you.



    ------------------------------
    Andrei Ionescu
    IBM
    Bucharest
    ------------------------------



  • 3.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Mon June 26, 2023 01:59 AM

    Hi Andrei,

    as far as I know, there are no API calls to get user and roll information.

    The question is why Ed like to send this query every day?

    Normally you will set up a company approval process to add new users to the system.Since ILMT is supporting LDAP groups these users can be added to a specific group which can be mapped to an ILMT role.

    Michael



    ------------------------------
    Michael Koester
    IBM License Management Consultant
    ARS Computer und Consulting GmbH
    Munich, Germany
    +49 89 32468 0
    ------------------------------



  • 4.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Mon June 26, 2023 04:20 AM

    Hello,

    It's correct, there are no REST API to get user information.
    I can provide a dedicated SQL query to fetch user&roles but one must be aware that ILMT database schema is a subject to change without notice, so, we can't guarantee that such SQL query will work in the future...



    ------------------------------
    Thank you,
    Oktawian

    Oktawian Powązka, L3 Support
    IBM License Metric Tool
    ------------------------------



  • 5.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Mon June 26, 2023 05:54 AM

    Hi Edward,

    below you find my MS SQL snippet getting the information out of temadb.

    ------

    select username, name, deleted
    from dbo.users u
      inner join dbo.roles_users ru
            on u.id=ru.user_id
      inner join dbo.roles r
            on r.id=ru.role_id

    ------

    As Octavian mentioned be aware that if there is a change on the related temadb schema. Nobyby will guarantee that thi will work on the next release.

    Michael



    ------------------------------
    Michael Koester
    IBM License Management Consultant
    ARS Computer und Consulting GmbH
    Munich, Germany
    +49 89 32468 0
    ------------------------------



  • 6.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Mon June 26, 2023 10:29 AM

    Thanks Michael...this works, but it's also pulling in outdated entries such as deleted users.  Is there a way to filter those out?



    ------------------------------
    Edward Posada
    ------------------------------



  • 7.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Tue June 27, 2023 12:33 AM

    Hi Ed,

    this statment is more exact regarding the seletct database (temadb) and will filter the active users (where clause) 

    as well as sort the results depending an the user name (order by clause) 

    -----

    select username, name
    from temadb.dbo.users u
      inner join temadb.dbo.roles_users ru on u.id=ru.user_id
      inner join temadb.dbo.roles r  on r.id=ru.role_id
    where u.deleted=0
    order by u.username 

    -----

    Michael



    ------------------------------
    Michael Koester
    IBM License Management Consultant
    ARS Computer und Consulting GmbH
    Munich, Germany
    +49 89 32468 0
    ------------------------------



  • 8.  RE: Pulling ILMT user/role data using SQL query from TEMADB database.

    Posted Thu June 29, 2023 05:23 PM

    Thank you for the help Michael!  I will pass this along to the access management group with the note about it being subject to change if the database architecture ever changes in a future release.



    ------------------------------
    Edward Posada
    ------------------------------