How to extract data from topic and send it to specific row?

Hi everyone, I am trying to figure out how to adapt the system to my needs.

I have telemetry coming to topics such as events/<source_id>, e.g., events/device_1, events/device_2, and so on.

I would like my spreadsheet to show me several things:

  • the time of the last message that came from each source, e.g. device_1
  • I want this cell to turn red if the last message is more than N minutes old

Because I have many sources of info (circa 400 devices), I want them to be created dynamically. The algorithm I envision might be like this:

- Split topic by "/" and extract the last element
- Go to the row that corresponds to this device
- Set the second column of that row to the timestamp (and apply color rules accordingly)

I’ve tinkered with Streamsheets and I found if very flexible, the concept is amazing! However, I haven’t figured out how I can adapt it to my needs, thus I ask for help.

Note: because the number of devices in my system is pretty much a constant, I would be OK with a one-time effort of creating some sort of a map, like device_1 -> row 1, device_2 -> row2, etc. I look forward to your hints.

1 Like

Moin Moin moinmoin,

Sorry about that joke :wink: .
I love your use case and I went ahead to solve it for the first 50 of your devices.
Check out the export I attached.

What did I do:

First, I simulated some devices in one sheet and added another which consumes the send data.

I used the metadata section of the inbox to check the “topic” and the “arrivalTime”.
image
In C4 the function MID() is used to get only the second part of your topic structure.
Streamsheets uses Excel time. So 1 equals 1 day. So 1 minute equals 0,0006944 (F2).

All I now did was to copy the “arrivalTime” of a device in a dedicated cell. Another cell now checks, if this time is now older than 1 minute and turns from green to red if that turns out to be true.

image

I added some $ signs to be able to simply drag the cells and still get the right results. Done :slight_smile:
The dragging also worked for “device1”.

https://aws1.discourse-cdn.com/standard14/uploads/streamsheets/original/1X/366b58b9e9096b3a7a31d31ac0f820931654c90a.mp4

Cheers,

Tizian

Status Check.json (46.1 KB)