[Inventory] [POC] Explore variations of `internal/inventory/entities` API to support grouping entities

[Bluefinger]

With the need for grouping entities for Inventory, it became clear there's going to be a number of potential ways to approach this. For the sakes of making a good decision on the way forward on the API side, we should do a couple of POCs to explore the following:

• Making a single API call to return the grouped data based on the grouping criteria.
• Making multiple API calls to return the needed data for each group as per the grouping criteria.

The purpose of this is to discover the following:

• Which approach is the easiest to implement to unblock a one-level grouping solution
• Which approach is the most complex to extend to a nested grouping solution
• Which one enables the least effort on the frontend side to implement that feature (can the API support this from day one?)

We should not assume we'd only group by entity.type, but any relevant field could be selected for the first-level (and later, more than one field for grouping).

[elasticmachine]

Pinging @elastic/obs-ux-infra_services-team (Team:obs-ux-infra_services)

[Bluefinger]

Adding some of the ideas for this by @cauemarcondes here

GET /internal/inventory/entities/group_by/{field}

export const groupEntitiesRoute = createInventoryServerRoute({
  endpoint: 'GET /internal/inventory/entities/group_by/{field}',
  params: t.type({
    path: t.type({
      field: t.string,
    }),
    query: t.partial({
      where: jsonRt.pipe(t.array(t.string)),
    }),
  }),
  options: {
    tags: ['access:inventory'],
  },
  handler: async ({ params, context, logger }) => {
    const coreContext = await context.core;
    const inventoryEsClient = createObservabilityEsClient({
      client: coreContext.elasticsearch.client.asCurrentUser,
      logger,
      plugin: `@kbn/${INVENTORY_APP_ID}-plugin`,
    });

    const { field } = params.path;
    const { where } = params.query;
    const whereClauses = where?.map((filter) => `WHERE ${filter}`).join('\n |');

    const groups = await inventoryEsClient.esql('get_entities_groups', {
      query: `
        FROM entities*latest
        ${whereClauses ? `| ${whereClauses}` : ''}
        | STATS count = COUNT(*) by ${field}
      `,
    });

    return esqlResultToPlainObjects(groups);
  },
});

We'd call it like: groupBy = ['entity.type', 'agent.name']

1. Initial request: Group by entity.type: http://localhost:5699/foo/internal/inventory/entities/group_by/entity.type?where=[]
2. User opens entity type container accordion (Group by agent.name filtering by the selected entity.type): http://localhost:5699/foo/internal/inventory/entities/group_by/agent.name?where=["entity.type==\"container\""]