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:

    {
    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.

I am not sure how to do it.
I would consider maintaining another index just for the last status.

Yeah we have thought of workarounds like that however it would be ideal if there was a method to do this with Kibana.