# Troubleshooting on a data table with aggregation in Kibana **URL:** \ **Category:** Kibana\ **Created:** [January 23, 2020, 10:56am UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220 "2020-01-23T10:56:23Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [January 23, 2020, 10:56am UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/1 "2020-01-23T10:56:23Z")
Hello, I'm quite new to Kibana and ES, so my question might be a bit naive, I apologize in advance! I have indexed data on production test lines and I would like to compute (and then use) some specific values. Quick description of the data: - a tested product is identified with a serial - if the first test fails, this product can be tested on different test lines, up to 4 times (after which it is considered faulty) - all tests are timestamped (obviously) Let's begin with two basic needs: 1. I am able to count the number of products, but I would like to break down this dimension by test line by keeping only the first test for each product (so that the sum of tested products on each test line equals the total number of products) 2. I was able to display the number of tests carried out on each product, but now I would like to display the number of products with only 1 test (which corresponds to the "first pass yield" products), ideally broken down by test lines I guess the two questions both come from my relative ignorance about nesting aggregation, so any help would be greatly appreciated for me to overcome this initial obstacle 🙂 ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [January 23, 2020, 11:44am UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/2 "2020-01-23T11:44:49Z")
You don't have to apologize for naive questions. Guess it's perfectly normal for any beginner in any fields. What you should apologize for, is not giving us more details about your use case 🤣 Joking apart, what is the structure of your documents? Can you paste here a sample json of one of your doc (maybe without all those fields not useful for this thread's purpose)? Also, can you post here an image (even a sketch) of what you'd like to achieve with the data table? Thank you ad welcome to the community! ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [January 23, 2020, 1:40pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/3 "2020-01-23T13:40:47Z")
OK, not sure how to post JSON in a clean way but here it is: ``` { "mapping": { "properties": { "metadata": { "properties": { "equipment_code": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "global_status": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "product_serial": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "product_variant": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "seq_duration": { "type": "float" }, "tstamp": { "type": "date" }, "week": { "type": "long" } } }, "num_value_real": { "type": "float" }, "str_value_real": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "subtest_duration": { "type": "float" }, "subtest_id": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "subtest_status": { "type": "text", "fields": { "keyword": { "type": "keyword" } } }, "test_type": { "type": "text", "fields": { "keyword": { "type": "keyword" } } } } } } ``` Let me further explain the structure: - each file of my corpus corresponds to a global test of a given product on a given test line - each tested product has an unique serial (product\_serial) - each tested product belongs to a given variant (product\_variant) - each test line has an unique id (equipment\_code) - each test is timestamped, has a duration (seq\_duration) and an outcome (global\_status), which depends on the outcome of all subtests (subtest\_id, subtest\_status) - each subtest has a type (test\_type), a value (num\_value\_real or str\_value\_real according its type), a duration (subtest\_duration) and an outcome (subtest\_status) For my previous questions, the subtest part is not relevant. I will further explain what I am trying to achieve ASAP! ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [January 23, 2020, 1:43pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/4 "2020-01-23T13:43:00Z")
Sorry I didn't mean the mapping (which can be useful anyway). I meant a document. Just make a simple \_search call (`GET your_index/_search`) in the Dev Tools and paste here one of the returned documents on the right. Also, you can go in the Discover section, select your index pattern, highlight the fields you're interested in (in the column on the left move the cursor over one field and click on the `Add` label appearing next to it, so to add it as a column in the main section ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/3/d33f4cc3a9e77ba20fd59b47b2395d4f489f3530.png) ) and take a screenshot of the main part, so we can have an idea of how your data are structured. ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [January 23, 2020, 2:47pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/5 "2020-01-23T14:47:58Z")
Here is a discover screenshot of my data: [![Imgur](https://us1.discourse-cdn.com/elastic/original/3X/6/2/62cb76e1b06920f397ca60619858cf7bb5845e1a.jpeg "Imgur") ](https://imgur.com/mJNDenX) For your information, the 2 different statuses are either 'B' (for OK) or 'M' (for NOK), I will change it to a boolean later. Here, you can see that the global test fails since the subtest P000507 has failed. Now, for the intended visuals: in both use cases, I imagine a vertical bar chart on which each bar corresponds to a parameter value on different weeks of year and each bar is composed of several colors 1. Use case 1: the bar represents the number of products which have been FIRST tested during the week (so the different colors of the bar represent the number concerning each test line) 2. Use case 2: for a given week and a given test line, the bar represents the number of products which are FIRST tested on this line, with 2 colors corresponding to the success / fail global status I insist on the "FIRST", because otherwise I would be counting too many products (namely the ones which failed at least once and were retested on a different test line) Hope I've been more accurate in my explanations than before 😛 ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [January 23, 2020, 3:08pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/6 "2020-01-23T15:08:42Z")
> Just make a simple \_search call ( `GET your_index/_search` ) in the Dev Tools and paste here one of the returned documents on the right. I have to insist to get a subset of your data so I can make a test and see if I can reproduce what you're trying to achieve. Put in fake data if they are sensitive, but provide me with something to easily replicate your environment. A ready to use [bulk API](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-bulk.html) would be perfect to create a starting point. Also, why do all the docs have the same timestamp and how can you identify the first one if so? Again, if you can make even a hand drawn graph of what you'd like to build, it'd be extremely useful. FYI, to paste an image it is sufficient to take the screenshot, copy it in your clipboard and simply CTRL+V it in the post while writing, without the need to redirect users to external pages with cookies and stuff 😉 Another note (I'm editing this post while reading through all yours): if I got it right, what you call `test_line` is what is represented as `equipment_code` and what you show in your screenshot is the result of the test over a single `equipment_code`, which is a FAIL even if only one of the subtests for that `equipment_code` is a M, right? If that's the case, wouldn't it be more appropriate to store a document for each test or at least for each equipment\_code (aka `test_line`) with all the values of the subtest as part of it? It does depend on what you're about to do with your data but for the types of visualization you're asking here, you need a more aggregated data in order to avoid complex (and sometimes not feasible) computations. ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [January 23, 2020, 4:08pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/7 "2020-01-23T16:08:44Z")
Sorry for the data, here is some example: ``` { "_index" : "my-index", "_type" : "_doc", "_id" : "dpDR0W8B_GqiKl5gL8QY", "_score" : 1.0, "_source" : { "@timestamp" : "2020-01-23T09:52:24.856Z", "subtest_duration" : 336.931, "path" : "my-file.TXT", "subtest_name" : "BTN ADC Analog Open", "num_value_real" : 2.017, "test_type" : "Analogic", "metadata" : { "equipment_code" : "E0023", "global_status" : "B", "from_app_start" : 375771.044, "product_version" : "my-product-version", "seq_duration" : 159.953, "product_serial" : "4637054", "week" : 46, "product_variant" : "23783", "tstamp" : "2019-11-12T20:47:49.000Z" }, "subtest_status" : "B", "subtest_id" : "P004401", "from_test_start" : 94.573, "test_name" : "ID23 : Buttons Interface" } } ``` I will investigate the bulk API later, as I prefer to focus on providing the information (even if in a messier way 😅 ) > [@Fabio-sama](#): > > Also, why do all the docs have the same timestamp and how can you identify the first one if so? You are perfectly right, only the global test is timestamped, the subtests are not (the order is not important at the moment, but you can find it through the `subtest_id`) > [@Fabio-sama](#): > > FYI, to paste an image it is sufficient to take the screenshot, copy it in your clipboard and simply CTRL+V it in the post while writing, without the need to redirect users to external pages with cookies and stuff 😉 Thanks for the tip ! 🙂 > [@Fabio-sama](#): > > Another note (I'm editing this post while reading through all yours): if I got it right, what you call `test_line` is what is represented as `equipment_code` and what you show in your screenshot is the result of the test over a single `equipment_code` , which is a FAIL even if only one of the subtests for that `equipment_code` is a M, right? Totally correct > [@Fabio-sama](#): > > If that's the case, wouldn't it be more appropriate to store a document for each test or at least for each equipment\_code (aka `test_line` ) with all the values of the subtest as part of it? > It does depend on what you're about to do with your data but for the types of visualization you're asking here, you need a more aggregated data in order to avoid complex (and sometimes not feasible) computations. I see what you mean! I must confess I didn't think about it but it would make a lot more sense. I will try to modify my logstash config file in order to conform to your proposition. Just hoping I will be logstash-proficient enough to do so 😛 I will work on it ASAP and report back to tell you if it helped. I will also attempt to draw what I intend as Kibana visuals ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [January 30, 2020, 4:58pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/8 "2020-01-30T16:58:54Z")
Hello, I have been quite busy trying to figure out the best index mapping for my data, but I guess it is not entirely relevant for my issue. I have therefore simplified the indexed data, I'm pretty confident I will be able to adapt the views with a more complex index mapping. Anyway, here is a new data example: ``` { "_index" : "my_index-2020.01.30", "_type" : "_doc", "_id" : "Dju39W8Byf-Hbilm27G8", "_score" : 1.0, "_source" : { "product_serial" : "1234567", "global_status" : "B", "tstamp" : "2019-11-12T17:38:33.000Z", "product_version" : "my version", "equipment_code" : "E0021", "date" : "11-12-2019", "from_app_start" : 44413.381, "@version" : "1", "seq_duration" : 176.959, "@timestamp" : "2020-01-30T09:11:04.892Z", "product_variant" : "12345", "path" : "my-parsed-filepath.txt", "host" : "my-server", "week" : 46, "time" : "18:38:33", "year" : "2019" } } ``` The associated discover screenshot: ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/c/dcd0819c71c04f1ba8a1f833b55e9f85fd58d3c5.png) Finally, here are two graph examples of what I would like to get for my 2 use cases (see previous post) : 1. Use case 1 : ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/f/ef715e7292b475ba6e72d718a20169769b847fad.png) 2. Use case 2 : ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/7/97440a4806c0fb854f27bc8e177c519dad2bd944.png) ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [February 5, 2020, 6:15pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/9 "2020-02-05T18:15:31Z")
Hello, Me again. In order to describe more precisely what I don't manage to do, here is a data table: ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/d/fdfb093eb5dfb6484f903df53922e389d790b37a.png) You can see that I used a row split on the timestamp, reducing the results to only the first occurrence of each serial (since I am interested **only** in the first test for each product). The total count of unique tests marked as correct (global\_status : "B") is 1605 (out of 1666) Now what I want to do is replicate this behavior with an vertical bar chart (use case 2). This is what I managed to get: ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/1/a15d32c5777fd0f8b3a7f8eec313a70aa91a60c2.png) The problem here is that I get 1650 for the correct tests, obviously because I wasn't able to take into account only the first test for each product... So here is my question: does anybody know if it is possible to get such an histogram with Kibana? And how? Thanks in advance for all people brave enough to delve into this 🙂 ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [February 10, 2020, 3:54pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/10 "2020-02-10T15:54:54Z")
Hi there, sorry for disappearing but I've been quite busy. And I still am, but I'll read everything as soon as I can. Just one question, why are you ingesting all the additional data if you're interested in only the first one? And sorry for the question (it's most likely written somewhere) but by "first" you mean the first of the week right? I mean, if on Monday of week1 you have a serial `xyz`, then you will ignore all the `xyz` product up to the Sunday of the week1. But if a product `xyz` appears in week2 it has to be taken into consideration (obviously the first occurrence of week2), right? ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [February 10, 2020, 5:35pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/11 "2020-02-10T17:35:12Z")
Hi Fabio, No problem for the delay, it's already great to have someone to interact with 🙂 > [@Fabio-sama](#): > > Just one question, why are you ingesting all the additional data if you're interested in only the first one? Because it depends on the visualization: some (the most important, eg the [first pass yield](https://en.wikipedia.org/wiki/First_pass_yield)) are focused on the first occurrences of tests, others are not (in order to drill down any potential issue) > [@Fabio-sama](#): > > And sorry for the question (it's most likely written somewhere) but by "first" you mean the first of the week right? > > I mean, if on Monday of week1 you have a serial `xyz` , then you will ignore all the `xyz` product up to the Sunday of the week1. But if a product `xyz` appears in week2 it has to be taken into consideration (obviously the first occurrence of week2), right? "First" means the first of all time, period. In fact, there cannot be 2 products with the same serial (serial=unique id). A given object has a unique serial and goes through a series of tests between one and 4 times (over a short period of time, let's say 1 day max) : - as soon as the overall result is good, the object exits the test line labelled as good to sell - if after 4 tests the overall result is still incorrect, the object is labelled as flawed ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [February 11, 2020, 10:46am UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/12 "2020-02-11T10:46:38Z")
Ok so, what about "duplicate" the data over two indices like "whatever-index" and "whatever-index-unique"? On the former you could put all your data (useful for drilldown purposes) while on the latter you basically use the `product_serial` as `document_id` and simply don't ingest a document if a doc with that same id is already in the index. That way you could make an index pattern like `whatever-index*` and in the visualization that requires document to be unique you filter in the search bar by `_index:whatever-index-unique`. Might that be an idea to simplify whatever you have to do? ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [February 11, 2020, 11:18am UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/13 "2020-02-11T11:18:09Z")
I guess this could work (even if I thought this could have been feasible directly through Kibana manipulations) ... However I'm not sure how to do so, I suppose this can be done through logstash? Or do I simply have to modify the ES index? ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [February 11, 2020, 11:28am UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/14 "2020-02-11T11:28:52Z")
This might be done directly in Kibana (even though I need time to figure out your situation and I simply don't have it right now) but if adding an additional index is not a problem for you, I'd definitely go with that solution. It's way lighter from a computational point of view since you do not make any complex aggregation when browsing a dashboard and not so heavy from a storage p.o.v. since you're building an index of unique entries. For sure it also depends on your specific case. But it's generally easier to run out of computational power than raw storage. What's the amount of data in this case (how many product serial per day and how heavy is each doc)? Anyway, how are you ingesting data right now? Directly into ES? From where? ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [February 11, 2020, 1:11pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/15 "2020-02-11T13:11:30Z")
For the moment, storage is not a problem since it is a proof of concept! However, I will ask my colleagues for the daily volume of raw data, just to be sure... Currently, I am using Logstash to parse my log files (directly from a local directory, since it is a POC...) ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [February 11, 2020, 1:56pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/16 "2020-02-11T13:56:38Z")
Ok then, ask your colleagues but generally storage is easily to ask for rather than computational power. Anyway, when I have time I'll try to think a good solution to achieve it in Kibana from a unique index. As for the ingestion, if you're already using logstash you can do something like this in the output section: ``` output { elasticsearch { hosts => "localhost:9200" index => "whatever-index" } if [product_serial] { elasticsearch { hosts => "localhost:9200" index => "whatever-index-unique" document_id => "%{product_serial}" action => "create" } } } ``` this way you'll insert the event in the `whatever_index` with a random id and the same event in the `whatever-index-unique` with the `product_serial` as id (so it won't be written if already there) if the event has the `product_serial` field. ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [February 11, 2020, 2:26pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/17 "2020-02-11T14:26:55Z")
OK, I tried it and it works! 🤩 I only had to be careful on the order of the input files, just to be sure to handle the oldest first. Since the filenames are labelled with a timestamp, I just had to use `file_sort_by => "path"` in the `input` filter Thank you very much for your help, Fabio! Should I tag your previous answer as a solution, even if it is done outside of Kibana? ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [February 11, 2020, 2:41pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/18 "2020-02-11T14:41:06Z")
Glad it works 🙂 Anyway, for now you can tag this one as a solution > Ok so, what about "duplicate" the data over two indices like "whatever-index" and "whatever-index-unique"? On the former you could put all your data (useful for drilldown purposes) while on the latter you basically use the `product_serial` as `document_id` and simply don't ingest a document if a doc with that same id is already in the index. > > That way you could make an index pattern like `whatever-index*` and in the visualization that requires document to be unique you filter in the search bar by `_index:whatever-index-unique` . > > Might that be an idea to simplify whatever you have to do? If I manage to think of something we might update the post later. ---
**Author:** ![AxelR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/axelr/32/61269_2.png) [@AxelR](https://discuss.elastic.co/u/AxelR)\ **Post date:** [February 11, 2020, 2:42pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/19 "2020-02-11T14:42:36Z")
Done! Thanks again 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 10, 2020, 2:42pm UTC](https://discuss.elastic.co/t/troubleshooting-on-a-data-table-with-aggregation-in-kibana/216220/20 "2020-03-10T14:42:41Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.