How to view global KB usage in Jira Service Management

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

    

Summary

In Jira Service Management, we have the functionality of integrating a Confluence Space as the Knowledge Base source.

Additionally, we can view reports for each project, to understand how the KBs are helping deflect tickets and lower the service desk team volume.

Here we will understand how to retrieve global statistics from the database.

Context

Every time a KB related action is taken, an event is entered in the AO_0201F0_STATS_EVENT table of the Jira Database. There are 5 event types as of this writing:


EventDescription
stats.event.kb.article.sharedA KB article was shared in a ticket
stats.event.kb.helpful.clickedAn article was marked as helpful
stats.event.kb.not.helpful.clickedAn article was marked as not helpful
stats.event.kb.page.viewedSomeone viewed an article from the customer portal
stats.event.search.completedA search was made in the customer portal

Solution

If using a BI tool, capturing the table is enough to extract the data.

When querying it using SQL, we'll need to convert the EVENT_TIME from epoch to a readable date and aggregate it by the desired time.

Here's an example of how to do this on PostgreSQL, for the stats.event.kb.article.shared event, aggregating by day:

SELECT CONCAT(EXTRACT(YEAR from to_timestamp("EVENT_TIME"/1000)), '-',
	EXTRACT(MONTH from to_timestamp("EVENT_TIME"/1000)), '-',
	EXTRACT(DAY from to_timestamp("EVENT_TIME"/1000)) ) as date,
COUNT(*)
FROM public."AO_0201F0_STATS_EVENT"
WHERE "EVENT_KEY" = 'stats.event.kb.article.shared'
GROUP BY 1
ORDER BY 1

Last modified on Nov 18, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.