I have a software that retrieve data from multiple devices with SNMP. The software then create a record in a table with a starting polling time and a ending polling time.
For every SNMP table we retrieve, we put the data in different tables.
In the model below, there is one snmp table (FrequencyValue). The tables are like so:
| Polls | | |
|-------|------------------|------------------|
| id | start_time | end_time |
|-------|------------------|------------------|
| 1 | 2019-04-01T10:00 | 2019-04-01T10:10 |
| 2 | 2019-04-01T11:00 | 2019-04-01T11:10 |
| 3 | 2019-04-01T12:00 | 2019-04-01T12:10 |
| Devices |
|------------|
| ip |
|------------|
| 172.16.1.1 |
| FrequencyValue | | | | |
|----------------|---------|------------------|-----------|-------|
| device_ip | poll_id | timestamp | frequency | value |
|----------------|---------|------------------|-----------|-------|
| 172.16.1.1 | 1 | 2019-04-01T10:02 | 1000 | 10 |
| 172.16.1.1 | 1 | 2019-04-01T10:02 | 2000 | 20 |
| | | | | |
| 172.16.1.1 | 3 | 2019-04-01T12:02 | 1000 | 10 |
| 172.16.1.1 | 3 | 2019-04-01T12:02 | 2000 | 20 |
The problem come when a device fails to answer, because as shown in the table FrequencyValue, the software didn't create a record for that table.
The question: In order to graph the column value per frequency, we would like to create n rows for each frequency that would have the column value to null.
So far, our query look like so:
select p.ip, coalesce(t.timestamp, p.start_time) as "time", t.frequency, t.value
from FrequencyValue as t
right join (
select p.start_time, p.id, d.ip
from Polls as p, Devices as d
) as p on (t.poll_id = p.id and t.device_ip = p.ip)
With output:
| ip | timestamp | frequency | value |
|------------|------------------|-----------|-------|
| 172.16.1.1 | 2019-04-01T10:02 | 1000 | 10 |
| 172.16.1.1 | 2019-04-01T10:02 | 2000 | 20 |
| | | | |
| 172.16.1.1 | 2019-04-01T11:00 | null | null |
| | | | |
| 172.16.1.1 | 2019-04-01T12:02 | 1000 | 10 |
| 172.16.1.1 | 2019-04-01T12:02 | 2000 | 20 |
What we actually want is this:
| ip | timestamp | frequency | value |
|------------|------------------|-----------|-------|
| 172.16.1.1 | 2019-04-01T10:02 | 1000 | 10 |
| 172.16.1.1 | 2019-04-01T10:02 | 2000 | 20 |
| | | | |
| 172.16.1.1 | 2019-04-01T11:00 | 1000 | null |
| 172.16.1.1 | 2019-04-01T11:00 | 2000 | null |
| | | | |
| 172.16.1.1 | 2019-04-01T12:02 | 1000 | 10 |
| 172.16.1.1 | 2019-04-01T12:02 | 2000 | 20 |
We tried putting another right join in the query but we just can't have the result we want.
The frequencies are not defined elsewhere, so to retrieve the frequencies available we could select distinct the frequencies in the table.
The DB is a PostgreSQL.
Use a cross join to generate the rows and then a left join to bring in the values:
select d.ip, t.timestamp, f.frequency, fv.value
from devices d cross join
(select distinct timestamp from frequencyvalue) t cross join
(select distinct frequency from frequencyvalue) f left join
frequencyvalue fv
on fv.device_ip = d.ip and
fv.timestamp = t.timestamp and
fv.frequency = f.frequency;