How to efficiently report HR cases raised within 24 hours of KB article views

[NowNinja727]

Hello Community,

I’m working on a reporting requirement where we need to report HR cases raised within 24 hours of a user viewing a KB article.

Here’s what I’ve tried so far:

1. Script Include Approach with Report
   I wrote a Script Include that loops through kb_use records, checks the user and timestamp, and then queries sn_hr_core_case for cases created within 24 hours of the KB view. This works fine for recent data (e.g., last 6 months), but when I remove the date filter to process all historical data, the script becomes extremely slow and impacts instance performance because kb_use has 200k+ records in my environment.

Example snippet:

var kbUse = new GlideRecord('kb_use');
kbUse.query();
while (kbUse.next()) {
    var kbUser = kbUse.getValue('user');
    var kbCreatedDate = kbUse.getValue('sys_created_on');
    var startDateTime = new GlideDateTime(kbCreatedDate);
    var endDateTime = new GlideDateTime(kbCreatedDate);
    endDateTime.addDaysUTC(1);

    var hrCase = new GlideRecord('sn_hr_core_case');
    hrCase.addQuery    hrCase.addQuery('sys_created_on', '>=', startDateTime);
    hrCase.addQuery('sys_created_on', '<=', endDateTime);
    hrCase.addQuery('opened_for', kbUser);
    hrCase.query();
    // Collect sys_ids

2. Performance Analytics Widget
I tried using the same Script Include in a PA indicator, which worked for new data but failed for historical data jobs due to the same performance issue.

3. Database View
I attempted to create a database view joining kb_use and sn_hr_core_case on user fields, but couldn’t figure out how to apply the 24-hour condition effectively at the DB level.

Challenge

The main issue is performance when processing large volumes of KB views and HR cases. I need a solution that:

• Handles historical data efficiently.
• Applies the 24-hour condition between KB view and HR case creation.
• Works for reporting or PA widgets without timing out.

Question

Has anyone implemented a similar requirement? What’s the best approach here?

• Is there a way to optimise the Script Include?

Any guidance or examples would be greatly appreciated!

Thanks!😊