I’ve been trying to come up with a good way to visualize per Application devices statistics similar to how the tenant dashboard does for the whole tenant.
This was my initial idea for what I hoped to achieve:
I’m not sure if this would be useful for other people, but it would be great for how I use Chirpstack so I could see if devices in one application were not responding because I have devices all over the country and each application represents one location. So I could see if one location was having issues at a glance.
I was looking at the source code of ui/src/views/applications/ListApplications.tsx and adding the columns I want there would be easy, but getting the generated values in the application table in postgres would not be. Maybe someone has a better idea of how to get this into the GUI.
Here is the postgres query I’m using the generate my current report.
select public.application.name as App_Name, public.application.description,
Sum(
case when public.device.last_seen_at > (NOW() - (public.device_profile.uplink_interval * interval '1 second'))
then 1
else 0
end) as Active,
Sum(
case when public.device.last_seen_at < (NOW() - (public.device_profile.uplink_interval * interval '1 second'))
then 1
else 0
end) as Inactive,
Sum(
case when public.device.last_seen_at is null
then 1
else 0
end) as Never_Seen
from device
inner join public.application on device.application_id = application.id
inner join public.device_profile on device.device_profile_id = device_profile.id
group by public.application.name, public.application.description