# Creating Elasticsearch Complicated Data Table

**URL:** <https://discuss.elastic.co/t/creating-elasticsearch-complicated-data-table/221718>\
**Category:** Kibana\
**Created:** [March 2, 2020, 3:33pm UTC](https://discuss.elastic.co/t/creating-elasticsearch-complicated-data-table/221718 "2020-03-02T15:33:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![lmit](https://avatars.discourse-cdn.com/v4/letter/l/c68b51/32.png) [@lmit](https://discuss.elastic.co/u/lmit)\
**Post date:** [March 2, 2020, 3:33pm UTC](https://discuss.elastic.co/t/creating-elasticsearch-complicated-data-table/221718/1 "2020-03-02T15:33:22Z")

</div>

Hello, I am Looking to build the following report data table as a Elasticsearch Dashboard

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/2/7282846d8868b639356c1ce01387bea3ede5352d.png)

Using the following JSON data in Elasticsearch document(s)

```json
PUT canpi-v1-2020.03.01/_doc/004D58_5E5C0DA7 {
  "@timestamp": "2020-03-01T19:31:51Z",
  "@version": "1",
  "vehicle": {
    "id": "004D58",
    "alias": "731",
    "comment": "comment for LMIT TEST",
    "swver": "3.2.5",
    "channel": "dev-testing",
    "last_reported_sps": {
      "unix": 1583124282000,
      "gmt": "2020-03-02 04:44:42",
      "human_utc": "Mon Mar 2, 2020 @ 4:44 AM UTC"
    },
    "sensors": [
      {
        "loc_name": "1A",
        "sensor_id": "2301809C",
        "sensor_type": "tpms",
        "status": {
          "lost": "no",
          "summary": "Warning",
          "details": "No Leakage - Low Pressure Warning - Temperature OK - 1400 kPa Sensor (default) - Battery Voltage OK",
          "color_state": "orange"
        },
        "stdtemp": {
          "c": 22,
          "f": 71.59
        },
        "stdpressure": {
          "psi": 32.69,
          "kpa": 225.5
        },
        "basepressure": {
          "psi": 59.81,
          "kpa": 412.5
        }
      },
      {
        "loc_name": "1B",
        "sensor_id": "23018193",
        "sensor_type": "tpms",
        "status": {
          "lost": "no",
          "summary": "Warning",
          "details": "No Leakage - Low Pressure Warning - Temperature OK - 1400 kPa Sensor (default) - Battery Voltage OK",
          "color_state": "orange"
        },
        "stdtemp": {
          "c": 22,
          "f": 71.59
        },
        "stdpressure": {
          "psi": 32.69,
          "kpa": 225.5
        },
        "basepressure": {
          "psi": 59.81,
          "kpa": 412.5
        }
      },
      {
        "loc_name": "6A",
        "sensor_id": "2A014770",
        "sensor_type": "temptrac",
        "status": {
          "lost": "no",
          "summary": "Good",
          "details": "No Leakage - Low Pressure Warning - Temperature OK - 1400 kPa Sensor (default) - Battery Voltage OK",
          "color_state": "green"
        },
        "stdtemp": {
          "c": 22.25,
          "f": 72.05
        }
      },
      {
        "loc_name": "6B",
        "sensor_id": "2A014860",
        "sensor_type": "temptrac",
        "status": {
          "lost": "yes",
          "summary": "Error",
          "details": "LOST SENSOR DETECTED - Sensor Unreachable",
          "color_state": "red"
        },
        "stdtemp": {
          "c": "-1",
          "f": "-1"
        }
      }
    ]
  },
  "event": {
    "id": "5E5C0DA7",
    "timestamp": {
      "unix": 1583091111000,
      "gmt": "2020-03-01 19:31:51",
      "human_utc": "Sun Mar 1, 2020 @ 7:31 PM UTC"
    }
  },
  "stats": {
    "comm_failures": {
      "color_state": "red",
      "total": 3
    },
    "comm_retry_attempts": {
      "color_state": "green",
      "total": 0
    },
    "comm_invalid_read": {
      "color_state": "green",
      "total": 0
    }
  },
  "ecu_config": {
    "exists": "yes",
    "hardware_model": "QY1195-896",
    "receiver_type": "OTR Device",
    "software_version": "v1.2",
    "unit_mode": "ECU Serial Port Closed",
    "sp_interval_min": 0,
    "temp_warning_threshold": {
      "c": 88,
      "f": 190.4
    },
    "storage_save_time_min": 15,
    "max_num_tires": 24
  }
}

```

Please Note:

- The table data would mostly be generated from "vehicle", but I would be filtered by **"event.timestamp.unix over the last 24 hours, where ecu\_config.exists = yes"**

- I have total control over the JSON data format, AND how to normalize the organization of documents into Elasticsearch for indexing

Could support help me to accomplish this?

Thank you

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [March 2, 2020, 4:03pm UTC](https://discuss.elastic.co/t/creating-elasticsearch-complicated-data-table/221718/2 "2020-03-02T16:03:06Z")

</div>

This look like a continuous transform usecase to me. If I understand correctly you have a constant stream of new data coming in and want to get the last state by vehicle. A similar question came up today: [Latest full document in transform](https://discuss.elastic.co/t/latest-full-document-in-transform/221435)

To get an idea I suggest to watch this [webinar](https://www.elastic.co/webinars/introducing-data-frame-transforms-for-elastic-machine-learning). It goes much beyond your original case. But even if you "only" want a table like shown, you might want to calculate trends (e.g. value based on the last 5 data points).

Alternatively you could directly search and aggregate instead of transforming 1st.

---

<div class="post-metadata">

**Author:** ![lmit](https://avatars.discourse-cdn.com/v4/letter/l/c68b51/32.png) [@lmit](https://discuss.elastic.co/u/lmit)\
**Post date:** [March 2, 2020, 7:09pm UTC](https://discuss.elastic.co/t/creating-elasticsearch-complicated-data-table/221718/3 "2020-03-02T19:09:20Z")

</div>

Excellent! Thank you for giving me someplace to start

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 30, 2020, 7:09pm UTC](https://discuss.elastic.co/t/creating-elasticsearch-complicated-data-table/221718/4 "2020-03-30T19:09:29Z")

</div>

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