 Hello everyone and welcome to the Ovid Monitoring with Grafana and Advanced Options. My name is Shini Ratko, I'm a BI Principal Software Engineer and I've been working at Red Hat for the past 7 years. I'm the maintainer of Ovid DWH and Grafana and with me we'll co-present today Aviv Litman. She's a BI and integration intern and she's leading the Ovid Grafana development. On the agenda today we'll talk about the data warehouse, how it is built, the Ovid Grafana, how to add custom dashboards to the Grafana and we'll show you a demo on how to do that. The contributors for this project from the engineering side are Aviv, myself and Edidia and on the QE side Lucy, Guillain and Pavel. So let's go over a bit about what is the data warehouse. The data warehouse collects the data about all the Ovid entities, starting from the data center, the clusters, the hypervisors and the VMs and the usage consumption of the disk, CPU, network and memory. The data warehouse is doing the process of ETL, which is the term coming from the BI. Let's go over it. ETL, the first one is extract. Extracting the metrics from the engine database on one-minute intervals. Then we transform. The data is being validated, transformed, cleansed and we add additional metadata to it. Then there are aggregations of hourly and daily aggregations. The data retention can be set to one of two options, basic and full scale. The difference between them is the time that we keep the samples hourly and daily data. For the basic, which is the default, we save samples for 24 hours, hourly data for one month and we don't save any daily data. This enables us to keep the data warehouse small, but it comes over with a price that we don't have a lot of history. On the full scale, we save samples data for 24 hours, the hourly data for two months and the daily data for five years. On the load side, we take this data and we save it to the Postgres database, which is called Ovid Engine History. By default, this database is saved to the same machine as the engine database, but during the engine setup you can set it up on a remote machine. The data warehouse can be migrated to a separate machine even after it was initially set up on the same machine as the engine. This is because the data warehouse can become very large because you save, for full scale, you can save five years of data. If you have a big scale environment of Ovid, then you probably should set it up on a remote machine. Let's go over about the architecture. We have the Ovid Engine, which is using VDSM, the data from the hosts and VMs, and saves it to the engine database. Then we have the data warehouse, which is pulling data from the engine DB every one minute, and then it saves it to its Postgres database. Grafana connects to the data warehouse database and queries it and displays it, which is the visualization tool. Now I'll pass it to Aviv to talk about the Ovid Grafana. Let's talk about Grafana. As she explained, Grafana is pulling data from DWH that it's a Postgres SQL database and present it. Grafana is the UI tool to present data from DWH. We have a Grafana pre-built dashboard that are divided into four main folders, executive, trend, service level, and inventory. The user can make a custom dashboard and also make changes to pre-built dashboard by copy. We'll explain it later. Dashboards are based on the legacy of Ovid Report Project and just for report. The last point, data display based on the time period selected by the user. So we have some... Most of the reports are time-based, and the user can select a time frame and see all the activity in that time frame. The queries in this dashboard query in the sample hourly and daily statistics. So that means the user can see data for each minute, for each hour, and for each day about the installation. So Grafana and DWH are installed by default during the engine setup that we will soon see. Except for self-hosted engine. In that case, you will need to install it manually. So DWH is installed in basic scale by default, but we strongly recommend to change to full scale. As Shirley explained before, full scale saves data for five years and basic scale for one month. So this is the engine setup. As you can see, all the bars are set here. The username, the database name, and so on. Here you can see the web address for Ovid Engine and for Ovid Engine Grafana. After the setup is finished successfully, you can enter the Ovid Engine web, press monitoring portal, and it will automatically connect to Grafana. Let's see some example of the pre-built dashboard. This is the executive dashboard. It shows more administrative information about user activity on VMs, about the OS of VMs and hosts. Here is the cluster dashboard. It shows information in the cluster level, how many hosts, how many virtual machine, the host in the cluster, uptime the high availability virtual machine in the cluster. Also it shows the cluster over commit, how many CPUs for the host and how many for the VMs. The next example is the host resources usage dashboard. This dashboard shows for day over week or hour over day the average number of usage for virtual machine number, for CPU, memory. The last example is the inventory dashboard. It shows more latest information, the state now, what happened now. As you can see here, this database has 320 CPUs, but in the VM it has 832, so it's a problem. Maybe we should add more CPUs or more hosts to this data center. Now I'm going to show you how to create a copy of a pre-built dashboard because you can't edit them. So you go to each, any pre-built dashboard you want, press on the dashboard setting, save as. You choose the name. It will automatically add copy, but you can change it. Choose the folder and save. If you want to reserve the tags, you need to make it true. So after you save the dashboard, you can edit it. So here is the panel. You press on the little arrow here on each panel. Press edit. You can choose the visualization or see the current one. Data source and SQL query. And now it's time for demo. I will show you some things in Rafaana. So we connect to the Oviat Engine web portal. It's by writing in the address line your FQDN of your engine and then Oviat Engine. You press on monitoring portal and it will automatically connect you to Rafaana. You press on home. Then you have for a pre-built folder and 18 pre-built dashboard. Here in the general is a dashboard I created for myself. I will show you later in the demo. So a nice feature I want to show you is the tags. As you can see, near each dashboard you have the tags that will help you to know what the content of the dashboard is. Another cool thing you can do with it is I don't know if I want to see information about disks and about host disks. So it will give you the dashboard that contains information about hosts and disks. So it's very, very nice. So first dashboard I want to show you is part of the inventory dashboard. It's part of the inventory dashboard's folder. This shows you the states right now, the latest configuration and the latest data. It can be very helpful for system administrator to monitor and to choose the resources usage. As you can see for CPU and memory, the usage is changing over time. So you can see the test memory and CPU and the running VMs, CPU and memory. Here you can see the over commit rates. Here in the disk size you can know how much is used. So you can see the total disk size and the VMs use disk size and you know how much you have left until it's soon be over. Soon we will have to allocate more in disk space to TLB2 database. Another cool dashboard I want to show you in trend is the resources usage. Here you can see the peaks or the average by the day of the week or hour of the day. In this way you can see hours of peaks and maybe in these hours you can allocate more resources to make the performance better. Here is the CPU, you can see 11 o'clock and 5 o'clock. It's very high usage. Here in the memory it's small and different. You can also change the time range for the less 30 minutes and then seeing 11 o'clock You can also search for a specific VM Let's search for my VMs and see when they are the busiest. So I can choose all my VMs and then see my data very nice and helpful. Now I'm going to show you how to copy a dashboard so you can edit it and add columns to it. So imagine I'm using this dashboard and I can see all the VMs that are unused 100% planned downtime and I want to see how much memory and CPU they have so I know which VM to delete first. So I can go to my dashboard and see for example in the VMs inventory dashboard that I have this information. The VM memory size and the number of CPU cores. I can go inside the query and I can see the VM size and the CPU size and take them. Now I will go back to my dashboard I will press dashboard setting Save As It will automatically add copy and I can choose the folder I save it in. I will save it in the same folder. I will copy the columns also Now you can edit because it's not a pre-built dashboard you go inside and here after the uptime I will add the rows because of this query is using group by I will have to use an aggregation function or to group by also by the VM CPU cores and the VM memory size. I will choose the max for now because I want the latest information to edit. Also we need to edit to the other query this is query in the sample table and this query the hourly and daily and because we are in the last 30 days there is no information yet. So I will edit here after the uptime So now I want to delete the VMs that are unused and have the most VM memory size and CPU So I can also change the order by Grafana will automatically so I want to order by going Planned downtime then the VM memory size to clear up the most memory and then the VM CPU cores Again I will edit it to the second query as well I can also add the tags now we use also CPU which will be easier for you and you can save the dashboard because it's not a pre-built dashboard add CPU and the dashboard is saved you can change to the last 24 hours as you can see here it will be changed So now I have the information you can order by each column if you press it Now I have the information and I want to extract it so it's possible also in Grafana very cool feature if it's a table you go here in the little arrow you can do it also in pre-built dashboard you press more export CSV then it will download the file you open it, press OK and it will open an Excel file the last thing I want to show you is how you create your own dashboard so you click here on the plus choose visualization you choose the visualization you want for example a table here you can write the SQL query if you remember I showed you the DWH schema in the overview documentation you can use that or you can take inspiration from other pre-built dashboards here you set the column style if it's a date, if it's a number how many decimals what is the alias here you can write a description and a panel title if you want to add vars to choose like the data center there also can be a query you press here on the var, add variable you have a few you can choose a query and then you can choose from the table all the databases name all the cluster, all the VMs multi-value means that you can choose all the entities include all options and that's how you built your own dashboard so that's it for Grafana so in this slide we have installation documentation how to change to full scale it's in the one command how to migrate to a separate machine if you choose full scale it's better to migrate to a separate machine and as a reminder if you have a self-hosted engine Grafana is not installed by default so you will have to configure Grafana after the engine setup also it's one command