Skip to Content

Query builder + Java SDK: Find users part of “Everyone”  User Group and preferred User Account Type like “Enterprise”, “LDAP” or “Windows AD”

 

How to retrieve user list for a particular authentication account like AD, LDAP or Enterprise – a question on the web roaming as there is no direct way to get the user list through Query Builder.

We can work with SDK API and get the desired list of user by providing required set of parameter for filtering. I did some work where I need to find list of user belongs to particular user group and having only either Enterprise or LDAP account as well as user last log on  date greater than a given date. So the criteria are:

  1. User belongs to a particular User group
  2. Particular account type ( Enterprise /Windows AD / LADP)
  3. User last log on time > given date

Problem statement:

Retrieving user list from a particular user group with a given user account type (Enterprise/ AD/ LDAP) as BUSINESS OBJECTS Query Builder does not have a direct SQL to get back the list

Solution:

  We can have a java process using BUSINESS OBJECTS  SDK API’s connecting to CMS and retrieving the user list from a given group- a simplest part but question lies on the user account type – a particular directory user.  We need retrieve only selective user of a given account type. The simple query builder cannot return the desired result only because the required data exists as an object mapped to “SI_ALIASES” parameter. Once we retrieve through query builder, we need to iterate through the object and check only selective active directory. On match with the account type, build the list and write up in a file

How to Implement: 

Enterprise session :

Get BUSINESS OBJECTS Enterprise session to by providing admin credentials , once we have the session, we need to have IInfoStore object to run the query

// sample  IInfoStore infoStore = (IInfoStore) enterpriseSession.getService(“”, “InfoStore”);

Parameter to pass:

User last log on time as given format and user group “Everyone” passed into the query directly

 

// sample

String lastLogonTime = “20010.02.21.00.00.01”;

Iteration:

There may be more than 50k users in your server and you try to retrieve all users in single shot, you may get time out error while retrieving data set from CMS, so it’s better iterate through small numbers and build a list of match result

So, we take a simple loop of 100 iteration and build a query to run by passing required business parameters and on top of that we are adding iteration parameter. The iteration parameter is nothing but the SI_ID of users and retrieving user list in ascending order greater than the last selected SI_ID in last iteration

 

// sample

int min_id = 0; // iteration id

for(int count = 0 ; count < 100 ; count++) {

// looping to get all users list without time out error

// query having given user group, last log on time

String queryUserList =

“SELECT TOP 500 SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS,SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE DESCENDANTS(\”SI_NAME=’USERGROUP-USER’\”, \”SI_NAME=’Everyone’\”) And SI_ID > ” + min_id + ” AND (SI_LASTLOGONTIME > ‘”+ lastLogonTime +”‘ or SI_LASTLOGONTIME is null) ORDER BY SI_ID ASC”;

min_id = oInfoObject.getID(); // SI_ID – assigning the si_id for the next run

// SI_ALIASES – the parameter which contains the user account type

}

Execution :

once we process the query, we get the IInfoObjects

// sample                          

IInfoObjects oInfoObjects = (IInfoObjects)infoStore.query (queryUserList);

// iteration on IInfoObjects to get single user IInfoObject

IUser user = null;

//Execute the query

for (int i=0;i<oInfoObjects.size();i++){

oInfoObject=((IInfoObject)oInfoObjects.get(i));

user = (IUser) oInfoObjects.get(i);

}

SI_ALIASES:    

  Dealing with aliases is very crucial, once we have IUser object, we need to retrieve IUserAliases which returns user account type associated with an user

// retrieving user alias info user

IUserAliases boAliases = user.getAliases();

Filtering on user account type:

  We’re almost at the end to get user account. Once we have IUserAliases, we can iterate through and get the user type like following way

As we need to build a list to write into file, we create normal class to assign the result , UserType a class of two attributes –

  1. a) userType
  2. b) userId

 // build a list

List < UserType > userList = new ArrayList<UserType>();

Iterator<IUserAlias> itor = boAliases.iterator();

while (itor.hasNext()) {

UserType usertp = new UserType();

IUserAlias element = itor.next();

usertp.setUserType(element.getAuthentication()); //returns user account type

usertp.setUserId(user.getTitle()); // return user Id

userList.add(usertp); // assigning the result to the list

}

 

// finding LDAP user account type only, for enterprise, you can user secEnterprise

if(userList.get(0).getUserType().equalsIgnoreCase(“secLDAP”)){

userEnterprizeList.add(userList.get(0));

System.out.println(userList.get(0).getUserId());

}

Writing in a file:

Once you have ArrayList of required object, we can easily write the same into a file using StringBuffer like follows: 

 

  1.  

StringBuffer outputStr = new StringBuffer(“User name “);

for( UserType useDtls : userEnterprizeList)

outputStr.append(String.format(“%n%s”,

useDtls.getUserId()

));

Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(“\\UserList.csv”), “utf-8”));

writer.write(outputStr.toString());

 

Please let me know if you need anything in details.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Joe Peters

    If you want to retrieve users who have only a single alias of a particular kind, you can do it with a CMS query:

    select top 10 si_name,si_aliases 
      from ci_systemobjects 
     where si_kind = 'user' 
           and si_aliases.si_total = 1 
           and "si_aliases.1.si_id" like 'secwinad%'
    (0) 
    1. Abhijit Das Post author

       

      Thank you for sharing the idea.  The plan is to find the user id’s and delete those in a batch. I didn’t have a single query to find user with given criteria. Now, once we get the user list, then we delete the same through Java process in a batch mode.  We may have more than 5000 user to delete in whole batch.

      (0) 

Leave a Reply