Average time to publish knowledge from review state report

[Raju320]

Hello Everyone,

Please help me to create a report for average time to publish knowledge articles from review state to publish. Please provide suggestions or solutions for it.

[Yashsvi]

Hi @Raju320,

To create a report in ServiceNow that measures the average time to publish knowledge articles from review state to publish, follow these steps:

1. Define Requirements:
- Measure time from review to publish state.
- Data needed: `review_date` and `publish_date`.

2. Identify Relevant Tables:
- Use `kb_knowledge` table.

3. Create Data Points (if necessary):
- Ensure timestamps for state changes (`review_date` and `publish_date`) are captured, possibly using a business rule.

4. Create the Report:
- Navigate to `Reports > Create New`.
- Select `kb_knowledge` as the data source.
- Choose a `List` or `Calculated field` type.
5. Configure Report:
- Filter by `state = 'published'`.
- Add columns: `Number`, `Short Description`, `Review Date`, `Publish Date`.
- Create a calculated field to determine the time difference: `DATEDIFF(publish_date, review_date)`.

6. Generate and Visualize:
- Run the report to list articles with their durations.
- Use aggregation functions to show the average time.

7. Save and Schedule:
- Save the report with a meaningful name.
- Set a schedule for regular generation and email distribution if needed.

By following these steps, you can effectively track and report on the average time taken to publish knowledge articles in ServiceNow.

Thank you, please make helpful if you accept the solution. 

[Raju320]

@Yashsvi 

Thanks for the reply, Is there is any way to fulfill this requirement without creating new fields on the "kb_knowlegde" table.

[Yashsvi]

Hi @Raju320,

Yes, you can fulfill this requirement without creating new fields on the kb_knowledge table by utilizing the existing data and ServiceNow's reporting and scripting capabilities:

1. Use the `sys_audit` Table:
- This table logs changes, including state changes.

2. Create a Script Include:
- Navigate to `System Definition > Script Includes`.
- Add a new Script Include to calculate the time difference between the `review` and `published` states.

var KnowledgeArticleTime = Class.create();
KnowledgeArticleTime.prototype = {
initialize: function() {},

getTimeToPublish: function(articleSysId) {
var reviewTimestamp;
var publishTimestamp;

var auditGR = new GlideRecord('sys_audit');
auditGR.addQuery('documentkey', articleSysId);
auditGR.addQuery('fieldname', 'state');
auditGR.orderBy('sys_created_on');
auditGR.query();

while (auditGR.next()) {
if (auditGR.newvalue == 'review') {
reviewTimestamp = new GlideDateTime(auditGR.sys_created_on);
}
if (auditGR.newvalue == 'published' && reviewTimestamp) {
publishTimestamp = new GlideDateTime(auditGR.sys_created_on);
break;
}
}

if (reviewTimestamp && publishTimestamp) {
return GlideDateTime.subtract(publishTimestamp, reviewTimestamp).getNumericValue() / 1000; // Time in seconds
}
return null;
},

type: 'KnowledgeArticleTime'
};

3. Create the Report:
- Go to `Reports > Create New`.
- Select the `kb_knowledge` table.
- Add a calculated field using the script to determine the time difference:

4. Aggregate Results:
- Use the report's aggregation features to calculate the average time.

5. Save and Schedule:
- Save the report and set a schedule for regular updates if needed.

This approach leverages existing audit data to calculate the required metrics without modifying the `kb_knowledge` table.

Thank you, please make helpful if you accept the solution.