Stat panel showing last value, not current

[schlauch]

• Grafana 7.5

• What are you trying to achieve?
  we count the amount of servers and monitor the amount still in place. Datapoint each month.
  stat panel works perfect, shows history also. However - when there was no server found, no data is added to DB with timestamp, postgres query doesnt return data for this.
  Example: we had two servers 3 months ago and zero servers since last month, the stat panel does not show current value of 0. I’ve tried messing with postgresql coalesce, but that didnt work for me

• How are you trying to achieve it?

SELECT
$__timeGroup(“reportdate”,‘1M’),
count(hostip) as “Servers”
from tb_servers
WHERE operatingsystem = “Windows”
group by reportdate
order by reportdate

any ideas? I am sure I’m njot the only one with this issue, but I cant find how others resolved it. I looked at overrides, changing sql query, but the main issue seems to be that grafana cant show whats not there. i was hoping I could just select ‘current’ in stat panel instead of ‘last’ and grafana populating it with 0 or N/A if nothing there

Thank you so much!

[schlauch]

I’ve managed to figure out how to solve it, with adding 0 as parameter to timegroup
$__timeGroup(“reportdate”,‘1M’)
change
$__timeGroup(“reportdate”,‘1M’,0) --so it uses 0 if there’s no data

but for some weird reason one of my months does not show any data for any of the graphs, even though the data is there

I have a feeling this issue arises from __timegroup not using the actual days of the month (i only have data on each first of month).

like I said, all my data is at first day of month only