Batch add Data Sources, Transform Maps and Scheduled Imports

[yjaques]

I was asked to create user groups for about 50 of our applications. The requirement was to access on a daily basis the user IDs in the various DB tables used by these applications and update a set of groups in ServiceNow, each one being attached to a CI. In this way, using CI upstream and downstream relationships we can tell exactly how many users might be impacted from a change request, thus helping us to decide whether a change needs to be considered Normal or Standard in ITIL terms.

Of course I didn't want to do this all by hand, so I put a script together that iterates through a CI table and creates a Data Source, a Transform Map and a Scheduled Import for each CI. It still meant going back and doing some manual work as the database name was not always consistent, nor the SQL query that would get the list of user IDs, but once this whole important mechanism was created, it was just the work of a few hours to get the database names and queries configured. Here's the script I used. Note that I removed one piece, which is the script that is launched by the Transform Map that helps populate the user tables. You can find that script in another one of my blog posts: Programatically importing users from external apps and adding them to groups

Here's the script to create Data Sources, Transform Maps and Scheduled Imports (simply change the CI table to your CI table. And note that you can easily restrict the set of objects created by adding some parameters to the first query):

var gr = new GlideRecord('u_cmdb_ci_applications_web_jav'); //Indicate the table to query from

gr.query(); //Execute the query. you can add parameters here to filter the result set

while (gr.next()) { //While the recordset contains records, iterate through them

      var dsName = 'Users ' + gr.name;

      var dsTable = 'u_' + dsName.toLowerCase().replace(/ /g, '_');

      var dsId = createDataSource(dsName, dsTable, gr.name);

      if (dsId != null) {

              if (createTransformMap(dsName, dsTable, gr.name) != null) {

                      createScheduledImport(dsName, dsId);

              }

      }

}

function createDataSource(dsName, dsTable, grName) {

      var ds = new GlideRecord('sys_data_source'); //Indicate the table to query from

      ds.query('name', dsName); //Execute the query

      while (ds.next()) {

              //exists so just return sys_id and don't create new record

              return ds.sys_id;

      }

      ds.initialize();

      ds.name = dsName;

      ds.import_set_table_label = dsName;

      ds.import_set_table_name = dsTable;

      ds.type = "JDBC";

      ds.mid_server = '2aa0ed1b44cc11004e4f4c79f38a2a50'; //the sys_id of our mid server

      ds.database_name = grName;

      ds.jdbc_user_name = ''; //credentials

      ds.jdbc_password = ''; //credentials

      ds.jdbc_server = ''; //put your server url here

      ds.query = 'Specific SQL';

      ds.sql_statement = 'select distinct user_id from users'; //whatever SQL query you need

      var dsId = ds.insert();

      if (dsId != null) {

              gs.log("Added data source" + ds.name);

              return dsId;

      }

      return null;

}

function createScheduledImport(dsName, dsId) {

      var is = new GlideRecord('scheduled_import_set'); //Indicate the table to query from

      is.query('name', dsName); //Execute the query

      while (is.next()) {

              //exists so just return sys_id and don't create a new record

              return is.sys_id;

      }

      is.initialize();

      is.name = dsName;

      is.data_source = dsId;

      is.run_type = 'daily';

      is.run_as = '2d80600e785649003305f202b7c713ed';         //sys_id of user to run process as. must have appropriate rights

      var isId = is.insert();

      if (isId != null) {

              gs.log("Created scheduled import" + is.name);

              return true;

      }

      return false;

}

function createTransformMap(dsname, dsTable, grName) {

      var tm = new GlideRecord('sys_transform_map'); //Indicate the table to query from

      tm.query('name', dsName); //Execute the query

      while (tm.next()) {

              //exists so just return sys_id and don't create record

              return tm.sys_id;

      }

      tm.initialize();

      tm.name = dsName;

      tm.source_table = dsTable;

      tm.target_table = 'sys_user_grmember';

      tm.active = true;

      tm.run_business_rules = true;

      tm.enforce_mandatory_fields = 'No';

      tm.run_script = true;

      tm.script = ''; //put any script you need to process your data here

      var tmId = tm.insert();

      if (tmId != null) {

              gs.log("Added transform map" + tm.name);

              return tmId;

      }

      return null;

}

[yjaques]

Small update, the function that creates the data source should also set the "format" field, in my case to MySQL, so after

      ds.type = "JDBC";

add:

      ds.format = "com.mysql.jdbc.Driver";