I need to run a one-time script to set the dates on all KB articles. How do I do that?

[jeremyduffy]

I have a lot of KB articles that I want to set "due dates" for, but hitting each team with these all at once would be a nightmare for all involved. Instead, I was thinking of staggering them over a 6-month period, but how do I functionally do it?

I've written the following psuedo-code to describe what I'm going for:

SELECT KBAs where NOT ‘Help me Know Hub’ AND Last_Validated_Date is blank GROUP BY Owning_Group ORDER BY creation date ASC 
Foreach Owning_Group as Group: 
    Articles_Arr = Group[Articles] 
    Total_Articles = Array_Count(Articles_Arr) 
    If (Total_Articles < 6): 
        Foreach Articles_Arr as Article: 
            Article[Last_Validated_Date] = 306 days ago 
    If (Total_Articles > 6): 
        Sixth = Max(Total_Articles/6) // A sixth of the total rounded up 
        Past_Date = 306 // One day from starting the notification process 
        Count = 0 
        // Spread the due dates into 6 months 
        For (i=0;i<Total_Articles;i++): 
            Articles_Arr[i][Last_Validated_Date] = Past_Date 
            Count++ 
            if Count = Sixth OR Count > 20: 
                Count = 0       // Start counting the next sixth 
                Past_Date += 30 // Add 30 days 

So, basically, I would run this one time and have all the dates set based on a six-month even spread of their total KBA count. I think a flow can run the query and can even do the for-each part, but then how do I evaluate the count and skips in the flow? Maybe I could create an action that takes as input the count out of the total (can I send those values from a flow to an action) and returns a date value based on those two numbers?

[Ankur Bawiskar · Accepted solution]

@jeremyduffy 

something like this, but please test and enhance

var kbGR = new GlideRecord('kb_knowledge');
kbGR.addQuery('u_owning_group', '!=', ''); // Adjust for your group field
kbGR.addQuery('u_kb_type', '!=', 'Help me Know Hub'); // Adjust for your type field
kbGR.addNullQuery('u_last_validated_date'); // Adjust for your validated date field
kbGR.orderBy('u_owning_group');
kbGR.orderBy('sys_created_on');
kbGR.query();

var groupMap = {};

while (kbGR.next()) {
    var group = kbGR.u_owning_group.toString();
    if (!groupMap[group]) groupMap[group] = [];
    groupMap[group].push(kbGR.sys_id.toString());
}

// Now process each group
for (var group in groupMap) {
    var articles = groupMap[group];
    var total = articles.length;
    if (total < 6) {
        // Set all to 306 days ago
        for (var i = 0; i < total; i++) {
            var artGR = new GlideRecord('kb_knowledge');
            if (artGR.get(articles[i])) {
                artGR.u_last_validated_date = gs.daysAgo(306); // or use gs.dateGenerate()
                artGR.update();
            }
        }
    } else {
        var sixth = Math.ceil(total / 6);
        var pastDays = 306;
        var count = 0;
        for (var i = 0; i < total; i++) {
            var artGR = new GlideRecord('kb_knowledge');
            if (artGR.get(articles[i])) {
                artGR.u_last_validated_date = gs.daysAgo(pastDays);
                artGR.update();
            }
            count++;
            if (count === sixth || count > 20) {
                count = 0;
                pastDays -= 30; // Move the "due date" up by 30 days for the next batch
            }
        }
    }
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[jeremyduffy]

I'm accepting this because it's clearly clean and correct code from my experience - I  don't know the ServiceNow-specific stuff yet, but this certainly seems accurate. As you said, I'll use it as a base and work from there, but thank you so much for doing the legwork!

So basically, the script DOES have access to queries so I don't need to futz around with flows. I can ask my engineering team how to run this and test, but this is a fantastic start, thank you 🙂