How To Setup A Grafana Dashboard Step By Step

hi i'm chris from SQL for automation today we're going to upgrade the application we've created in the last tutorial now if you haven't seen it we've basically programmed the plc to measure the temperature and humidity every 5 seconds and then store it in a database so this application has now been running for a while and we want to take its data and visualize it using the free software core farmer and to do it we're going to download and install graph owner connect it to the database and set up the dashboard step-by-step so that by the end of this video we will have this dashboard now this dashboard is just an example to show you a few of the elements you can use in Reforma I guess you normally wouldn't display the same information several times anyway the different elements we're gonna set up our date a clock a text field an image from the internet this graph which combines two entities a single state field this bar graph a small graph with a fixed time range fields with aggregated functions and then finally this power gauge so yeah let's start so first we're gonna have to download it to do it I'm gonna open my browser go to graph oh no calm click this download button and the button here choose my operating system and then download the Installer now as soon as I have it I can open it execute it and run it anyway scope for all the steps all right so the server should now be running on the port 3,000 so to open it we can go to our browser type in localhost with the port 3000 which brings us here and then in here we just enter admin with the password admin after which it will ask us to set a new password so we do that click save and we're online okay so now we can set up our data source just click on this button here select your data source type and then fill out these settings here so first I'm going to enter a name Andres with the port the database I want to use the user and its password that's it so now we can go down here click save and test and we should have our data source so now we can create a first dashboard and I'm gonna do this in two parts first I'm going to add all the elements I want to use and then only later change their design so it's gotta look horrible in the beginning but don't worry about that to start I can click here which will bring up a new dashboard and because it already has an empty panel inside we can directly add a new query now as you can see it's already created an example for us and we can use this as a template now what we have here is one of the two ways we can set up the query and we're gonna stick with this for now but later we're also gonna use the other one first if you have multiple data sources set up you should make sure that you're accessing the correct one then you can define which table you want to query what column to use as the timestamp the metric you want to display on your panel the value you are interested in and then finally if you want to filter or group it so to make this a bit easier let's maybe take a look at our database first of all we have to define what table we want to use now as you can see here Griffin already selected the state's table and that's correct so we don't have to change that next it needs to know which column it can use for the time stamp and again it already selected to correct one so we can leave that as well which brings us to the metric column now with the metric column we can basically show what exactly we're displaying in the graph and right now it's set to none which means it's just displaying the name of the column it's using for the values but that's not really what I want in my case I want to display the temperature and the humidity and we can find that information in this column so I'm gonna select the entity as my metric so now we just have to define what column we want to use for the values and we're obviously interested in the states so I'm gonna select the state as my value column so now I just need to filter the results so I can show each of my entities as a separate line and to do this I'm just gonna make a filter to only select the rows where the entity is the humidity and then I'm gonna duplicate this query so I can also display my temperature all right so now since I don't want to display every little detail I'm gonna aggregate my value and only display an average and then I'm gonna give that average an alias and finally average it for every 15 minutes okay so now with these settings here you can play around a bit if you want but again I'm not going to do that too much for now what we want to do is I want to display the temperature on a different y-axis so I can click this then I can enter the ranges of each axis and enter the units okay so now we can also add a time region for example hours and then I'm gonna remove the panel title and make it transparent that's it so that's our first panel and I'm actually going to change just a little bit yeah I think that looks better okay next we're gonna add this single stat here just click Add panel choose visualization single step and then we enter pretty much the same things like before you enter a prefix select the unit and display one decimal all right next will be our bar gauge so again at panel choose visualization bar gauge and entry query and the rest were gonna pretty much leave for now just gonna remove the panel title and make it transparent next we're going to make this single graph here you and for this graph I'm gonna enter a relative time so it's always gonna display the last 24 hours no matter what time frame I'm selecting and then I'm gonna remove all the info remove the panel title a transparent all right so next will be our aggregated functions but instead of adding them one by one I'm gonna select our single stat duplicate it and modify it you okay so now let's add the power gage you and now we're going to add the title we can either choose between markdown and HTML I'm gonna go with HTML and then I just end paragraph with the text and change the font size the fall weight and the color you all right so now with the same element we can also display an image from the internet just going to duplicate it and then start off the paragraph I'm gonna insert the image with the URL and this URL is just pointing to an image on our website that's it so I'm just gonna paste that link into my image I have it here and then I can also change the style if I want okay so now we're gonna add a date and this will be a bit different than the other elements I want to extract the date from my table by always looking at the timestamp from our last entry so first of all instead of using the query builder I'm gonna edit the query directly formatted as a table and then order the table by the time stamp descending and limits the results to one row and this should give us this timestamp here now it's not really what we're looking for yet so we'll have to extract our date from this timestamp and format it so it looks good now I'm not going to explain the functions I'll be using in detail but you should be able to pretty much just copy the syntax if you want to do the same thing so first I'm going to display the day as a word then I'm also gonna ask today as a number the month as a number and a year you and that's it so let me just remove the panel tile make it transparent and we have our date so this leaves us with the clock and that one's actually a bit more complicated than the other ones because we wanted to come up automatically we're gonna have to use a plugin we don't have yet luckily though it's pretty easy to download and install a new one you can search for Gravano plugins and then look out for the clock plug-in which you have here and there we go to installation and download the zip file and then we can extract it will be to our installation folder and then we can restart the server it's just very important that we save our dashboard first because otherwise we would lose everything okay so now we can go to services search for Agra found a service and restart it all right so now I can go to our browser refresh it at panel visualization and we should find our clock plugin there I'm just gonna remove the panel title and make it transparent and that's it so now we have a basic dashboard and the only thing left to do is to make it a little bit better and then duplicate the stuff down here to also show the humidity so let's do that now the first thing I recommend is that you decide on a color theme now I'm going to use the yellow and blue from SQL for automation and black and white because most of the time it looks better if you don't use 20,000 different colors so I'm just gonna change these colors and a few settings and repeat this process for every panel also I'm not gonna narrate everything because it's basically the same process over and over again but I'm going to show it so you can follow along if you want to [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] okay I think we can leave it like this you could obviously customize and upgrade this a lot more but for this video I think it's good enough now what you might have noticed during the editing is that it sometimes took quite a long time to load the panels and that's because our database type isn't really optimized for the amount of data we put inside so depending on your application it can make sense to use a time series data base like in Fox TV or Prometheus instead of relational one like I'm using here and maybe we'll cover that in a future video so if you're interested in that or have any questions feel free to write a comment below or visit our channel or website for more information thanks a lot for watching I hope this video was helpful and I'll see you next tutorial [Music]

Channel: SQL4automation

Views: 268,257

Rating: 4.9337296 out of 5

Keywords: PLC, SPS, SQL, Database, Automation, SQL4automation, Inasoft, MariaDB, MySQL, Grafana

Id: 4qpI4T6_bUw

Length: 21min 0sec (1260 seconds)

Published: Tue Nov 05 2019