Visualizations with conditionals to evaluate documents across an index

OpenSearch Dashboards
1

Hi Comunity! I’m recently joined to this. My name is Catriel and I (and my team) support one OpenSearch cluster deployed in AWS as ‘managed cluster’, ver. 2.15.

Now, I’m face on to a new challenge related to OpenSearch visualizations. First, say “Dashboards and visualizations scope is new to me”.

Below, details of scenario and request to solve.

We have one index with documents, each document contain information about transactions processed by our system.
One document contain information about one transaction.
This documents contain one unique ID per transaction (field ‘trxId’) and one status for this (field ‘trxStatus’) [booth fields as ‘keyword’ type].
Then, our system start to ingest documents for each transactions with status (‘PENDING’), this is the initial status for all transactions.
Usually, we have multiple documents for the same transaction with initial status ‘PENDING’.
Some time later, our system ingest a new document with status changed (‘APPROVED’ or ‘DECLINED’) for each transaction.

Finally, we need build a new visualization to ‘count’ transactions in the diferent status (‘PENDING’, ‘APPROVED’ and ‘DECLINED’), the logic for this will be:

  • For each ‘trxId’ count one transaction
  • For each ‘trxId’ with status ‘PENDING’ and without status ‘APPROVED’ or DECLINED’ count one “pending transaction”
  • For each ‘trxId’ with status ‘APPROVED’ count one “approved transaction”
  • For each ‘trxId’ with status ‘DECLINED’ count one “declined transaction”

I share example of our documents ingested as reference (one document per line):

@timestamp: “2025-04-03 21:19:01.000”, trxId: “001-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:02.000”, trxId: “001-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:03.000”, trxId: “001-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:04.000”, trxId: “002-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:05.000”, trxId: “002-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:06.000”, trxId: “002-000”, trxStatus: “APPROVED”
@timestamp: “2025-04-03 21:19:07.000”, trxId: “003-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:08.000”, trxId: “003-000”, trxStatus: “PENDING”
@timestamp: “2025-04-03 21:19:09.000”, trxId: “003-000”, trxStatus: “DECLINED”

then, the visualization will show the next values:

pending transaction = 1 (explanation: because for trxId=001-000 we have only ‘PENDING’ status, not ‘APPROVED’ or ‘DECLINED’ status for this one)
approved transaction = 1 (explanation: because trxId=002-000 adquire status ‘DECLINED’ in document with timestamp “2025-04-03 21:19:06.000”)
declined transaction = 1 (explanation: because trxId=003-000 adquire status ‘DECLINED’ in document with timestamp “2025-04-03 21:19:09.000”)

I started to think in the solution using some kind of coditions to evaluate documents across the index (e.g. if for each ‘trxId=XXX-XXX’; have documents with ‘trxStatus=PENDING’ and not have documents with ‘trxStatus=APPROVED’ or ‘trxStatus=DECLINED’; then count as ‘pending transaction’) … but I’m not shure if this is the correct way.

Any idea or sugestion will be great! Thanks so much.

2

I took your sample data and tried to make a visualization out of it. Unfortunately I didn’t get very far, as the visualization engines don’t quite have the necessary smarts to match whether the trxId has another entry with a different status. So, that is to say, I was able to get a stacked bar chart with proper counts of each PENDING, APPROVED, and DECLINED but was unable to implement the logic - it saw 7 PENDING, 1 APPROVED, and 1 DECLINED for the time period selected.

I really think the best way to approach this is to implement some kind of logic prior to generating your JSON. What might be interesting, if possible, is to add a date field for ‘APPROVED’ or ‘DECLINED’, as this would also give you additional metrics that you could watch - in particular the amount of time passed between switching from PENDING to either APPROVED or DECLINED.

Whatever logic you want to implement, I suspect it will have to be done prior to ingesting so that the records themselves contain the data you want to aggregate.

I hope that gets you going in the right direction, and of course I’m happy to be corrected by the rest of the community in case I’ve missed some feature that would allow this kind of visualization. :slight_smile:

1 Like