Querying Users of all Groups

system · January 10, 2019, 4:10am

I trying to query users of all groups in business objects.

SELECT *
FROM ci_systemobjects
WHERE descendants(“si_name=‘Usergroup-User’”, “si_name=’’”)

The above query will return users of one group. But, I want users of all groups with group name in the below format.

User_id group_name

Thanks and Regards,

Diwakar G

diwakarg029 (BOB member since 2019-01-10)

system · January 10, 2019, 9:30am

Welcome to B:bob:B! You don’t mention what version you are using…there is a CMS universe with the mosr recent version, which would be easier than using Query Builder.

Nick Daniels (BOB member since 2002-08-15)

system · January 10, 2019, 9:32am

I am using CMS query builder version 1.3.

diwakarg029 (BOB member since 2019-01-10)

system · January 10, 2019, 9:34am

What version of Business Objects?

Nick Daniels (BOB member since 2002-08-15)

system · January 10, 2019, 9:57am

BO 4.1.

I want result in below format.

Id Login Name Email Groups Created_date

Please provide query for this?

diwakarg029 (BOB member since 2019-01-10)

system · January 10, 2019, 1:20pm

The CMS query language is SQL-ish, but does not support joins which would be necessary to produce your desired result in a single query.

This will require two steps:

select si_id,si_name,si_userfullname,si_creation_time,si_email_address,si_usergroups from ci_systemobjects where  si_kind = 'user'

This will produce a list of all users. Next, get a list of all groups:

select si_id,si_name from ci_systemobjects wehre si_kind = 'usergroup'

You’ll then need to cross-reference the values in the SI_USERGROUPS property from the first step with the SI_ID values in the second. This will give you a list of the groups that each user is a member of.

Joe

joepeters (BOB member since 2002-08-29)