BO CMC query builder

[Former Member]

Hi All,

i am looking for an script to run in QB that would retrive the user's details like username, user DI ,user mail id and also other details like the user's member of(need  know the groups which that user has).

currently i have  one which will say how many users are there in a groups. like wise i am looking for list of groups the user is associated with.

ultimate aim is to look the user's security setup without viewing the users profile in the CMC.

please let me know if you still need any info on this.

Thanks in advance!!

[Former Member · Accepted solution]

Hi,

You can download query Builder from below link:

http://bukhantsov.org/2012/03/query-builder-with-export-to-excel/

later export the user list by using below query in query builder this will result in 2 tabs, under container tab you will find the GroupsID's corresponding to user list that can be used to join SI_ID column from Group list query given below.

User List:

select SI_ID,SI_NAME,SI_EMAIL_ADDRESS,SI_USERFULLNAME,SI_USERGROUPS

from CI_SYSTEMOBJECTS where si_kind='User'

Group List:

select SI_ID,SI_NAME,SI_CHILDREN,SI_CUID,SI_RUID,SI_PARENTID,SI_KIND

from CI_SYSTEMOBJECTS where si_kind='UserGroup'

Regards

Jatinder Singh Bhatti

[Former Member · Accepted solution]

Hi Jatinder,

Thank you for your response.

can we get the details in the below format? like

User name:

User ID:

User in group1: 'Group name the user belong'

User in group2: 'Group name the user belong'

User in group3: 'Group name the user belong'

.

.

.

.

Please let me know if have like this one?

[Former Member · Accepted solution]

Yes you can have.

please follow the steps below.

1.     Download the QueryBuilder from site given.

2.     Launch the application with name QueryBuilder (no need to install the query builder). It will launch           a login form.

3.     Enter User/password for your CMS.

4.     Select table as "ci_infoobjects" enter the query below for user list (refer to Userlist screenshot)

select SI_ID,SI_NAME,SI_EMAIL_ADDRESS,SI_USERFULLNAME,SI_USERGROUPS

from CI_SYSTEMOBJECTS where si_kind='User'

5.     Click on Export to Excel. it will generate a xls file with name quickview which is nothing but user list. Rename it to user.xls

6.     Similarly enter query below for group list

select SI_ID,SI_NAME,SI_CHILDREN,SI_CUID,SI_RUID,SI_PARENTID,SI_KIND

from CI_SYSTEMOBJECTS where si_kind='UserGroup'

7.     Click on Export to Excel. it will generate xls file which is nothing but grouplist (refer to Grouplist screenshot) rename it to group.xls

8. Now open user.xls file and insert a new tab, paste the content of group.xls file in that tab and re-name that tab to groups

9. Now go to tab container, here value column is actually SI_ID of groups tab, so u can lookup for value of user to SI_ID of groups and have value of S_NAME (Group name) attaching screenshot too.

[Former Member · Accepted solution]

Thank you Jatinder this helps. Appriciate you Guide with the screen shot

[Former Member · Accepted solution]

Hi Sasi,
I think you can change the question status to Answered to make this thread beneficiary for others.

[former_member185603]

Check this,

BI40 Users and Groups Lists

If this does not serve your purpose, post it in SDK forum.

[Former Member]

Hi Jawahar thanks. i posted here as i was redirected here. the extarct is not working in my PC.

i tried to run the script of the VBA in the querybilder but it is not reflecting the name instead it is showing the seq i guess.

please guide.

Thanks

Sasi

[former_member182521]

try these two program objects implementation.

[Former Member]

Thanks Manikandan I see the error like Library can not be found when I try to do the extract through Excel as you guided can you please suggest something on this?

Thanks

Sasi