Precisely Enterworks

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
------------------------------

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
------------------------------

Thank you, Brenda.  This has been extremely helpful to capture what is needed.

------------------------------
Jodi Buck
Thomson Reuters | (763) 326-5675
------------------------------

Original Message:
Sent: 03-02-2021 08:11
From: Brenda Maxwell
Subject: User and Group Account Inventory

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
------------------------------