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
------------------------------
Original Message:
Sent: Mon June 26, 2023 10:29 AM
From: Edward Posada
Subject: Pulling ILMT user/role data using SQL query from TEMADB database.
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
Original Message:
Sent: Mon June 26, 2023 05:53 AM
From: Michael Köster
Subject: Pulling ILMT user/role data using SQL query from TEMADB database.
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
Original Message:
Sent: Thu June 22, 2023 03:00 PM
From: Edward Posada
Subject: Pulling ILMT user/role data using SQL query from TEMADB database.
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
------------------------------