Creating Elasticsearch Complicated Data Table

[lmit (Lance Mitchell)]

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

Using the following JSON data in Elasticsearch document(s)

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

[Hendrik_Muhs]

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

To get an idea I suggest to watch this webinar. 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.

[lmit (Lance Mitchell)]

Excellent! Thank you for giving me someplace to start