Kibana visualization built with context of other logs

Hello -

My team is using ElasticSearch/Kibana for security metric related things and we are having trouble with a specific use case:

When a finding is open, a document is created with alerts.status:open associated to the UUID of a finding.id. When that finding has been closed, another document is created with an updated status of 'resolved' with the same finding.id.

We are having trouble accurately visualizing the total count of OPEN findings - i.e. Unique count of findings.id where there is a document for open but there does not exist a document for resolved.

We tried a KQL query like the following however this doesnt work as expected in every case.

  • findings.id:* AND (alerts.status:open AND NOT alerts.status:resolved)

The goal of this would be to get the unique count of findings.Id where there doesn't exist a document for that findings.Id where status=resolved.

Sample Payload:

Copy to clipboard

    {
    findings.id: "1"
    alerts: {
        status:open
      }
    }

    {
    findings.id: "1"
    alerts: {
        status:resolved
      }
    }


    {
    findings.id: "2"
    alerts: {
        status:open
      }
    }

The count here should be 1 but we get the value of 2 -> unique count findings.ids without the context of the resolved document for findings.id: 1

Does anyone have a solution for this or something similar? Happy to provide clarification where needed or anything that would help point us to the correct solution.

The reason the query doesn't work is that's per document. So you'll never have a single document per ur schema with alert.status as open and resolved. What ur trying to do is like a subquery in sql. I know some sql like queries are possible but idk about that.

1 Like

You need to use a transform to evaluate the status per finding. Based in that you could do your visualization.

1 Like

Thanks Felix - We are using the AWS Managed Elasticsearch version which does not have transforms until later this quarter hopefully.

Do you or have you seen an Aggs query that could solve for this that we could potentially use in a Vega or Kibana viz?

Hey @mbrad
transforms are part of the free basic license. So it will never be available in AWS .
If you would like to use it you should consider using Elastic Cloud which is also available on AWS but offers full Elastic functionality and support. Its a very different product to what you get from Amazon.

2 Likes

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.