[9.0][Deprecations][Detection Engine] Square brackets '[]' need to be removed in FROM METADATA declaration

[yctercero]

Parent ticket - https://github.com/elastic/kibana-team/issues/1173

Summary

Address API deprecations ahead of 9.0:

• x-pack/test/security_solution_api_integration/test_suites/detections_response/detection_engine/rule_execution_logic/trial_license_complete_tier/configs/serverless.config.ts

  Elasticsearch deprecation: 299 Elasticsearch-9d3fc35316c008f2fa637c73d93ebc9df81f20e4 Line 1:20: Square brackets '[]' need to be removed in FROM METADATA declaration
  Stack trace:
  at KibanaTransport.request (kibana/node_modules/@elastic/transport/lib/Transport.js:651:20)
  at KibanaTransport.request (kibana/node_modules/@kbn/core-elasticsearch-client-server-internal/src/create_transport.js:60:16)
  at search (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/esql/esql_request.js:21:27)
  at performEsqlRequest (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/esql/esql_request.js:37:11)
  at kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/esql/esql.js:96:26
  at kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/create_security_rule_type_wrapper.js:383:33
  at executor (kibana/node_modules/@kbn/rule-registry-plugin/server/utils/create_persistence_rule_type_wrapper.js:187:22)
  at runExecutors (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_preview/api/preview_rules/route.js:184:15)
  at Object.fn (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_preview/api/preview_rules/route.js:327:11)
  at kibana/node_modules/@kbn/core-http-router-server-internal/src/versioned_router/core_versioned_route.js:113:24
  at Router.handle (kibana/node_modules/@kbn/core-http-router-server-internal/src/router.js:214:30)
  at handler (kibana/node_modules/@kbn/core-http-router-server-internal/src/router.js:138:50)
  at exports.Manager.execute (kibana/node_modules/@hapi/hapi/lib/toolkit.js:60:28)
  at Object.internals.handler (kibana/node_modules/@hapi/hapi/lib/handler.js:46:20)
  at exports.execute (kibana/node_modules/@hapi/hapi/lib/handler.js:31:20)
  at Request._lifecycle (kibana/node_modules/@hapi/hapi/lib/request.js:370:32)
  at Request._execute (kibana/node_modules/@hapi/hapi/lib/request.js:280:9)
  Query:
  200
  POST /_query
  {query:from ecs_compliant [metadata _id] | where id==cb8b08af-e524-4b96-987a-46c981ee1991 | keep _id, agent.name | limit 101,filter:{bool:{filter:[{range:{@timestamp:{lte:2020-10-28T06:30:00.000Z,gte:2020-10-28T05:30:00.000Z,format:strict_date_optional_time}}},{bool:{must:[],filter:[],should:[],must_not:[]}}]}}}

• x-pack/test/security_solution_api_integration/test_suites/detections_response/detection_engine/rule_execution_logic/trial_license_complete_tier/configs/ess.config.ts

  Elasticsearch deprecation: 299 Elasticsearch-9.0.0-5bf446ea2e10aac093f0e02dd123db39466b6c56 Line 1:20: Square brackets '[]' need to be removed in FROM METADATA declaration
  Stack trace:
  at KibanaTransport.request (kibana/node_modules/@elastic/transport/lib/Transport.js:651:20)
  at KibanaTransport.request (kibana/node_modules/@kbn/core-elasticsearch-client-server-internal/src/create_transport.js:60:16)
  at search (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/esql/esql_request.js:21:27)
  at performEsqlRequest (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/esql/esql_request.js:37:11)
  at kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/esql/esql.js:96:26
  at kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_types/create_security_rule_type_wrapper.js:383:33
  at executor (kibana/node_modules/@kbn/rule-registry-plugin/server/utils/create_persistence_rule_type_wrapper.js:187:22)
  at runExecutors (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_preview/api/preview_rules/route.js:184:15)
  at Object.fn (kibana/node_modules/@kbn/security-solution-plugin/server/lib/detection_engine/rule_preview/api/preview_rules/route.js:327:11)
  at kibana/node_modules/@kbn/core-http-router-server-internal/src/versioned_router/core_versioned_route.js:113:24
  at Router.handle (kibana/node_modules/@kbn/core-http-router-server-internal/src/router.js:214:30)
  at handler (kibana/node_modules/@kbn/core-http-router-server-internal/src/router.js:138:50)
  at exports.Manager.execute (kibana/node_modules/@hapi/hapi/lib/toolkit.js:60:28)
  at Object.internals.handler (kibana/node_modules/@hapi/hapi/lib/handler.js:46:20)
  at exports.execute (kibana/node_modules/@hapi/hapi/lib/handler.js:31:20)
  at Request._lifecycle (kibana/node_modules/@hapi/hapi/lib/request.js:370:32)
  at Request._execute (kibana/node_modules/@hapi/hapi/lib/request.js:280:9)
  Query:
  200
  POST /_query
  {query:from ecs_compliant [metadata _id] | where id==f26f675c-b52f-4f17-a63a-89632b199d42 | keep _id, agent.name | limit 101,filter:{bool:{filter:[{range:{@timestamp:{lte:2020-10-28T06:30:00.000Z,gte:2020-10-28T05:30:00.000Z,format:strict_date_optional_time}}},{bool:{must:[],filter:[],should:[],must_not:[]}}]}}}

[elasticmachine]

Pinging @elastic/security-solution (Team: SecuritySolution)

[elasticmachine]

Pinging @elastic/security-detection-engine (Team:Detection Engine)

[elasticmachine]

Pinging @elastic/security-detections-response (Team:Detections and Resp)

[yctercero]

I think this PR will address this issue - https://github.com/elastic/kibana/pull/196991#event-14757087587