Aggregating Case Information

[bm11100 (Brent Murphy)]

Hello,

I would like to aggregate case information monthly into certain reports, for example, how many cases were opened in the last month, status, etc..

I know all cases are stored as saved objects, and I was looking in the .kibana index, but having a hard time finding case information there. Is there a particular index in which I can easily aggregate statistics on cases from?

Thanks

[spong (Garrett)]

Oh hey there @bm11100!

As you mentioned, since cases are stored as SO's you'll need to query the .kibana directly if you want to perform aggregations on them (since SO aggs aren't supported within Kibana Visualizations yet). Heavy caveat here though that the .kibana index is a system index and there are future plans to restrict direct access by default, and that modifying anything directly within this index can result in serious problems.

That said, these appear to be all the different case related SO types:

elastic/kibana/blob/b9c563c41c8ec9d46a95b2c0bf090d0504be44fa/x-pack/plugins/cases/common/constants.ts#L15-L20

So you should be able to query for each of these in dev tools ala:

GET .kibana*/_search
{
  "size": 10000,
  "query": {
    "term": {
      "type": {
        "value": "cases"
      }
    }
  }
}

and get a response with cases like:

#! this request accesses system indices: [.kibana_8.1.0_001], but in a future major version, direct access to system indices will be prevented by default
{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 6.728628,
    "hits": [
      {
        "_index": ".kibana_8.1.0_001",
        "_id": "cases:2c174a70-6ff4-11ec-97eb-6b110029d6b5",
        "_score": 6.728628,
        "_source": {
          "cases": {
            "type": "individual",
            "title": "New Case From Alert",
            "tags": [
              "case'"
            ],
            "description": "Hope all has been well Brent! :)",
            "settings": {
              "syncAlerts": true
            },
            "owner": "securitySolution",
            "closed_at": null,
            "closed_by": null,
            "created_at": "2022-01-07T19:58:24.408Z",
            "created_by": {
              "username": "elastic",
              "email": null,
              "full_name": null
            },
            "status": "open",
            "updated_at": "2022-01-07T19:58:26.189Z",
            "updated_by": {
              "full_name": null,
              "email": null,
              "username": "elastic"
            },
            "connector": {
              "name": "none",
              "type": ".none",
              "fields": []
            },
            "external_service": null
          },
          "type": "cases",
          "references": [],
          "namespaces": [
            "default"
          ],
          "migrationVersion": {
            "cases": "8.0.0"
          },
          "coreMigrationVersion": "8.1.0",
          "updated_at": "2022-01-07T19:58:26.192Z"
        }
      },
    ]
  }
}

Hope this helps!

Cheers!
Garrett

[bm11100 (Brent Murphy)]

Thank you, Garrett!! That is super helpful.

Is there a way to easily turn said query/variations into a visualization for a dashboard as opposed to just dev tools?

Been having a tough time with all the nested fields going on.

[spong (Garrett)]

Of course! Happy to help!

As for leveraging this data within visualization/dashboards, you should be able to create a Kibana Index Pattern (now called Data Views) within Stack Management, and then use that within Lens/Dashboards.

Kibana Index Patterns / Data Views

Create Data View
Note: Be sure to click Allow Hidden & system Indices, and ensure you select cases.created_at as the timestamp

Now you can use this KIP/Data View in Discover/Dashboards/Lens/etc!

And here was the data from within the Security -> Cases view:

Cheers!
Garrett

[bm11100 (Brent Murphy)]

Ahh it was the cases.created_at that I did not have! I was using timestamp and could not parse the data in lens. Thank you!