Help with background script

[Abhijit Das7]

Hi Team,

I have 19000 active user created assets in alm_asset table. I exported this data in csv file. Then I copied all serial numbers present in CSV file (means all 19000 assets). Then on table I have put two conditions that serial number is one of (all the 19000 serial numbers) and asset is active (install_status =1). I got around 33000 assets. These 33000 assets has user created + MDP created assets

Requirement: I want a background script which gives the exact count of this scenario: there are exact 2 records for single asset (same serial number) and both are active and one is created by user and another MDP. And MDP created date is latest compared to user created asset.

As this data is very large and background script is timing out while running the script. I ran the below code but it keeps on buffering

(function () {

    var BATCH_SIZE = 5000;
    var offset = 0;
    var totalCount = 0;
    var hasMore = true;

    while (hasMore) {

        var ga = new GlideAggregate('alm_asset');
        ga.addQuery('install_status', 1);
        ga.addNotNullQuery('serial_number');

        ga.groupBy('serial_number');
        ga.addAggregate('COUNT');

        // Pagination
        ga.setLimit(BATCH_SIZE);
        ga.setOffset(offset);

        ga.query();

        var batchProcessed = 0;

        while (ga.next()) {

            var count = ga.getAggregate('COUNT');

            // Only interested in exactly 2 records
            if (count == 2) {

                var serial = ga.getValue('serial_number');

                var gr = new GlideRecord('alm_asset');
                gr.addQuery('serial_number', serial);
                gr.addQuery('install_status', 1);
                gr.orderByDesc('sys_created_on');
                gr.query();

                var records = [];

                while (gr.next()) {
                    records.push({
                        created_by: gr.sys_created_by.toString(),
                        created_on: gr.sys_created_on.getDisplayValue()
                    });
                }

                if (records.length == 2) {

                    var rec1 = records[0]; // latest
                    var rec2 = records[1];

                    // Check MDP + User condition
                    if (
                        rec1.created_by == 'mdp_user' &&   // latest must be MDP
                        rec2.created_by != 'mdp_user'      // older must be user
                    ) {
                        totalCount++;
                    }
                }
            }

            batchProcessed++;
        }

        gs.info('Processed batch offset: ' + offset + 
                ' | Batch size: ' + batchProcessed + 
                ' | Running total: ' + totalCount);

        // Move to next batch
        offset += BATCH_SIZE;

        // Stop condition
        if (batchProcessed < BATCH_SIZE) {
            hasMore = false;
        }
    }

    gs.info('FINAL COUNT = ' + totalCount);

})();
``

Please provide some method where I can get the exact count.

cc: @Ankur Bawiskar 

Thanks in advance

[PankajK75582456]

Hi Abhijit,

In Total how many records are there in alm_asset table. If you could run the script by splitting data in 4 or more parts then it will increase the performance . Also coming to the window capacity for printing log messages, it is only 50k lines of logs, that you can get after background script execution. Rest lines of logs will be truncated even if the script runs smoothly.

[pr8172510]

Hey @Abhijit Das7 ,

(function() {
    
    // First, find all serials that have exactly 2 active assets
    var ga = new GlideAggregate('alm_asset');
    ga.addQuery('install_status', 1);
    ga.addNotNullQuery('serial_number');
    ga.groupBy('serial_number');
    ga.addAggregate('COUNT');
    ga.addHaving('COUNT', '=', 2);
    ga.query();
    
    var validSerials = [];
    
    while (ga.next()) {
        validSerials.push(ga.getValue('serial_number'));
    }
    
    gs.info('Found ' + validSerials.length + ' serials with exactly 2 active assets');
    
    // Now check each serial's latest 2 records
    var finalCount = 0;
    
    for (var i = 0; i < validSerials.length; i++) {
        var serial = validSerials[i];
        
        var gr = new GlideRecord('alm_asset');
        gr.addQuery('serial_number', serial);
        gr.addQuery('install_status', 1);
        gr.orderByDesc('sys_created_on');
        gr.setLimit(2);
        gr.query();
        
        var records = [];
        while (gr.next()) {
            records.push(gr.getValue('sys_created_by'));
        }
        
        if (records.length == 2 && records[0] == 'mdp_user' && records[1] != 'mdp_user') {
            finalCount++;
        }
        
        // Progress update every 1000
        if ((i + 1) % 1000 == 0) {
            gs.info('Processed ' + (i + 1) + ' serials, found: ' + finalCount);
        }
    }
    
    gs.info('=== FINAL COUNT: ' + finalCount + ' ===');
    
})();

[Abhijit Das7]

Hi @pr8172510 ,

The script goes to white screen.