Recently we came across one of the requirements to create CSV file from script. The requirement is, When user submits a Request for creating Configuration Items / Assets. It should create the CI Records and created CI should be sent to the user in the form of CSV file.
User need to feel the CI details and revert to ServiceNow with updated CSV file and we need to update the CMDB with these details.
1. Code for creating configuration Items / Assets and Creating CSV file of created CI records:
- Create properties, instead using SysId’s directly in script.
//var ciToBeCreated = current.variables.number_of_ci_s_to_be_created ; variable available on catalog item form
var ciToBeCreated = 4 ;
var array =[];
for ( var i =0; i < ciToBeCreated ; i++){
var gr = new GlideRecord('alm_asset');
gr.initialize();
gr.asset_tag = new NumberManager(gr.sys_meta.name).getNextObjNumberPadded();
gr.model = '11aaeb3f3763100044e0bfc8bcbe5d8e'; //
gr.model_category ='55d57c14c3031000b959fd251eba8fa6';
gr.comments = 'Created From Service Request';
var sysID = gr.insert();
array.push(sysID.toString());
}
var cid = array.toString(); // we are storing all the sys_id of created records
// Creating CSV FILE for above created Records
var csvHeaders = ["Sys ID","Asset Tag","Serial Number","Status"]; // this is the columns for the CSV,
var csvAnswers = [];
var attachmentData ='';
var fileName = 'Configuration Item Details.csv';
var gr = new GlideRecord('alm_asset');
gr.addQuery('sys_id','IN',cid); // filtering out the created records from table
gr.query();
while(gr.next()){
var assetDetails = gr.getValue('ci')+","+gr.getValue('asset_tag');
csvAnswers.push(assetDetails.split(','));
var csvData = ''; //The variable csvData will contain a string which is used to build the CSV file contents
for (var i = 0; i < csvHeaders.length - 1; i++) { //Build the Headers
csvData = csvData + '"' + csvHeaders[i] + '"' + ',';
}
csvData = csvData + '"' + csvHeaders[csvHeaders.length - 1] + '"' + '\r\n';
for (var k = 0; k < csvAnswers.length; k++) {
for (var m = 0; m < csvAnswers[k].length; m++) {
if (m == (csvAnswers[k].length-1))
csvData = csvData + '"' + csvAnswers[k][m].toString() + '"';
else
csvData = csvData + '"' + csvAnswers[k][m].toString() + '"' + ',';
}
csvData = csvData+"\r\n";
}
attachmentData =csvData ;
}
// Code for addding attachment to the record
var grRitm = new GlideRecord('sc_req_item');
grRitm.addQuery('sys_id','b822ad9a1b410010364d32a3cc4bcb87');
grRitm.query();
if(grRitm.next()){
var grAttachment = new GlideSysAttachment();
grAttachment.write(grRitm, fileName, 'application/csv', attachmentData);
}
Once user sent us the CSV with updated details, with the help of below code we are pulling out the values and updating the CI.
2. Getting Attachment Content -CSV
var ritmSysId= 'b822ad9a1b410010364d32a3cc4bcb87';
var gr = new GlideRecord('sys_attachment');
gr.addQuery('table_sys_id', ritmSysId); // sys_id of attachment record\
gr.orderByDesc('sys_created_on');
gr.query();
if(gr.next()){
var gsa = new GlideSysAttachment();
var bytesInFile = gsa.getBytes('sc_req_item', ritmSysId); // tablename, table sysID
var originalContentsInFile = Packages.java.lang.String(bytesInFile); // originalContentsInFile
originalContentsInFile = String(originalContentsInFile);
gs.print("Contents of Attached CSV File: "+originalContentsInFile);
var fileData = originalContentsInFile.split('\n');
var csvHeaders = fileData[0] ;
var csvHeadersValues = csvHeaders.split(',');
for(i=1 ; i< fileData.length-1 ; i++){
gs.print("Values: "+i+" : "+fileData[i]);
var rowDetails = fileData[i] ;
var rowValues = rowDetails.split(',');
var sysIdOfCi = rowValues[0].toString();
var assetTag = rowValues[1];
var serialNumber = rowValues[2];
var status = rowValues[3];
var grAsset = new GlideRecord('cmdb_ci') ;
grAsset.addQuery('sys_id',sysIdOfCi);
grAsset.query();
if(grAsset.next()){
grAsset.setDisplayValue('asset_tag',assetTag);
grAsset.setValue('serial_number',serialNumber);
grAsset.setValue('install_status',status);
grAsset.update();
}
}
}
Hope you will find it as helpful. Don’t forget to Mark it Helpful and Bookmark article so you can easily find on your profile.
Thank you,
Abhishek Gardade
Hexaware Technologies
