Issue in representing data because of multiple data sources

Hello all,

I am using multiple data sources namely Prometheus and Postgres.

I have some 300 servers. I want to keep track of that like CPU usage, and memory usage, and all other metrics. So I thought of installing node exporter in all servers and using Prometheus I want to pull metrics. I installed node exporter in 270 servers. But for the remaining 30 servers, I didn’t install node exporter as those servers are in an Unreachable state. I am not able to log in or do SSH for those servers.

So finally I had 300 servers IP in the Postgres database but in Prometheus, I had only 270 server details.

Using outer join transformation I am able to represent the Postgres and Prometheus data in the table panel as IP is the common field in both the data sources.

Problem Statment:
I want to know how many servers are reachable and how many are unreachable.

I thought the Prometheus query up{instance=~“$instance”} will give the reachability status of the servers.
I created a custom variable Reachability and added a key-value pair as below:
Variable name: Reachability
Key-Values: All : ,Reachable : == 1,Unreachable : == 0
Prometheus Query: up{instance=~“$instance”} $reachability
And in transformations, I added that the null value rows should disappear if I click reachable value from the filter it should give all reachable servers in the table. If I click Reachable it works fine, similarly if I click Unreachable its works but if I click All I am not getting the servers that were not present in the Prometheus as those values are null, and in transformations, I added that condition to remove those rows whose values are null.
But there are a few servers that were not present in Prometheus but are present in Postgres that are not able to populate in the dashboard.

Could anyone please help me?

Please suggest any other approach if my approach is wrong

Thanks & regards,
Bharath Kumar.