If you are an SAP BusinessObjects BI Platform Administrator and looking to find out Empty Groups, or List Sub Groups and Users inside groups, following are the queries which will help in that aspect.
To list the top level Groups, which are at the root level in Group Hierarchy:
SELECT si_id, si_cuid, si_name, si_group_members, si_subgroups, si_usergroups FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup' and si_usergroups is null
To list the Groups where there are no users and no subgroups, use the following query:
SELECT si_id, si_cuid, si_name, si_group_members, si_subgroups, si_usergroups FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup' and si_subgroups is null and si_group_members is null
To list the Groups where there are no subgroups, use the following query:
SELECT si_id, si_cuid, si_name, si_group_members, si_subgroups, si_usergroups FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup' and si_subgroups is null
To list the Groups where there are no users, use the following query:
SELECT si_id, si_cuid, si_name, si_group_members, si_subgroups, si_usergroups FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup' and si_group_members is null
And here is JAVA SDK code sample, which you can refer and fine tune according to your needs. It will
recursively scans through all the groups and its subgroups to print the number users and subgroups inside each.
package com.sap.businessobjects.bi.samples;
import java.util.Iterator;
import java.util.Set;
import com.businessobjects.bcm.BCM;
import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;
import com.crystaldecisions.sdk.plugin.desktop.usergroup.IUserGroup;
public class listOfEmptyGroups
{
public static void main (String[] agrs)
{
String cmsname = "localhost:6400";
String username = "administrator";
String password = "Password1";
int mainFolderCounter = 1;
IEnterpriseSession boSession = null;
ISessionMgr lSessionMgr = null;
IInfoStore infoStore = null;
IInfoObjects boUserGroups = null;
IInfoObject boUserGroup = null;
IUserGroup userGroup = null;
try
{
BCM.initializeSAPJCE();
lSessionMgr = CrystalEnterprise.getSessionMgr();
System.out.println("LIST OF USERS OF EACH GROUP: ");
System.out.println();
boSession = lSessionMgr.logon(username, password, cmsname, "secEnterprise");
infoStore = (IInfoStore)boSession.getService("", "InfoStore");
boUserGroups = infoStore.query("SELECT si_id, si_cuid, si_name, si_group_members, si_subgroups FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup' and si_usergroups is null");
if(boUserGroups.size() > 0)
{
for(Iterator<?> igroup = boUserGroups.iterator() ; igroup.hasNext() ; )
{
boUserGroup = (IInfoObject) igroup.next();
userGroup = (IUserGroup) boUserGroup;
recurGroup(userGroup, infoStore, mainFolderCounter);
mainFolderCounter++;
}
}
}
catch (SDKException e)
{
e.printStackTrace();
}
finally
{
if (boSession!= null)
{
boSession.logoff();
}
}
}
public static void recurGroup(IUserGroup rUserGroup, IInfoStore iStore, int y) throws SDKException
{
Set<Integer> setUsers;
Set<Integer> subGroups;
Object userID, groupID = null;
IInfoObjects users, sGroups = null;
IInfoObject user, sGroup = null;
IUserGroup rGroup = null;
int x = 1;
int z = 1;
setUsers = rUserGroup.getUsers();
if (setUsers.size() >= 1)
{
System.out.println(" "+y+" - User Group '" + rUserGroup.getTitle() + "' has " + setUsers.size() + " users, and they are: ");
for(Iterator<?> iUser = setUsers.iterator() ; iUser.hasNext() ; )
{
userID = iUser.next();
users = iStore.query("SELECT SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' AND SI_ID = '" + userID + "'");
user = (IInfoObject) users.get(0);
System.out.println(" "+ x + " - " + user.getTitle());
x++;
}
System.out.println();
}
else
{
System.out.println(" "+y+" - User Group '" + rUserGroup.getTitle() + "' has " + setUsers.size() + " users.");
System.out.println();
}
subGroups = rUserGroup.getSubGroups();
if (subGroups.size() > 0)
{
System.out.println(" "+y+" - User Group '" + rUserGroup.getTitle() + "' has " + subGroups.size() + " Sub Groups, and they are: ");
for(Iterator<?> subGroup = subGroups.iterator(); subGroup.hasNext();)
{
groupID = subGroup.next();
sGroups = iStore.query("select si_name from ci_systemobjects where si_kind = 'UserGroup' and si_id = '" + groupID + "'");
sGroup = (IInfoObject) sGroups.get(0);
System.out.println(" "+ z + " - " + sGroup.getTitle());
z++;
}
System.out.println();
subGroups = rUserGroup.getSubGroups();
Iterator<?> subGrp = subGroups.iterator();
for(int g = 0; g < subGroups.size(); g++)
{
groupID = subGrp.next();
sGroups = iStore.query("select si_id, si_cuid, si_name, si_group_members, si_subgroups from ci_systemobjects where si_kind = 'UserGroup' and si_id = '" + groupID + "'");
sGroup = (IInfoObject) sGroups.get(0);
rGroup = (IUserGroup) sGroup;
recurGroup(rGroup, iStore, y);
}
}
else
{
System.out.println(" "+y+" - User Group '" + rUserGroup.getTitle() + "' has " + subGroups.size() + " Sub Groups.");
System.out.println();
}
}
}
Hope you find it useful.
Best regards, Venkat.
