Locating Jira Service Management's Knowledge Base search statistics through the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below
The purpose of this Knowledge Base is to provide additional information regarding the following Jira Service Management tables:
|
|---|
When using a Service Management along with Confluence to make use of the Knowledge Base linking functionality, these tables are responsible for keeping track of all searches that are done through the customer portal. Here's how they work:
When a Customer accesses either the Customer Portal or the Issue Creation pages, inputs on the following fields are directly searched through the linked Confluence page
Then, when inputting something on those fields, a search is run on the linked Confluence space in order to look for pages that might help your customer and avoid the creation of a ticket. Here's how these results look on screen:
Solution
Database tables
Every search performed on these fields will include a line to the AO_0201F0_STATS_EVENTtable, which will look like this:
We can, then, consult the actual value of the search through the AO_0201F0_STATS_EVENT_PARAM table by matching the ID (AO_0201F0_STATS_EVENT) value with the STATS_EVENT_ID (AO_0201F0_STATS_EVENT_PARAM) value.
|
|---|
Which results in:
Here's the information breakdown of every line included:
searchTime | Actual date / time in Epoch timestamp. This value can be converted by tools such as https://www.epochconverter.com/. |
pageIds | ID of the Confluence pages retrieved as result of the search. |
searchTerm | The string that was used to generate the search on Confluence's linked page. |
origin | Where the search originated from within Service Management. smart-portal refers to the Customer Portal section, while request-type-create refers to the Create Request page. |
requestTypeNames / requestTypeIds | If the search is done through the Customer portal, a value for each retrieved Request Type name as well as their ID's is added to the table. |
username | Username that triggered the search. |
This information can be valuable for custom reporting and system administrators interested on the actual data of their userbase searches.
Was this helpful?