Hi Jodi,
Try running the SQL below and see if that gets what you need. The last column should be a concatenation of all the groups assigned to a user account.
Hope this helps!
Brenda
use EPIM;
select distinct
u.USER_ID as [User Id]
,u.LOGIN as [User]
,u.FIRST_NAME as [First Name]
,u.LAST_NAME as [Last name]
,u.EMAIL as [E-mail address]
,u.IMPERSONATE_IND
,u.DESCRIPTION
,u.SECURITY_CONTEXT_VALUE as [Security Context]
,substring(
(
Select ','+g.NAME AS [text()]
From [B_GROUP_USER] gu
join [B_GROUP] g on g.GROUP_ID = gu.GROUP_ID
where gu.USER_ID = u.USER_ID
ORDER BY g.NAME
For XML PATH ('')
), 2, 1000) [Groups]
from B_USER u
order by 4;
------------------------------
Brenda Maxwell | Director Global Product Management
IDEA | 6787788921
------------------------------
Original Message:
Sent: 02-26-2021 09:24
From: Jodi Buck
Subject: User and Group Account Inventory
Hello,
I have read about the methods for obtaining user login history data and am interested in learning whether there is currently a way to generate an inventory report of all application accounts and group assignments from EnterWorks.
We are required to provide this for our audit control procedures. According to the controls, the inventory should be system-produced from the application. The inventory should include account name, account status, account type/access level, account description, account creation date, and account owner.
Regards,
------------------------------
Jodi Buck
Thomson Reuters | (763) 326-5675
------------------------------