Need to export loaded data transform history automatically.

[Abhilasha G T]

Hi Team,

we have loaded the data into ServiceNow target table , 

i want the exported excel file of that loaded data  automatically not manually like how many records got inserted, ignored and skipped .. etc.

How to achieve this 

Regards,

Abhilasha G T

[Deepak Shaerma]

Hi @Abhilasha G T 

Create a scheduled job that run daily, weekly-- according to your need.
- Name: Export Transform History
- Run: Daily/Weekly as per your needs
- Time: 
- Script :

(function() {
    // Get the latest import set run
    var importSetRunGR = new GlideRecord('sys_import_set_run');
    importSetRunGR.orderByDesc('sys_created_on');
    importSetRunGR.setLimit(1);
    importSetRunGR.query();
    if (importSetRunGR.next()) {
        // Create the export file
        var reportStreamWriter = new GlideStringWriter();

        // Write headers
        reportStreamWriter.writeLine('Status,Table Name,Inserted,Updated,Errors');

        // Get the details of the run
        var status = importSetRunGR.getValue('state'); // Status of the import set run
        var tableName = importSetRunGR.getValue('name'); // Name of the target table
        var inserted = importSetRunGR.getValue('inserts'); // Number of inserted records
        var updated = importSetRunGR.getValue('updates'); // Number of updated records
        var errors = importSetRunGR.getValue('errors'); // Number of errors

        // Write data
        reportStreamWriter.writeLine(status + ',' + tableName + ',' + inserted + ',' + updated + ',' + errors);

        // Convert the report to a string (CSV format)
        var reportContent = reportStreamWriter.toString();

        // Create the attachment
        var attach = new GlideSysAttachment();
        var contentType = 'text/csv';
        var fileName = 'Transform_History.csv';
        var attachmentSysId = attach.write(importSetRunGR, fileName, contentType, reportContent);

        gs.info('Exported transform history attachment sys_id: ' + attachmentSysId);

        // Sending Email with the Attachment
        var grAttachment = new GlideRecord('sys_attachment');
        if (grAttachment.get(attachmentSysId)) {
            var attachment = new GlideSysAttachment().getContentStream(grAttachment);
            var email = new GlideEmailOutbound();
            email.setSubject('Transform History Export');
            email.setFrom('you@yourcompany.com');
            email.setTo('recipient@theircompany.com');
            email.setBody('Please find the attached transform history export.');
            email.addAttachmentStream(fileName, attachment, contentType);
            email.send();
        }
    }
})();

Please Mark this Helpful and Accepted Solution. If this Helps you to understand. This will help both the community and me..
- Keep Learning ‌‌
Thanks & Regards
Deepak Sharma 

[Abhilasha G T]

Hi Deepak ,

Thanks for your response

we are reloading  data multiple times in a day  to the same target table.

i want the extracted automatic report of each time loading happens,

is this above script , will complete my this requirements also

Regards,

Abhilasha G T

[Abhilasha G T]

Hi Deepak ,

Thanks for your response

we are reloading  data multiple times in a day  to the same target table.

i want the extracted automatic report of each time loading happens, with the all fields name that i have mapped,

is this above script , will complete my this requirements also

Regards,

Abhilasha G T