Count the number of occurrences for each status only considering the last registred status - Table visualization

Elastic Stack Kibana
1

Hi there! Could you help me with a question, please?

I have an application that sends logs to elasticsearch to log and track the status of a purchase.

Example:

{"idcode": "a1", "order_status":"approved","timestamp": 1563041808958}
{"idcode": "a1", "order_status":"under_analysis","timestamp":1563041793374}
{"idcode": "a2", "order_status": "waiting_payment","timestamp":1563041760591}
{"idcode":"a1", "order_status":"waiting_payment", "timestamp":156304110293}

I would like to make a visualization on kibana that shows how many purchases are in a certain status without counting the status that a purchase already had.

for example:

Status Tracking Table

image

I've already tried creating a table where my metric is "Count" and bucket has aggregation "Terms", field is order_status.

When I do this I get the view I want, but it does not only count the last purchase status, but all the statuses a purchase has already had. This way the view goes out wrong

Just to explain:
Let's image that the first log was
{"idcode":"a1", "order_status":"waiting_payment", "timestamp":156304110293}.

and we have another log line immediately after the first one that is
{"idcode": "a1", "order_status": "under_analysis", "timestamp": 1563041793374}

I am trying to create a table visualization which show something like this

image
But currently I have this:

image
That is, it is computing the old and new status

Also, I've already tried creating a table where my metric is "top hit", the field is order_status , sort onis time_stamp and buckets with aggregation:terms, field:idcode. But it just shows the lastest state by idcode, in others words it does not work as the visualization that I want.

Could you help me with this question?

any help will be appreciated!! :smiley:

Thank you very much!

2

Hi there ... I helped on a very similar use case tracking orders, order status etc and we solved it a bit differently. This is just a suggestion as I suspect an "Aggs Expert" may be able to help you too but this user really liked this solution.

Turns out that this use cases wanted details for each order, order step, but also wanted to see running aggregates or number of orders in each status, total value in the pipeline etc. much like you are describing.

What we did is create 2 indexes, there is some overhead in indexing and storage but tradeoff very easy / fast aggregations and visualizations etc. Even using canvas with the SQL queries became very easy.

The 2 indices are:

  1. Detail : An index with the individual entries, this is the index you have already
  2. Lastest: An index that just kept the latest entry, this made all the aggregations and visualizations much easier. Example your visualization you are describing above would work out of the box just how you want it. Calculating total value in the pipeline was a snap.

So IF your orderid is truly unique / GUID etc you could do something like this on ingest.

The first will keep all the details so you can see all the states a particular order went through etc.

The second output does an doc_as_upsert and so only keeps the latest document, using this you will be able to easily do the aggregation / visualizations that I think you are looking for.

This is a logstash conf, you can do similar via the REST API.

output {

  stdout {codec => rubydebug}
  
  elasticsearch {
      hosts => ["http://localhost:9200"]
      index => "orders-details-%{+YYYY.MM.dd}"
  }

  elasticsearch {
      hosts => ["http://localhost:9200"]
      index => "orders-latest-%{+YYYY.MM.dd}"
      document_id => "%{orderid}"
      doc_as_upsert => true
      action => "index"
  }

}
3

@stephenb yes this can be a good solution, just need to clone the event to use it in the second output
I have tried also, in a use case,the aggregate filter

4

Thanks @ylasri for the confirmation but I think it is unnecessary to need to use clone with the above logstash it should work as I have it above, it sends each event to both outputs without clone perhaps I am missing something but we have that working today as it is written.

5

@stephenb, thank you very much for your answer! I really appreciated your suggestion. Indeed was the solution that I decided to apply here in my context.

Now I have two indexs. The newest is just to retrive the lastest status result for each order.

Thank you and all the beast.

1 Like
6

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