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?

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.

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?

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

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.

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?

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?

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

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?

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

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

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?

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

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?