How to view global KB usage in Jira Service Management
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:
| Event | Description |
|---|---|
stats.event.kb.article.shared | A KB article was shared in a ticket |
stats.event.kb.helpful.clicked | An article was marked as helpful |
stats.event.kb.not.helpful.clicked | An article was marked as not helpful |
stats.event.kb.page.viewed | Someone viewed an article from the customer portal |
stats.event.search.completed | A 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