Fetching BO users list along with their associated user groups in BO 4.2 SP5

Abstract:

This post will provide a simple overview about fetching the users list along with their associated User groups from BO4.2 SP 5

Details:

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.

Query :

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

Thanks to Matthew Shaw for his wonderful blog from where I got this little idea and Bharath B N  for pointing me to the right direction.

Conclusion:

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.

Regards,

Kamalessh.