Postgresql integration rx_info not inserted/available

ChirpStack Application Server
1

First of all I think the the Application Server events PSQL integration is great, thank you.
My question is related to the state of the rx_info column in the device_up relation, currently it is ‘null’.
Is this an issue on my end or is that part of the integration not complete?

My current remedy is listening to gateway mqtt messages and updating device_up rows with some backend logic, it’s not elegant.
Note that I’m running Docker containers and [integration]marshaler=“json” gateway-bridge configuration.

I would like to visualize the rx_info contents with Grafana, other information is working correctly.

Thanks again,
Sandro

2

Hi Sandro,
I believe the only thing you need to do is to check the “Add gateway meta-data” option in service profile you are using.

Rx info is then stored as json structure in that field.

Btw I would love to see how to do that, I have both Grafana and postgre integration installed, but don’t really know how to work with them.

3

Thanks for your answer, solved my problem! It seems like reading is not my forte :slight_smile:
I’ll post an example for the Grafana psql query here a bit later.

4

image

image

Notice that I’m using varibales like $dev_eui, they are useful so you can have the same graphs for your whole device fleet. You then have a select list at the top of the view to choose your device.
My variable is misnamed, it should be $device_name

1 Like
5

I had no idea how to filter devices and column values I wanted. This is just what I needed, so thank you very much!

6

Great that we could help eachother!

image
image.png1360×409 35.2 KB

image
image.png1326×434 39 KB

The variables are in Dashboard settings.

7

hi everyone,
i will be made with the json language…

i’m now near to print the time series of 2 envirnmental lora node. BUT the last thing to do is to convert my “jsonb” object into a numeric value.

As i use a codec in the application server to store the “data” , in my postgre DB i have something like that:

"
{“DecodeDataHex”: {},
“DecodeDataObj”: {
“battery”: “3.64V”,
“environment”:
{“humidity”: “49.0% RH”,
“barometer”: “965.90hPa”,
“temperature”: “20.30°C”,
“gasResistance”: “3.20KΩ”
}}}"

so i cant convert to “numeric” the text as it contain some text charactere as [°C; KΩ,hPa etc…]
in my query i have somethings like :
SELECT (Column: object -> ‘DecodeDataObj’ -> ‘environment’ ->> ‘humidity’) :: numeric

Sure i could change the codec, but this is not the question…
sure i could use influxDb but still the same answer…

i read the https://www.postgresql.org/docs/9.3/functions-json.html
but i understand … something like … not so much…

thanks

8

store in the db only numeric data without text addition names of measurement units. otherwise it will be text data. which makes difficult to work with values

9

why we work with json object so???

And futhermore as my data is encoded… (all environmental data are in a chain of byte )
so after that i don’t know how to split it into different column for a database storage…

substring is not a good way???

10

f…cking informatic langague …
4 hours to found this:

substring ( (object → ‘DecodeDataObj’ → ‘environment’ ->> ‘humidity’) , 0 , 4) ) :: numeric

it seem to works!

what do you thing about use this kinf of method ???

11

if your hex data is the pure text string with special symbolic names of values, you are need to use the substr. if not, rewrite the codec without adding °C; KΩ,hPa etc…
use the construction like this js-example:
decoded.temperature = bytes[offset];
offset += 1; // bytes offset depends on your payload structure
decoded.humidity = bytes[offset];
return decoded;

12

It make sense!
Principaly for storage capacity and simplicity of query.

Thanks for your answer.

13

@Sandro
the tricks with the array to SELECT the RSSI helped me !!!
go to know!