Hi,
How can I represent data in my Grafana Dashboard for SQL Database that is changing (dynamic) everyday?
An application consumes data and puts it in the database table (date_created) everyday. How can I automate my Grafana dashboard to pick data once it gets the data available in database?
Database: Microsoft SQL Server Management Studio
Grafana version: v9.3.1
Hi Greeting Dear,
Grafana’s plugins are a quick and simple way to extend Grafana’s dashboard plugins and data sources. Leveraging the GoLang oci8 library, the Oracle data source plugin allows you to pull Oracle data and stats directly into Grafana dashboards — without having to extract and load the data to “yet another data warehouse” — which means you can visualize the data either in isolation (one database) or blend the data with other data sources (e.g., other Oracle databases, other database technologies, MSSQL, MySQL, Maria, Postgres, RDS, etc.). This enables you to discover and visualize correlations and covariances in your data in minutes.
As someone who has worked with many databases in my past, I found that the ease of use, extensibility, and the ability to mix queries across databases in real time make this plugin a highly powerful tool to have in your analytics and visualization toolkit. If you want complete info about this then you can go for it.
Welcome @rishabdalal
Have you already set up the MSSQL datasource in Grafana to view your data?
1 Like
Hi @grant2 yes, the datasource is configured, but its a static data representation currently.
At the risk of asking an obvious question, are you saying that as your MSSQL database grows with datapoints, you do not see these in Grafana? Do you have any working queries in Grafana that use the MSSQL database?
yes, currently the query that is designed (to fetch- time, avg_transaction) is used in grafana dashboard to represent data for a certain time frame (Last 10 days) and is static.
Now the database grows everyday with new data with updated (time, avg_transaction) for the same query which is pulled in Grafana.
How can I automate my dashboard so that, whenever there is new data in MSSQL database, it represents the updated data in Grafana also (after a refresh click)?
1 Like
Please post the current static query that you are using.
Also is your date_created column defined as UTC?
Query :
select getdate() as ‘time’, case
when CONVERT(varchar(10),batch_no) = ‘2840’ then ‘22.2.4772’
when CONVERT(varchar(10),batch_no) = ‘2841’ then ‘22.2.4773’
end as batch_no,
avg(total_transaction_time) as value
from performance_hdr_detail where transaction_type = ‘XYZ’ and wos_test = ‘N’ And batch_no
IN (‘2840’,‘2841’) GROUP BY batch_no
The batch_no is getting updated regularly with +1 and with new set of transaction_time in the database.
Whereas to represent it in Grafana, I need to update the query manually everytime in grafana. can this be automated, as my Database updates, Grafana dashboard also updates just refreshing it?
How does this conversion rule work?
Its just a variable that is declared
for 2840 the value will display as 22.2.4772
To make it dynamic i think we need to know know how 2840 correlates to 22.2.4772 and so on
Or which part of the query do you want to be dynamic?
The Where clause or the case statement?