Log viewer doesn't show data although it exists in elastic, Document tables do not support sorting by `sys_id` - Known Error

Log viewer doesn't show data although it exists in elastic, Document tables do not support sorting by `sys_id` Description When the system property `glide.secondary.query.sysid` is set to `true` an additional sort order is added to the query "sort by `sys_id` ascending". Document Tables currently don't support this feature and the error message "No mapping found for [sys_id] in order to sort on" is shown to the end user.

Steps to Reproduce

This behavior is happening on all list views. Our customer is RP8-HF1. A second customer that is also affected is using RP7a. Here's a sample sequence of events:

- ensure the system property "glide.secondary.query.sysid" is set to "true" - ensure Elasticsearch Document Store plugin is installed - Create a Document table named "parent", ensure "is extendable" is checked - Create a second Document Table named "child" that extends "parent" - Create the associated Document Store Index for the table named "child" - Navigate to the "parent" document table - Show the "filter" (the funnel in the upper left, just beneath the table name) - Click on "Add Sort" - Choose a column to sort on - Run the sort - Observe the error message "No mapping found for [sys_id] in order to sort on" - Click on the "filter" button - Observe there is a new sort criteria for "Sys ID" ascending

Workaround There are two work arounds. The first one involves changing a Glide Property and requires the customer to consent to changing the behavior of the system. The second one allows the customer to keep existing behavior and repair the issue.

REVERT Property

Verify the property "glide.secondary.query.sysid" is set to `true` on the customer instance, if not this work-around does not apply.

Confirm with the customer that it is OK to set the property "glide.secondary.query.sysid" to false. Instance downtime is expected because the application nodes will need to be restarted (rolling restart is OK).

To change the value of the property by modifying the DB directly:

== START SQL == update sys_properties set value = 'false' where name = 'glide.secondary.query.sysid'; == END SQL ==

** Rolling RESTART all the application servers **

Navigated to "All Properties" and confirm "glide.secondary.query.sysid" is set to "false".

Open the HLA Log Viewer, confirm that logs are shown when "all components" is selected.

Add Alias for Sys_ID to Elasticsearch

The attached file "AddSysIdAlias.zip" contains the JavaScript script "AddSysIdAliasToAllEsIndices.js" (use `unzip` to extract the file). This script can be run safely in "Scripts - Background" (/sys.scripts.do) to add an alias mapping `sys_id` to `_id` on all existing Elasticsearch indices. You can run this script multiple times, it will only update an Elasticsearch index if it doesn't contain a field named `sys_id`.

** The script must be run in the scope `sn_occ` (Health Log Analytics). **

Running this script will fix the "no mapping found for [sys_id]" issue for EXISTING INDICES only. A good long term solution to use while we are waiting for the Glide code changes would be to add a new Document Index Template that creates the alias whenever a new Elasticsearch index is created. Follow these steps:

1) Set your scope to "Health Log Analytics"

2) Navigate to "Document Store Index Template", note the existing Health Log Analytics templates: we'll need to create a template that has an order number greater than the maximum order in the list (e.g., "200" for the default HLA installation).

3) Click "New" and fill in the form fields:

Name

Add Sys Id Alias

Index patterns

sn_occ*

Order

200

Mappings

{

"properties": {

"sys_id" : {

"type" : "alias",

"path" : "_id"

}

4) Click "Save Template"

Now all new Elasticsearch indices created for the Health Log Analytics application will have the query field `sys_id` mapped to the `_id` field in the index.