Report of User Details from CMC

[system]

Hi all
Is there anyway of producing a report from the system database, which details account id, name, description, groups, enabled/disabled status?
Many thanks, Sandi

---

SandiR (BOB member since 2004-12-20)

[system]

Have you configured Audit database? If yes, you can trace from there.

---

sunchega (BOB member since 2008-04-23)

[system]

Or check this utility from BOB downloads

BOXI UserList & Group Extraction

Here is a little excel file with a bit of VBA code to extract from a CMS the complete list of user and the list of groups each user is member of. Quite useful when you need to check group membership… It’s now possible to edit the CMS data, simply by modify entries in the Excel File. You need then to export your modification back to the CMS. Modifiables column are : Login, FullName, Email, Disabled and Description. Platform: BO XI R2 / BOXI3.1 (it’s necessary to change the libs to fits XI3.1, s…

.

---

haider (BOB member since 2005-07-18)

[system]

Hi - yes audit is enabled - I will check in the activity universe.
But I had thought that the activity universe only tracked actual activity rather than the base tables such as users and groups.

I know there is a download in the dl section on here, but I am working on customer site and do not have access to the client side tools.

Many thanks

---

SandiR (BOB member since 2004-12-20)

[system]

Auditing does only list activity - so any users who haven’t logged in wouldn’t be listed.

---

David.Mitchell (BOB member since 2008-04-28)

[system]

[quote:778c9bd9a2=“David.Mitchell”]Auditing does only list activity - so any users who haven’t logged in wouldn’t be listed.
[/quote]

This is what I thought.
We are after a complete list of the users currently recorded on the system including all details except password.
I have used the macro’d spreadsheet in the past but only where I had access to the client side tools. It does not work from PCs which have WebI access only it seems.

I was having a look at the Query Builder from the launchpad, however I do not have access to the DDL for the system database to enable me to find out the tables and column names that I need.

Cheers, Sandi

---

SandiR (BOB member since 2004-12-20)

[system]

OK I have managed to get the information out using Query Builder and the following query however I was hoping it would be in a format that I could extract to a spreadsheet.
Cheers, Sandi

SELECT
SI_ID,
SI_NAME,
SI_USERFULLNAME,
SI_DESCRIPTION,
SI_USERGROUPS,
SI_EMAIL_ADDRESS,
SI_FORCE_PASSWORD_CHANGE,
SI_ALIASES,
SI_LASTLOGONTIME,
SI_PASSWORDEXPIRE
FROM
CI_SYSTEMOBJECTS
Where
SI_KIND=‘User’

---

SandiR (BOB member since 2004-12-20)

[system]

Yes, this is correct query for all CMC information and gets the data from Audit table.

---

sunchega (BOB member since 2008-04-23)

[system]

@sunchega

Yes, this is correct query for all CMC information and gets the data from Audit table.

Surely this comes from the system tables and not audit?
You can still get this information even if the Auditing is not switched on.
If it was from the Audit database, you would have been able to write a query against the Activity universe.

---

Curvy (BOB member since 2008-09-02)