Using Mysql custom query to create Grafana dashboards

use so now let's talk about how we can use a custom query in order to create a visualization so i'll go back to again the data source which i have okay so this is the table name which we have which is called matrix so let's say we have a query which is called select dt and then we want max of cpu utilization and the table name is matrix and this is going to be a group by on dt okay so that's a typical sql query which we have and if i hit enter let me also put limit 10 and let's hit enter and this is the kind of result which we get okay so now let's say i want to use the similar query for grafana so i can do that let me copy these statements so i will only copy until dt i will not use limit 10 because in graphana we don't want to limit our results to only 10 records okay only for the seeing on the console or on the command prompt i have put limit 10 okay so let's go to grafana and here again i'm just going to use a short method so either we can just click on the title of one of the panel and go to more and then select duplicate and it is going to duplicate the panel and then we can click on edit on that new panel and now here in the query if you see this is a query a okay this is a query which we created using the query builder now let's say i want to get rid of this i can click on remove query option so once you click on that so the query is gone and you do not have any data in the your visualization or the dashboard or the panel which you have so now i'm going to click on query and then if you see here we have a toggle option okay so this is a toggle text edit mode so if you click on this pencil icon it is going to take you to a window where you have an option to type the query okay so by default whatever it has available i'm just going to remove that and going to paste the query which we have just copied okay now one thing here is if right now if i hit enter basically i try to save it it's going to give us error okay the error here is as you can see here the error here saying is that there is no column found which is called time or time underscore sec so whenever you are working with grafana you need to have a one column which is having date time and that column should be called time okay or time sec so now we do have a column which is called which is having date time and that column is called dt now i'm going to create an alias for this column name and i'm going to call it time okay so once we do that this error should be gone okay so again it takes some time for this to refresh or otherwise you can click on save added a panel with custom queries so that's what i have given an annotation and this is just some description so when admin is seeing he'll be able to see what is that annotation you have given so more about this later on so for now just give some comment and save it and now here if you see we already have the results based on our custom query now let's say other than max cpu you also wanted minimum cpu okay so we can do that by adding one new column which is called main cpu okay again it's going to take some time before you see it or you can hit on refresh you can see on background it is already trying to refresh and you can see one refresh icon here okay so now this is the data we have we have the data for minimum and max cpu utilization again notice the data which we have is not really very changing in dynamic that's why you see the lines almost like kind of straight line because our mean and max cpu utilization is almost similar now here if you come down on the panel you see legends so here you see mac cpu and main cpu these are the default name which which has been given to these columns you can change it by using alias again so i'm going to call it max underscore cpu and the next one i'm going to call it min cpu okay so once you do that you'll notice a change here in the legends you can now see that these values has been changed okay max cp is already changed means cpu is going to get changed or you can click on refresh so it can refresh and take these changes okay so now you can see it has been changed so that's how you use a custom query in order to create a visualization

Channel: Vikas Jha

Views: 17,954

Keywords: kibana vs grafana, grafana with mysql database, grafana dashbaords, learn grafana, mysql data source in grafana, grafana, grafana mysql, grafana for beginners, mysql custom query in grafana, grafana tutorials, mysql grafana, creating grafana dashboard using mysql, garafan panel with mysql, grafana mysql datasource, mysql grafana datasource, vikas jha, creating grafana panel using mysql

Id: R_7Hh4Vy35w

Length: 5min 33sec (333 seconds)

Published: Thu Oct 15 2020