How to Sync Cognos Groups with a Database/Excel

Do you have a need to synchronize IBM Cognos Groups with a datasource?

MetaManager’s™ Script Runner module can do this in only a couple lines of JavaScript. Best of all the since the code is not compiled into an SDK application you can easily update it when your requirements change.

This script is designed to synchronize IBM Cognos Groups with information stored in a database or Excel file.  The script will create a namespace folder (if needed) in the Cognos Namespace to store the groups.  It will then create each of the groups (if needed) and then synchronize the members with the information in the datasource. It does this by clearing the existing members then re-adding the members listed in the datasource.

The information in the datasource contains 2 columns, the first is the Name of the group and the second is the Account’s username.  To add a member to a group the script will need the user’s CAMID, however it’s unlikely that you have this external to Cognos.  So the script assumes you have the username and then looks up the user with that information. This can easily be changed to an email address or other information.

Attached is a copy of the script file, The excel file and the create script for the database.  To run this in your environment you will need to do the following:

  1. Open the script and update the database information (if using the database mode)
  2. Change useDatabase to true or false depending on your datasource
  3. Change the folder name (folderName) to the location where you want to store the groups
  4. Change the namespace to the CAMID of your security namespace
  5. Update the usernames in the Excel File or Database to use usernames that exist in your environment.

The script uses the Active MetaManager™ connection so that we don’t need to store connection or credentials in the script.  If you want to run this script in Command Line mode (e.g. schedule the script) a minor change can be made to use api.createServer() instead of api.getCurrentServer() to automate the logon process. Be sure when you run the script you are connected to the correct environment.

Excerpt of the script:
	var rows;
	
	// 1. Get the data from the datasource to process
	if(useDatabase)
	{
		var sql = "SELECT GroupName, UserName FROM Memberships";
		rows = helpers.database.readSqlServerTable(dbserver, dbname, dbusername, dbpassword);
	}
	else
	{
		var fileName = "memberships.xlsx";
		var rows = helpers.file.readExcelFile(fileName, "Memberships");
	}
	console.log("Read Data: " + rows.length + " rows");

	
	// 2. Get a unique list of the groups to process
	var groups = [];
	for( var i = 0; i < rows.length; i++ )
	{
		var row = rows[i];
		var groupName = row[0];
		
		// Add the group to the list if it's not already in there
		if(!contains(groups, groupName)) 
		{
			groups.push(groupName);
			console.log("Found Group: " + groupName);
		}
	}
	console.log(groups.length + " Unique groups to process");
	console.log(" ");
	
	// 3. Log onto IBM Cognos
	var server = api.getCurrentServer(); // Note. Change this to createServer for commandline mode
	var currentUser = server.getCurrentUser();
	console.log("Server: " + server.url);
	console.log("Logged On As: " + currentUser.name);
	
	
	// 4. Create the namespace folder if needed
	var folder = server.getCMObject("CAMID(":")/namespaceFolder[@name='" + folderName + "']")
	if(folder == null)
	{
		folder = server.factory.createNamespaceFolder("CAMID(":")", folderName);
		console.log("Created Folder: " + folder.name);
	}
	console.log(" ");
	
	// Process each of the groups
	for( var g = 0; g < groups.length; g++ )
	{
		var groupName = groups[g];	
		console.log("Processing: " + groupName);

		
		// 5. Create the group if it doesn't exist
		var group = server.getCMObject(folder.searchPath + "/group[@name='" + groupName + "']")
		if(group == null)
		{
			group = server.factory.createGroup(folder.searchPath, groupName);
			console.log("  Created Group: " + group.name);
		}
		
		
		// 6. Remove all of the members from the group
		var members = group.securityFns.getMembers();
		for( var i = 0; i < members.length; i++ )
		{
			var member = members[i];
			group.securityFns.removeMember(member);
		}
	
		// 7. Re-add the users back into the group
		// Loop through all of the records and add the users
		// that are mapped into the current group
		for( var i = 0; i < rows.length; i++ )
		{	
			var row = rows[i];
			var rowGroupName = row[0];
			var rowUserName = row[1];
			
			if(groupName == rowGroupName)
			{
				// Look up the user by username.  You can change this to email or something else
				var user = server.getCMObject(namespace + "//account[@userName='" + rowUserName + "']")
				if(user == null)
				{
					console.error("  Error: Unable to find " + rowUserName + " in " + namespace)
					continue;
				}
				
				// Add the user's searchPath to the list of group members
				group.securityFns.addMember(user.searchPath);
				
				console.log("  Added " + user.name + " to " + group.name)
			}
		}
		
		// 8. Save the updates to the group
		group.securityFns.saveMembers();
		console.log(" ");
	}
                
Create Statement for the database: CREATE TABLE [dbo].[Memberships]( [ID] [int] IDENTITY(1,1) NOT NULL, [GroupName] [varchar](50) NOT NULL, [UserName] [varchar](50) NOT NULL, CONSTRAINT [PK_Membership] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]<

Happy Automating!

-Andy