Load CMDB Data via Email

Loading CMDB via email isn’t the best option, you would be better off building an REST integration. However there are times when working with vendors or partners that email is the “best” option to load data in a particular situation.

IMPORT PROCESS

Important Note: It is best practice to run the import in a development instance first to see if you have any errors or mistakes. It can be difficult to remove a mistake in data after it is within production.

Import process is as follows

Inbound Action. Processes the inbound email
Script Include. Code that loads and schedules the import set
Data Source. Data in Excel (or CSV)
Import Set. Data is imported to a Import Set, a temporary location for the data
Transform Map. Data is transformed to the Target Table
Target Table. The end result of the import. CIs, Users, Groups, etc.

STEP 1: INBOUND ACTION

I am reusing the code from this community post. You’ll want to substitute the importSetTableName variable for Name of Import Set and the transformMapIDs variable to the SysID of the Transform Map you create later.

The community post also references how to do with scoped applications, but I am going to skip that topic in this article.

Name: Load CMDB Data
Active: True
Stop Processing: true
Execution Order: 20
Type: New
When to Run
Conditions: Subject starts with CMDB Data Load
Script:
(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {
    var importSetTableName = "u_cmdb_data_load"; //Name of Import Set
    var transformMapIDs = "8a36e396db33330033ba8e4748961919";   //SysID of Transform Map
    var applicatonScope = "Global";

    // Create the datasource record
    current.name = "File import from: " + email.from;   //Feel free to rename this as appropriate
    current.import_set_table_name = importSetTableName;
    current.file_retrieval_method = "Attachment";
    current.type = "File";
    current.format = "Excel"; // For Excel Files
    //current.format = "CSV"; // For CSV Files
    current.header_row = 1;
    current.sheet_number = 1;
    current.sys_package.setDisplayValue(applicatonScope);
    current.sys_scope.setDisplayValue(applicatonScope);
    var dataSourceID = current.insert();

    /*
    * Schedule Load of Attachment
    *
    * This inbound email action will generate an import data source, however the attachment isn't copied to the data source until
    * after the insert of the record.   Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
    */

    new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);

})(current, event, email, logger, classifier);

STEP 2: SCRIPT INCLUDE

Name: EmailFileImportUtils
Accessible from: All application scopes
Script:
var EmailFileImportUtils = Class.create();
EmailFileImportUtils.prototype = {
    initialize: function() {
    },

    scheduleImport: function(dataSourceID, transformMapIDs) {
        /*
        * Create scheduled job to process import
        *
        * The inbound email action will generate an import data source, however the attachment isn't copied to the data source until
        * after the insert of the record.   The code below will create a scheduled job to process the import 30 seconds later
        * so that attachment has time to be copied to the data source from the email.
        */

        var schRec = new GlideRecord("sys_trigger");
        schRec.name = "Load Data Source: " + dataSourceID;
        schRec.trigger_type = 0;   // Run Once
        schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapIDs + "')";

        var nextAction = new GlideDateTime();
        nextAction.addSeconds(30);   // 30 seconds should be enough time however this can be changed.
        schRec.next_action = nextAction;
        schRec.insert();
    },

    loadImportSet: function(dataSourceID, transformMapIDs) {
        // Get Datasource Record
        var dataSource = new GlideRecord("sys_data_source");
        dataSource.get(dataSourceID);

        // If CSV and header isn't on row 1, recreate attachment with empty rows removed
        if (dataSource.getValue("format") == "CSV" && dataSource.getValue("header_row") > 1) {
            var attachmentRec = new GlideRecord("sys_attachment");
            attachmentRec.addQuery("table_sys_id", dataSource.getValue("sys_id"));
            attachmentRec.query();
            if (attachmentRec.next()) {
                var oldAttachmentID = attachmentRec.getValue("sys_id");
                var inputStream = new GlideSysAttachment().getContentStream(oldAttachmentID);
                var textReader = new GlideTextReader(inputStream);
                var ln = " ";
                var newLine = "";

                var lineCounter = 0;
                var headerRow = parseInt(dataSource.getValue("header_row"));
                while((ln = textReader.readLine()) != null) {
                    lineCounter += 1;
                    if (lineCounter < headerRow) 


                    if (ln.length > 1) {
                        newLine += ln + "\n";
                    }
                }
                new GlideSysAttachment().write(dataSource, 'Changed ' + attachmentRec.getValue('file_name'), "text/csv", newLine);
            }
        }       

        // Process data source file
        var loader = new GlideImportSetLoader();
        var importSetRec = loader.getImportSetGr(dataSource);
        var ranload = loader.loadImportSetTable(importSetRec, dataSource);
        importSetRec.state = "loaded";
        importSetRec.update();

        // Transform import set
        var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapIDs);
        transformWorker.setBackground(true);
        transformWorker.start();
    },

    type: 'EmailFileImportUtils'

};

STEP 3: CREATE EXCEL FILE

Here is an example excel file. You can import any number of columns and types.

News Jace Pro NJP

Load CMDB Data via Email

RELATED LINKS

IMPORT PROCESS

STEP 1: INBOUND ACTION

STEP 2: SCRIPT INCLUDE

STEP 3: CREATE EXCEL FILE