Skip to Content
Technical Articles
Author's profile photo Kamalesshwaren Thangarajan

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.

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Peter von Schubert
      Peter von Schubert

      Hello, unfortunatly the list returns multiple assignments of user-usergroup although user does not have to much assigned groups.

      E.g. user 01ABCDE has 5 assigned user groups but return list displays 19(!) assigned groups. That is not the actual situation!

      How could I got a list of users with their actual assigned groups correctly?

      Regards,

      Peter

      Author's profile photo Kamiya Vishwakarma
      Kamiya Vishwakarma

      For your requirement, auditing will also have record s of the associations in between the user-usergroup from the past, that doesn’t exist anymore.

      The correct way is to get this detail from CMS DB.

      you may use KB 2369779 – How to unlock the CMS database with new data access driver for BI 4.2 SP3 [VIDEO]

      Using the given KB, you can query the CMS DB to get the correct results.

       

      Regards,

      Kamiya