Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
kamalessh
Explorer
7,707
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.
2 Comments
pvschu
Explorer
0 Kudos
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
0 Kudos

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

Labels in this area