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

NowNinja727
Giga Contributor

3 weeks ago

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!😊
0 Helpfuls
  • 163 Views
1 REPLY 1

SupriyaWaghmode
Kilo Sage

Monday

Hello @NowNinja727  ,

 

Step-by-Step Guide to Collect Historical and Future Data

Step 1: Collect Historical Data

  1. Create a one-time data job to gather previous records.
  2. If you are setting up a new configuration, review past records for timelines.
  3. Use Performance Analytics (PA) for this task.
  4. Configure the job to collect 3–6 months of historical data (or as required).
  5. Run the job once and then deactivate it.
    Purpose: This job is only for retrieving historical data (e.g., data from one year ago).

Step 2: Set Up Ongoing Data Collection

  1. Create another data job for future tracking.
  2. Schedule this job to run daily or monthly, based on your reporting needs.
  3. This job will ensure that all new records are captured and tracked consistently going forward.

Key Points

  • The first job is one-time for historical data.
  • The second job is recurring for future data.
  • Use Performance Analytics for both jobs to ensure accurate reporting.

    Please mark accepted solution if this resolves your query.

    Thanks & Regards ,

    Supriya Waghmode |ServiceNow Consultant

0 Helpfuls