I have an use case where we're are suppose to establish status monitoring for certain entities. Business case looks like this: There is a table containing two columns: ID along with their Status. Status can be one of three values: New, In process or Completed. Our business users would like to see in Kibana a list of ID with status New or in Process. Once ID will change it's status to Completed, it's supposed to be filtered out by Kibana.
Unfortunately there's no timestamp based on which i could update Status of recently changed ID's in Elasticsearch, so the only option would be to query an entire table and update an entire index based on ID of each entry. This might be ineffective, since there are around 3mln of records and we need to refresh it every minute. Another approach would be to use Logstash to get only New and In Process ID's and put them in Elasticsearch index, but we would need to remove all documents from an index first, and then upload newly downloaded results. Every minute.
I would appreciate a hint of an effective way of handling such case. There will more in the future, with much bigger tables where we will need to see only a small chunk of data refreshed quite often.
Thanks in advance.
Additional note: we are using Logstash for our pipelines. For oracle, we are using jdbc to fetch the results.