- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
One thing I was recently tasked with was programatically importing users from external apps, comparing them to the users we have in the system (which all come from our LDAP anyway) and adding them to groups. The idea is that this way we can more easily see who is affected if we need to have some downtime on a CI. Turns out it's not too difficult. Below are the steps I followed to do this:
1. Create a Data Source from the Administration area of the System Import Sets menu. Ideally, you have the SQL plugin enabled and a MID server running on your infrastructure so that you can simply write an SQL query against the user table for the app in question. That's what I did. This means that you have a data source item that whenever you run it you're getting a fresh vision of the app's users. It's important that you are importing a user id that you will be able to compare to the id of the users in Servicew Now.
2. Go ahead and Load Data from the System Import Sets menu. This will create an Import Set table which you need in order to keep going forward (the process is not terribly intuitive).
3. Create a Transform Map that has your Import Set table as the source and the Group Member [sys_user_grmember] table as the target. In this transform map is where I had to do most of the lifting. Below is the script I put in in order to find the group and the user and the group member tables and add users as needed:
//the user group for the application
var group = "Nexams";
//the user id from the source table import
var uid = source.u_user_id.toString();
//get the system id for the user from service now
var user_sys_id = getUserSysId(uid);
//if no user in service now we stop
if(user_sys_id == null) {
log.error("No user found. No one to add to group. Exiting");
} else {
//get or create the group
var group_sys_id = getGroupSysId(group);
//check if the user us already a member
if(!isMember(group_sys_id,user_sys_id)) {
log.info("Adding user member to group.");
//if user is not a member we create a new record
if(setMember(group_sys_id,user_sys_id)) {
log.info("Done adding member.");
}
}
}
//for each row to process from the import, get the user name, find the sys id from the user table
function getUserSysId(uid) {
var userRecord = new GlideRecord("sys_user");
userRecord.addQuery('user_name', uid);
userRecord.query();
while (userRecord.next()) {
log.info("Found user: " + userRecord.user_name);
return userRecord.sys_id;
}
//no user found in LDAP
log.error("No user found. Check LDAP!");
return null;
}
function getGroupSysId(group) {
var groupRecord = new GlideRecord("sys_user_group");
groupRecord.addQuery('name', group);
groupRecord.query();
while (groupRecord.next()) {
log.info("Found group: " + groupRecord.getDisplayValue());
return groupRecord.sys_id;
}
//group doesn"t exist so create it
log.warn("No existing group found. Creating one.");
groupRecord.initialize();
groupRecord.name = group;
var group_sys_id = groupRecord.insert();
if (group_sys_id != null) {
log.info("Created new group: " + group_sys_id);
return group_sys_id;
} else {
log.error("Failed to create new group!");
return null;
}
}
// now see if the user is in the group table
function isMember(group_sys_id, user_sys_id) {
var groupUserRecord = new GlideRecord("sys_user_grmember");
groupUserRecord.addQuery('group', group_sys_id);
groupUserRecord.addQuery('user', user_sys_id);
groupUserRecord.query();
while (groupUserRecord.next()) {
userInGroup = true;
log.info("Found User as a Group member.");
return true
}
log.warn("User not found as group member.");
return false;
}
//user in group doesn"t exist so create it
function setMember(group_sys_id, user_sys_id) {
var groupUserRecord = new GlideRecord("sys_user_grmember");
groupUserRecord.initialize();
groupUserRecord.group = group_sys_id;
groupUserRecord.user = user_sys_id;
if (groupUserRecord.insert() != null) {
gs.addInfoMessage("Successfully added user as group member.");
return true;
}
gs.addInfoMessage("Failed to add user to group!");
return false;
}
============================================================
4. The final step is to create a Scheduled Import from the same System Import Sets menu. In this Scheduled Import which you can run as often as you like (I chose weekly) you can specify it to run a pre-import script. This is necessary so that you can wipe the group member table clean before you reload it. My script looks like this:
//get the system id of the group and if it doesn"t exist create it
//if it does exist empty it.
var group = "Nexams";
//get or create the group
var group_sys_id = getGroupSysId(group);
if (group_sys_id != null) {
emptyTable(group_sys_id);
}
function getGroupSysId(group) {
var groupRecord = new GlideRecord("sys_user_group");
groupRecord.addQuery('name', group);
groupRecord.query();
while (groupRecord.next()) {
log.info("Found group: " + groupRecord.getDisplayValue());
return groupRecord.sys_id;
}
//group doesn"t exist so create it
log.warn("No existing group found. Creating one.");
groupRecord.initialize();
groupRecord.name = group;
var group_sys_id = groupRecord.insert();
if (group_sys_id != null) {
log.info("Created new group: " + group_sys_id);
return group_sys_id;
} else {
log.error("Failed to create new group!");
return null;
}
}
// now empty the group member table
function emptyTable(group_sys_id) {
var failCount = 0;
var groupUserRecord = new GlideRecord("sys_user_grmember");
groupUserRecord.addQuery('group', group_sys_id);
groupUserRecord.query();
while (groupUserRecord.next()) {
if (!groupUserRecord.deleteRecord()) {
failCount++;
}
}
if (failCount > 0) {
log.warn("Failed to delete " + failCount + " rows.");
}
}
==============================================================
Well that's about it. The next step I plan to do is to associate the group to the application CI. That should be easy. Then it's time to look into the reporting. Cheers!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
