[BUG] On demand reports fail to generate a CSV file for download.

[necoras]

What is the bug? After updating to v 2.13, we're having some of our reports fail to generate. It's not every report, but it is consistent. That is, if a query fails to allow a csv to be generated it will always fail, and if one succeeds it will always succeed. The only error message in the console is a 500 with the message of:

"The 'order' parameter should be one of 'asc' or 'desc'".

This seems to indicate that there is an Order enum that isn't being properly declared, but I don't know where in the codebase that is. There is a "Sort" query string parameter being passed into the generate-report call that may be related.

How can one reproduce the bug? Steps to reproduce the behavior:

1. Generate a query of some data
2. Click on the Reporting button.
3. Click Generate CSV
4. See if error occurs. If so, toaster with text "Error downloading report." will appear.
5. Check dev console, see 500 error with message "The 'order' parameter should be one of 'asc' or 'desc'"

What is the expected behavior? CSV file is generated and downloaded.

What is your host/environment?

• OS: Amazon Opensearch
• Version 2.13
• Plugins

Do you have any screenshots?

Do you have any additional context? I think I've traced it down to a bad querystring being built/supplied in context_menu.js: https://github.com/opensearch-project/dashboards-reporting/blob/985e623c912fb90af51357efdd2c3e9feca9f854/public/components/context_menu/context_menu.js#L177

But that code hasn't changed in years (aside from the addition of the xlsx support a bit above it.)

[dblock]

Thanks for opening this. Please do comment if you find the root cause.

[Catch All Triage w/ 1, 2, 3]

[Swiddis]

Some further debugging: I traced it down to this line in dataReportHelpers:

return esb.sort(element[0], element[1]);

The error happens when element[1] is not one of the listed values. But I haven't been able to reproduce this error path yet with actual reports (including migrating from 2.9 to 2.13).

[Swiddis]

Update: it looks like after the upgrade, for some types of sorts with saved searches, the sort field is de-nested. If you have sort: [["field1", "asc"]] before the upgrade, it may have become sort: ["field1", "asc"]. Per the above logic, this means it incorrectly handles the value and tries to sort the field f by order i. I'm not sure what causes that to get incorrectly transformed during the upgrade, but manually updating the saved search in the index should be sufficient to fix the issue.

[Swiddis]

A "just fix this" solution, the following will reset the sort information for all saved searches, which will stop the errors. From there, they can be re-saved from the UI with new sort information. In local testing I wasn't able to get the search to fix itself once corrupted using only the UI, but either fixing the sort field per-report or resetting everything worked fine.

POST .kibana/_update_by_query
{
  "query": {
    "match": {
      "type": "search"
    }
  },
  "script": {
    "source": "ctx._source.search.sort = []",
    "lang": "painless"
  }
}

There probably exists a more elaborate painless script that can fix all the reports non-destructively (if not nested -> nest, if empty -> keep empty, if already nested -> don't nest). If I had more time on-hand I'd try to write it. Note re-saving with a new sort will cause both: sort: ["@timestamp", "asc", ["@timestamp", "asc"]].

[Swiddis]

Wrote #458 as a partial fix, but the root cause is still unknown