This post will provide a simple overview about fetching the users list along with their associated User groups from BO4.2 SP 5
Now, from BO 4.2 SP 5 onward, we can fetch the users list along with their associated user groups from Audit Universe also.
I know that we can easily fetch these details from Query builder, but having these details in a webi report will be very handy for auditing purpose.
I have shared the report query, which will fetch all the active users and their associated user groups.
Note : The user has to be logged into the system at least once. So, this will be captured as an event and the user & their associated user groups detail will get recorded in the Auditing DB.
If there is a change in the user group for a user, then the change will get updated in the DB once the user logs into BO again and we will be having the updated Users list in the Webi report.
SELECT ADS_EVENT.USER_NAME, ADS_EVENT_DETAIL_TYPE_STR.EVENT_DETAIL_TYPE_NAME, ADS_EVENT_DETAIL_V2.EVENT_DETAIL_VALUE2 FROM ADS_EVENT, ( SELECT ADS_EVENT_DETAIL."EVENT_ID", ADS_EVENT_DETAIL."EVENT_DETAIL_ID", ADS_EVENT_DETAIL."EVENT_DETAIL_TYPE_ID", ADS_EVENT_DETAIL."BUNCH", CAST("ADS_EVENT_DETAIL".Event_Detail_Value AS VARCHAR(256)) AS Event_Detail_Value2 FROM ADS_EVENT_DETAIL ) ADS_EVENT_DETAIL_V2, ADS_EVENT_DETAIL_TYPE_STR WHERE ( ADS_EVENT.EVENT_ID=ADS_EVENT_DETAIL_V2.EVENT_ID ) AND ( ADS_EVENT_DETAIL_V2.EVENT_DETAIL_TYPE_ID=ADS_EVENT_DETAIL_TYPE_STR.EVENT_DETAIL_TYPE_ID ) AND ( ADS_EVENT_DETAIL_TYPE_STR.LANGUAGE = @Prompt(Select language) ) AND ADS_EVENT_DETAIL_TYPE_STR.EVENT_DETAIL_TYPE_NAME IN ( 'User Group Name' )
With more options still available in the Audit DB like this, we have to deep dive in to get more things out of the Audit Universe which can really reduce work of BO Admin.