1 00:00:00,390 --> 00:00:05,740 So enough of looking at visualizations now with a test of DB, let's use a real data source. 2 00:00:05,830 --> 00:00:10,860 First one will be a more secure data source will also need to install a collective it and then we can 3 00:00:10,860 --> 00:00:11,700 install a dashboard. 4 00:00:11,770 --> 00:00:16,350 OK, so what we can do is get ourselves a dedicated MySchool server, don't use an existing production 5 00:00:16,350 --> 00:00:19,470 server, but now we can create one using the digitalization coupon from earlier. 6 00:00:19,470 --> 00:00:21,660 Or you might have a different cloud provider set up. 7 00:00:21,810 --> 00:00:22,200 OK. 8 00:00:22,230 --> 00:00:26,990 So looking at the diagram so far, Karana server might want to take you to a profanities because it 9 00:00:27,240 --> 00:00:28,280 has an IP address. 10 00:00:28,290 --> 00:00:32,430 I have ports 84 for three and actually twenty two as well. 11 00:00:32,430 --> 00:00:36,870 For site, we've been playing around with a test data DB data source. 12 00:00:36,880 --> 00:00:40,490 We're now moving on to the mosque, your data source, which is one of many. 13 00:00:40,500 --> 00:00:46,440 So I'm going to install a mosque, our server first, then install a collector that collect will then 14 00:00:46,440 --> 00:00:50,910 prepare tables in a time series format that we can use in Havana. 15 00:00:50,970 --> 00:00:53,340 OK, so first thing, let's go to your server. 16 00:00:53,370 --> 00:00:55,620 I mean, digitalization, I'm going to create new droplet. 17 00:00:55,620 --> 00:01:00,270 Many cloud providers will have a specialized database option. 18 00:01:00,270 --> 00:01:01,160 I'm not going to do that. 19 00:01:01,170 --> 00:01:02,700 I'm just going to create a droplet. 20 00:01:02,700 --> 00:01:07,800 I'm going to install Ubuntu 20 minimum spec and I'm going to manually install my SKU on to that. 21 00:01:08,280 --> 00:01:11,610 OK, so Ubuntu twenty basic $6 a month I can put anywhere. 22 00:01:11,610 --> 00:01:13,440 I like Amsterdam, for example. 23 00:01:13,830 --> 00:01:15,270 I'm going to use my key. 24 00:01:15,300 --> 00:01:16,050 I already have one. 25 00:01:16,060 --> 00:01:17,640 You can use a password if you want. 26 00:01:18,120 --> 00:01:20,040 I'm going to call it my school. 27 00:01:20,070 --> 00:01:21,870 Like that crate droplet? 28 00:01:25,380 --> 00:01:30,360 OK, so that's the IP address I've been given, that's a public IP copy that I'm now going to set that 29 00:01:30,360 --> 00:01:31,110 up in party. 30 00:01:31,140 --> 00:01:32,220 That's what I use, for instance. 31 00:01:32,670 --> 00:01:36,150 So IP address and there I'm going to call it my secure. 32 00:01:36,180 --> 00:01:43,320 I'm going to set the authentication, my private key because I'm using this as HK method and I'm going 33 00:01:43,320 --> 00:01:47,520 to change my parents to be larger font because it's hard to say otherwise. 34 00:01:47,560 --> 00:01:50,880 OK, before I open that, I'll just save that. 35 00:01:50,940 --> 00:01:53,620 OK, so my school, there it is there so I can open that up. 36 00:01:53,830 --> 00:01:55,560 This is my new mask, your server. 37 00:01:55,560 --> 00:01:56,670 I'm logging on as root. 38 00:01:57,030 --> 00:01:57,990 There we go there. 39 00:01:58,020 --> 00:02:01,430 So root at my school, this is another server. 40 00:02:01,440 --> 00:02:07,260 So I've got two servers now microfinance server at that IP address, and I like your server at that 41 00:02:07,260 --> 00:02:08,050 IP address. 42 00:02:08,070 --> 00:02:09,789 It's going to update my documentation. 43 00:02:09,810 --> 00:02:10,410 They would go. 44 00:02:10,500 --> 00:02:10,960 Very good. 45 00:02:10,979 --> 00:02:16,200 I need to have open port twenty two and three three zero six, but more about that after. 46 00:02:16,230 --> 00:02:20,340 OK, first thing you normally do when you get anybody, server is update the app to so it knows about 47 00:02:20,340 --> 00:02:21,540 the latest packages. 48 00:02:23,970 --> 00:02:30,030 OK, now we're going to install my civil servant to that server, so sudo apt get install Mike, your 49 00:02:30,030 --> 00:02:30,450 server. 50 00:02:31,280 --> 00:02:32,160 Now we go now. 51 00:02:32,190 --> 00:02:36,570 Precious, OK, now I'm creating a brand new Mike your server. 52 00:02:36,870 --> 00:02:38,940 I don't want to use a production server just yet. 53 00:02:39,600 --> 00:02:42,740 I can do that later, so I recommend not using our production server. 54 00:02:42,750 --> 00:02:47,460 Just use something that we can experiment with and then delete when you're finished with it. 55 00:02:48,240 --> 00:02:49,080 OK, that's done. 56 00:02:49,380 --> 00:02:52,740 Next thing I'm going to do is run this command here. 57 00:02:52,770 --> 00:02:59,410 This is a tool that we can use to make sure our SQL server is locked down in a secure manner. 58 00:02:59,460 --> 00:03:05,070 Presenter, I'm not going to use the validate password option because I'm just teaching here. 59 00:03:05,070 --> 00:03:07,110 I want the password to be nice and simple. 60 00:03:07,140 --> 00:03:10,610 If you want really super complicated passwords, press us for this. 61 00:03:10,620 --> 00:03:13,350 I'm just going to press no, because it will create problems while learning. 62 00:03:13,350 --> 00:03:15,280 So any other key for no end? 63 00:03:15,540 --> 00:03:21,950 For example, I'm just going to create a default password and that is just password S.W.O.R.D.. 64 00:03:21,960 --> 00:03:22,890 Nothing complicated. 65 00:03:23,310 --> 00:03:24,440 Remove anonymous users. 66 00:03:24,450 --> 00:03:26,850 Yes, this allowed root remotely. 67 00:03:26,970 --> 00:03:27,480 Yes. 68 00:03:27,570 --> 00:03:28,890 Remove test database. 69 00:03:28,890 --> 00:03:29,460 Yes. 70 00:03:29,550 --> 00:03:30,870 Reload privilege tables. 71 00:03:30,990 --> 00:03:31,500 Yes. 72 00:03:31,650 --> 00:03:32,130 All done. 73 00:03:32,160 --> 00:03:32,730 That's ready. 74 00:03:32,730 --> 00:03:34,410 Let's check its status. 75 00:03:35,920 --> 00:03:37,150 Very good active running. 76 00:03:37,180 --> 00:03:42,940 Now it's important to know what version you just installed so that you can run Moscow Hyphen Capital 77 00:03:42,940 --> 00:03:43,660 V like that? 78 00:03:43,810 --> 00:03:46,820 Version eight That's important to note because we'll use it at the moment. 79 00:03:46,840 --> 00:03:50,530 Okay, so we don't have this server, Moscow server and that's my IP address. 80 00:03:50,530 --> 00:03:52,000 Your IP address is going to be different. 81 00:03:52,060 --> 00:03:54,970 Next thing I'm going to do is I need to install a collector. 82 00:03:55,030 --> 00:04:01,240 All data sources will have some kind of collection process going on, preparing data from the end data 83 00:04:01,240 --> 00:04:06,190 source and saving it into a format that Safana can quickly retrieve from. 84 00:04:06,190 --> 00:04:11,920 That will usually be a table of some sort that contains rows with a timestamp and one or more values. 85 00:04:12,050 --> 00:04:16,510 OK, so for this mosque, your data source, there are several options that we can choose from. 86 00:04:16,690 --> 00:04:21,579 I'm going to use a dashboard and collected that you can get from the Griffon Dashboards website. 87 00:04:21,700 --> 00:04:27,880 OK, so the dashboard I'll use will be the popular my two simple dashboard from this link here. 88 00:04:27,920 --> 00:04:32,020 So open that this is the Carvana dashboard page we scroll down. 89 00:04:32,020 --> 00:04:38,230 I have my school data source selected from this list of many, and it's this one here to my school simple 90 00:04:38,230 --> 00:04:38,740 dashboard. 91 00:04:38,770 --> 00:04:42,760 There are several others that you can try out, but I always found this one to be the best. 92 00:04:42,950 --> 00:04:47,470 OK, so there's some sample images here of what it will look like when we're finished. 93 00:04:48,540 --> 00:04:48,870 OK. 94 00:04:49,170 --> 00:04:55,230 Its dependencies are minimum Gryffindor 8.1.2, and we use these visualizations down here. 95 00:04:55,920 --> 00:05:02,220 Now this dashboard requires a specific collector, and you can see that written here in the reviews 96 00:05:02,220 --> 00:05:04,530 section down here. 97 00:05:04,560 --> 00:05:06,920 You must use a collector from there. 98 00:05:06,930 --> 00:05:08,740 So I have a link to that also. 99 00:05:08,760 --> 00:05:10,560 So the collector will be from here. 100 00:05:10,590 --> 00:05:11,910 That's the collector will install. 101 00:05:11,920 --> 00:05:17,940 It's on GitHub, and I'm using version eight, so I'll need to download this script to my to underscore 102 00:05:18,000 --> 00:05:18,180 it. 103 00:05:18,850 --> 00:05:22,740 Well, if you're using version five of my show, then download that script. 104 00:05:22,770 --> 00:05:27,030 So on my documentation, I've prepared a get command here. 105 00:05:27,120 --> 00:05:32,970 You can copy that, and that's downloading the mod to underscore skill on my MySchool server. 106 00:05:32,980 --> 00:05:34,560 Right click space. 107 00:05:34,590 --> 00:05:41,970 OK, so you get rule GitHub user, etc. My collector master my two ideas key will enter and that's downloaded 108 00:05:41,970 --> 00:05:43,380 100 percent through fast. 109 00:05:43,390 --> 00:05:44,810 We need to edit that file. 110 00:05:44,820 --> 00:05:47,540 So Sudo Nano or use nano for that. 111 00:05:47,640 --> 00:05:51,020 Nano is a text editor for Linux Ciudadano. 112 00:05:51,090 --> 00:05:52,200 My two ideas cool. 113 00:05:52,230 --> 00:05:53,970 Now we scroll down. 114 00:05:53,970 --> 00:05:57,370 What it's actually doing is creating a database called Meitu. 115 00:05:57,420 --> 00:06:03,900 It will create some tables in that database where it will store statistics about your server. 116 00:06:05,060 --> 00:06:11,810 And these are the commands that will create and run if you are good at school, you might find this 117 00:06:11,810 --> 00:06:12,660 very informative. 118 00:06:12,680 --> 00:06:16,940 But if you don't know a skill, then you don't really need to know this to understand professional. 119 00:06:18,150 --> 00:06:24,270 But what the collector is essentially made from is a store procedure which will collect daily stats. 120 00:06:25,450 --> 00:06:28,780 And it will run every 10 minutes now, right at the end. 121 00:06:28,810 --> 00:06:31,270 We need to uncomment these last three lines. 122 00:06:32,050 --> 00:06:34,870 We need to create a user called my two hat. 123 00:06:34,960 --> 00:06:38,530 That's a wild card for this server identified by anything you like. 124 00:06:38,560 --> 00:06:39,310 This is the password. 125 00:06:39,340 --> 00:06:40,420 I'm just going to keep it simple. 126 00:06:40,450 --> 00:06:41,950 I'm asking, you use the word password. 127 00:06:42,160 --> 00:06:49,090 Grant all that means all permissions on my two dot star, which means all the tables in the Meitu database 128 00:06:49,090 --> 00:06:50,220 to the user. 129 00:06:50,230 --> 00:06:56,890 My two at wild card this server, because the my two user is going to be querying global statistics 130 00:06:56,890 --> 00:06:58,110 about the MySchool server. 131 00:06:58,120 --> 00:07:05,110 It also needs to have select permission on the Performance Schema database or tables to my two same 132 00:07:05,110 --> 00:07:05,480 user. 133 00:07:05,590 --> 00:07:09,790 Now this is all about the collector, so control X to save that. 134 00:07:10,000 --> 00:07:10,510 Yes. 135 00:07:11,110 --> 00:07:14,560 What I've just done was installed the script for the collector. 136 00:07:14,560 --> 00:07:16,740 When we run the script, it will credit stored procedure. 137 00:07:16,810 --> 00:07:21,940 It will run every 10 minutes running a command called Show Global Status and store that data into a 138 00:07:21,940 --> 00:07:25,480 table, know time series fashion so that we can do that using Gravano. 139 00:07:25,510 --> 00:07:30,960 Now, the last three lines we on commented were about creating this user called my two just to you, 140 00:07:30,970 --> 00:07:34,620 and it has a grant select permission on everything in performance schema. 141 00:07:34,630 --> 00:07:35,910 We don't need to run that script. 142 00:07:35,920 --> 00:07:37,450 We can run that script using Morse code. 143 00:07:37,450 --> 00:07:41,020 Also copy that line just there and on your server. 144 00:07:41,050 --> 00:07:48,280 Mike will run my to you will present a case that's just run that mask your script that we just edited 145 00:07:48,280 --> 00:07:50,400 using nano and it would have created a new user. 146 00:07:50,410 --> 00:07:53,810 We can now open a mike your prompt and do some simple tests. 147 00:07:53,830 --> 00:07:55,060 So just type mask. 148 00:07:55,060 --> 00:08:00,340 You'll like that enter and it should present you with a mask for a prompt that greater than sign with 149 00:08:00,340 --> 00:08:00,900 the mosquito. 150 00:08:00,910 --> 00:08:02,290 That's the mask you are prompt. 151 00:08:02,320 --> 00:08:04,510 We can now start typing still commands. 152 00:08:04,780 --> 00:08:11,620 So the first one will do show databases, they show databases, and we have several databases on this 153 00:08:11,620 --> 00:08:12,460 as your server. 154 00:08:12,490 --> 00:08:17,320 The new one we discredit from the script is called Mitu has several tables that our collective will 155 00:08:17,320 --> 00:08:21,640 be using, and Gravano will be reading from this also the performance schema type that already exists 156 00:08:21,640 --> 00:08:22,780 on the MySchool server. 157 00:08:22,780 --> 00:08:29,290 But we've created that my to user, which can read from the performance schema and summarize information 158 00:08:29,290 --> 00:08:33,960 into the My two table so that my two user and the My two table are named the same thing. 159 00:08:33,970 --> 00:08:37,539 But just be aware that this is the database called Mitu. 160 00:08:37,539 --> 00:08:42,039 And there's a user that we also created a Code White with select permissions on the performance schema. 161 00:08:42,070 --> 00:08:48,250 OK, now part of what's important about the collector is that Eskimo will use an event scheduled to 162 00:08:48,250 --> 00:08:53,800 run that every ten minutes a copy that line pasted in show variables where verbal name equals event 163 00:08:53,800 --> 00:08:56,170 scheduler, if it says on that is good. 164 00:08:56,200 --> 00:09:01,840 If it says off like it used to in the older versions of Moscow, then would have to do some things to 165 00:09:01,840 --> 00:09:02,560 enable it. 166 00:09:02,590 --> 00:09:03,370 We don't have to do that. 167 00:09:03,950 --> 00:09:04,840 OK, so the next line? 168 00:09:04,840 --> 00:09:08,380 Let's have a look at some information about our users in our database. 169 00:09:09,100 --> 00:09:11,350 Select host user from my shoulder user. 170 00:09:11,410 --> 00:09:17,670 OK, so we have some in-built users, the scale we use and we also have our own one that we credit who 171 00:09:17,770 --> 00:09:24,010 my to, then my to is the specific user that the collective will use to read that performance schema 172 00:09:24,010 --> 00:09:27,310 and summarize it into the my two that will query and Bafana. 173 00:09:27,760 --> 00:09:31,350 OK, next, let's look at the Martu database. 174 00:09:31,360 --> 00:09:37,660 So use my to what else switching to the My two database, it's to show tables. 175 00:09:37,780 --> 00:09:39,100 Finished with a semicolon. 176 00:09:39,130 --> 00:09:41,740 OK, so there's two tables in it called current status. 177 00:09:41,740 --> 00:09:46,720 The collector will be saving data into those two tables, so let's see what there is now. 178 00:09:46,750 --> 00:09:53,230 Select all from current and use with a semicolon and there's some data there, so I can do the same 179 00:09:53,230 --> 00:09:53,620 thing. 180 00:09:53,650 --> 00:09:55,210 Select all status. 181 00:09:56,210 --> 00:09:56,810 There we go. 182 00:09:56,870 --> 00:09:58,380 And there's some data now, too. 183 00:09:58,400 --> 00:10:02,210 Now, if you want to exit the mosque, you are prompt just Typekit like that. 184 00:10:02,550 --> 00:10:05,870 So we're back into the normal Ubuntu bash prompt now. 185 00:10:05,960 --> 00:10:06,730 OK, that's very good. 186 00:10:06,830 --> 00:10:09,920 We now have the collector running and this sort there. 187 00:10:09,950 --> 00:10:14,100 We can now go into Safana and try and set up a data source. 188 00:10:14,120 --> 00:10:18,890 It's not going to work completely, but it's good to see what kind of problems will have so that we 189 00:10:18,890 --> 00:10:19,790 know how to fix them. 190 00:10:19,860 --> 00:10:25,190 OK, so intifadah, I'm going to go down here to configuration data sources, and I'm going to add a 191 00:10:25,190 --> 00:10:25,990 data source. 192 00:10:26,000 --> 00:10:29,820 Scroll down until I find my skill to select. 193 00:10:29,840 --> 00:10:34,680 OK, so this is a lot of test out a DB, but instead this is about connecting to a mosque yourself. 194 00:10:34,700 --> 00:10:40,470 So the host will be the IP address of mosque your server call and three three zero six the IP address. 195 00:10:40,490 --> 00:10:41,000 What's that? 196 00:10:41,060 --> 00:10:44,270 OK, so hosts call and three three zero six. 197 00:10:44,420 --> 00:10:48,710 Now, these things aren't going to work initially, but we'll just show you what those problems are. 198 00:10:48,710 --> 00:10:52,210 Be the database that I'll be connecting to will be called two. 199 00:10:52,250 --> 00:10:53,980 Now that's the database that we just created. 200 00:10:54,040 --> 00:10:58,570 One that script and our collector will be saving data into that Meitu database. 201 00:10:58,580 --> 00:11:01,970 The user that will use is called Griffon with a password. 202 00:11:01,980 --> 00:11:02,950 I'm going to keep it simple. 203 00:11:02,960 --> 00:11:07,730 I'm going to use the word password and I can see that if I click this, so that's the simple password 204 00:11:07,730 --> 00:11:09,710 I'm using, you can use something a lot more complicated. 205 00:11:09,770 --> 00:11:14,300 OK, now this user doesn't yet exist on our Eskil service will create that. 206 00:11:14,360 --> 00:11:18,920 Also, depending on where you got your Eskil server from and how we've installed it, we won't be able 207 00:11:18,920 --> 00:11:21,850 to connect on Port three three zero six, but we'll get into that also. 208 00:11:21,860 --> 00:11:25,270 So let's just save and test and see what we get. 209 00:11:25,340 --> 00:11:26,950 OK, so data source updated. 210 00:11:26,960 --> 00:11:28,310 But we got a problem. 211 00:11:28,310 --> 00:11:30,320 Connecting to the server will resolve that. 212 00:11:31,070 --> 00:11:32,240 OK, so several things to do. 213 00:11:32,270 --> 00:11:36,360 So one of those is to create a user called Crafar on that server. 214 00:11:36,380 --> 00:11:39,480 Now I'm creating a new user because it's advised to sacrifice. 215 00:11:39,490 --> 00:11:41,870 Gryffindor does not validate the query to site. 216 00:11:41,910 --> 00:11:47,060 So queries can contain any Haskell Simon, for example, use on the database or drop tables, or do 217 00:11:47,060 --> 00:11:49,820 any other malicious thing and use Carafano as a vector. 218 00:11:49,850 --> 00:11:55,910 So what you do is you create a specific user with minimum permissions, for example, select only on 219 00:11:55,910 --> 00:11:58,710 a specified database and tables that you want to query. 220 00:11:58,730 --> 00:12:04,820 So I could also use the my two user, but the my two user has advanced permissions because you can also 221 00:12:04,820 --> 00:12:09,050 read from the performance schema table and also insert data. 222 00:12:09,060 --> 00:12:14,540 So I'm creating a specific user code Bafana with only select permissions and select permissions on all 223 00:12:14,540 --> 00:12:16,730 the tables in the two database. 224 00:12:17,090 --> 00:12:18,940 So first thing will do is create a user. 225 00:12:18,950 --> 00:12:24,680 So going back onto our MySchool server when my MySchool server, I'm going to log back into the mosque 226 00:12:24,680 --> 00:12:25,310 will prompt. 227 00:12:25,310 --> 00:12:26,510 So type in Mike. 228 00:12:26,510 --> 00:12:33,200 You will enter like that and will create a user, so create use Agraféna at identified by password. 229 00:12:33,290 --> 00:12:35,600 Now we need to know what the IP address is. 230 00:12:35,600 --> 00:12:36,650 All biography on a server. 231 00:12:36,650 --> 00:12:42,650 So I'm replaced that day and the IP address of Mega-fauna server is this value that I saved earlier. 232 00:12:42,710 --> 00:12:49,100 OK, so I'm creating a new user called Griffon at that IP address, so that will be used for connecting 233 00:12:49,100 --> 00:12:51,910 to the mosquito server from Mega-fauna server. 234 00:12:51,920 --> 00:12:54,830 And that's the username that Mike will expect. 235 00:12:54,830 --> 00:12:56,360 And that's the password I'm using is very simple. 236 00:12:56,360 --> 00:12:57,050 Just password. 237 00:12:57,320 --> 00:12:59,640 And that's the password that I entered here before. 238 00:12:59,690 --> 00:13:02,360 OK, so identified by password presents. 239 00:13:02,420 --> 00:13:03,680 So we have a new user. 240 00:13:03,710 --> 00:13:11,660 Now I'm going to select on that user to all the tables in the Meitu database so that user 2FA at one 241 00:13:11,660 --> 00:13:12,680 for two. 242 00:13:14,120 --> 00:13:20,960 OK, so a line that can finally use it to read all the tables in the Meitu database present to flush 243 00:13:20,960 --> 00:13:21,620 privileges. 244 00:13:22,460 --> 00:13:29,690 And before we go, we can verify that user exists by typing in select host use for my skill user. 245 00:13:30,170 --> 00:13:32,450 And then we go, we have a new user called Crafar. 246 00:13:32,450 --> 00:13:34,670 Now that's the host that it will be connected. 247 00:13:35,330 --> 00:13:36,590 So quit now. 248 00:13:36,620 --> 00:13:41,630 That's not all the problems solved yet, but try connecting again to see what error we get now in the 249 00:13:41,630 --> 00:13:42,380 data source. 250 00:13:43,010 --> 00:13:45,170 So save and test data source updated. 251 00:13:45,170 --> 00:13:47,180 But we still have a problem now. 252 00:13:47,180 --> 00:13:51,200 By default, when you install MySchool, it won't allow external connections. 253 00:13:51,230 --> 00:13:58,020 So my server that IP address isn't able to connect to the running module process. 254 00:13:58,040 --> 00:14:03,680 OK, so to allow remote connections, we need to open the MySchool configuration file that will use 255 00:14:03,680 --> 00:14:04,280 that command. 256 00:14:04,310 --> 00:14:07,100 Sudo Nano ADC Moscow my positive. 257 00:14:07,250 --> 00:14:10,460 Scroll down and add this section here. 258 00:14:10,970 --> 00:14:16,880 This will bind Moscow to all IP addresses on your server thing, the external IP address, as well as 259 00:14:16,880 --> 00:14:23,300 localhost because right now, by default, it's only bound to local host cases a that control x y vious. 260 00:14:23,300 --> 00:14:24,980 Enter now to restart was. 261 00:14:25,550 --> 00:14:26,330 Copy that. 262 00:14:26,960 --> 00:14:28,910 Sudo service module restart. 263 00:14:29,060 --> 00:14:29,440 OK. 264 00:14:30,770 --> 00:14:32,300 Let's check its status. 265 00:14:33,430 --> 00:14:35,620 Right, good active running control to get out of that. 266 00:14:35,650 --> 00:14:42,070 Now if we try again to connect using the Moscow data source, it should be a case saving test database 267 00:14:42,070 --> 00:14:42,430 connection. 268 00:14:42,430 --> 00:14:47,680 Okay, so when connecting to external data sources, you are going to have lots of different issues 269 00:14:47,680 --> 00:14:49,700 related to connectivity and permissions. 270 00:14:49,720 --> 00:14:52,870 So on my my SQL server, I have an IP address. 271 00:14:52,870 --> 00:14:53,890 I have ports. 272 00:14:53,890 --> 00:14:56,620 Twenty two and three three zero six already open. 273 00:14:56,650 --> 00:15:00,690 I'm using unrestricted Ubuntu server with no port blocking by default. 274 00:15:00,700 --> 00:15:02,740 So three three zero six works already. 275 00:15:02,740 --> 00:15:07,930 I've created a user called Raphinha at the IP address of my grandfather's server because that's how 276 00:15:07,930 --> 00:15:09,640 a school is going to see the connection. 277 00:15:09,670 --> 00:15:15,970 I've also bound my school two zero zero zero zero so that it also binds to the external IP address. 278 00:15:16,000 --> 00:15:21,940 Now, if you're using any other cloud provider, Port three three zero six will probably also need to 279 00:15:21,940 --> 00:15:24,790 be opened in your security group settings. 280 00:15:24,910 --> 00:15:26,870 However, you do this in your cloud provider. 281 00:15:26,890 --> 00:15:27,730 I didn't need to do that. 282 00:15:27,970 --> 00:15:30,460 What my digitalization server because it's already open by default. 283 00:15:30,490 --> 00:15:31,850 OK, so that's good. 284 00:15:31,870 --> 00:15:33,130 We can get out of that now. 285 00:15:33,610 --> 00:15:39,620 If we go to the Explore tab, just click that and select the Moscow data source at the top. 286 00:15:39,640 --> 00:15:42,270 We will now be able to run queries on that. 287 00:15:42,280 --> 00:15:48,310 My two table now, the Moscow Query Wizard here is quite hard to understand at first, so instead go 288 00:15:48,310 --> 00:15:50,440 straight into the ESCO option. 289 00:15:50,440 --> 00:15:55,220 If you're familiar with SQL, that statement will make some sense to you. 290 00:15:55,240 --> 00:15:56,470 But anyway, I'm not going to run that. 291 00:15:56,480 --> 00:16:02,640 What I want to run is this section here, so copy that and replace that and now run query. 292 00:16:02,650 --> 00:16:03,220 And there we go. 293 00:16:03,230 --> 00:16:04,600 We start to get some data. 294 00:16:04,600 --> 00:16:11,590 So we're reading data from my two dot status where variable name equals threads connected in order by 295 00:16:11,590 --> 00:16:12,610 time ascending. 296 00:16:12,610 --> 00:16:16,180 We don't have to really understand what's going on there yet, but you will understand that I don't. 297 00:16:16,210 --> 00:16:22,470 Anyway, that just verifies that we're connecting to the Moscow database through the Moscow data source. 298 00:16:22,480 --> 00:16:26,860 The next part is about creating the dashboard so dashboards manage. 299 00:16:26,950 --> 00:16:28,360 We're going to import a dashboard. 300 00:16:28,450 --> 00:16:30,550 We're going to import the dashboard from Gravano. 301 00:16:30,670 --> 00:16:37,930 OK, so back on the Giffen Labs website or to Moscow, a simple dashboard, there is an ID just to get 302 00:16:37,930 --> 00:16:38,680 this dashboard. 303 00:16:38,680 --> 00:16:41,410 So copy that to clipboard seven nine nine one. 304 00:16:41,410 --> 00:16:43,360 Go back into this graph on a page here. 305 00:16:43,390 --> 00:16:45,730 Type in the ID or paste and load. 306 00:16:45,730 --> 00:16:49,660 OK, so it's found to MySchool Simple Dashboard Folder General. 307 00:16:49,660 --> 00:16:52,170 We could create around folders if we want it, so I'm not going to do that. 308 00:16:52,270 --> 00:16:56,010 Select a default data source being Moscow and now import. 309 00:16:56,200 --> 00:17:02,020 OK, so we now have an import the we've just downloaded from Safana, which is built specifically for 310 00:17:02,020 --> 00:17:04,750 that collector that we just installed and set up in Moscow. 311 00:17:04,750 --> 00:17:05,980 And we've created the users. 312 00:17:05,980 --> 00:17:07,770 We've opened the appropriate ports. 313 00:17:07,780 --> 00:17:13,060 We've made the appropriate changes to Moscow to allow the external connection, and we can now start 314 00:17:13,060 --> 00:17:16,170 getting data now to collect runs every 10 minutes. 315 00:17:16,180 --> 00:17:18,970 So we're not going to see a real lot for now. 316 00:17:18,970 --> 00:17:22,050 Every 10 minutes, there'll be a new update to these graphs. 317 00:17:22,060 --> 00:17:28,180 So what I'll do is pause my recording and come back in an hour and we'll see some more data written 318 00:17:28,180 --> 00:17:29,900 here about our Moscow seven. 319 00:17:29,950 --> 00:17:30,400 Excellent. 320 00:17:30,670 --> 00:17:35,800 OK, so my Moscow data source that's going to be running for about two and a half hours. 321 00:17:36,070 --> 00:17:37,800 Set that to three hours. 322 00:17:37,810 --> 00:17:39,130 So see it. 323 00:17:39,430 --> 00:17:44,680 OK, so also note that my database server that I just installed isn't working very hard. 324 00:17:44,680 --> 00:17:47,860 It's not a production server, so there isn't really much to see here. 325 00:17:48,220 --> 00:17:49,560 It's not working hard at all. 326 00:17:49,570 --> 00:17:54,550 Anyway, with each of these visualizations, we can look into those and inspect them a little more to 327 00:17:54,550 --> 00:17:55,240 see what they are. 328 00:17:55,270 --> 00:18:01,720 For example, I can press eight on these threats and errors here, and if I scroll up, that's the query 329 00:18:01,720 --> 00:18:04,690 that California is using, which is worth being aware of. 330 00:18:05,380 --> 00:18:10,630 There are two queries running in here and be threads connected threads running a few other particular 331 00:18:10,630 --> 00:18:15,790 properties from the data source that we see on the one single graph, so you can see how that is put 332 00:18:15,790 --> 00:18:16,180 together. 333 00:18:16,210 --> 00:18:23,050 Another thing here where a graph has been hard coded to 180 days, despite me expecting three hours 334 00:18:23,050 --> 00:18:23,410 up there. 335 00:18:23,440 --> 00:18:30,040 So if I edit that one and if I click query options, I can change that 180 days to be two days. 336 00:18:30,070 --> 00:18:34,390 For example, there is in two days of data, so it's still not that interesting to see, but I could 337 00:18:34,390 --> 00:18:36,460 apply that now, says last two days. 338 00:18:36,730 --> 00:18:38,730 There's another one down here last 14 days. 339 00:18:38,740 --> 00:18:42,950 This is a heat map and will become more interesting as it fills up. 340 00:18:43,030 --> 00:18:44,460 Let's change that to two days. 341 00:18:45,440 --> 00:18:46,700 As well, just to see. 342 00:18:47,560 --> 00:18:52,400 OK, so apply that so it's now showing two days, this will look more interesting when it's been running 343 00:18:52,400 --> 00:18:59,510 for many days and especially if it was actually a production mosquito database used by our website or 344 00:18:59,510 --> 00:19:00,710 other application anyway. 345 00:19:00,710 --> 00:19:07,670 So I hope you can see from all that that setting up a dashboard is actually a very complicated process. 346 00:19:07,700 --> 00:19:09,440 We will set up many dashboards. 347 00:19:09,500 --> 00:19:11,420 The mosquito was the first one. 348 00:19:11,900 --> 00:19:13,940 There'll be more as we go across. 349 00:19:14,030 --> 00:19:19,310 Important thing about setting up dashboards and Crafar is that you need to consider that the graphic 350 00:19:19,310 --> 00:19:24,440 on a server is going to have potentially privileged access to the end data source you're connecting 351 00:19:24,440 --> 00:19:24,680 to. 352 00:19:24,710 --> 00:19:31,250 So you need to manage permissions and security so that Rafah now can't be used as a vector to steal 353 00:19:31,250 --> 00:19:33,010 data or even destroyed data. 354 00:19:33,020 --> 00:19:38,810 So that was important that I had set up appropriate uses and permissions and IP restrictions, which 355 00:19:38,810 --> 00:19:44,140 leads me on to setting up IP tables rules now for my server on three three zero six. 356 00:19:44,150 --> 00:19:48,260 Since I don't have a dedicated firewall on my server, I'm going to manage access to ports three three 357 00:19:48,260 --> 00:19:49,880 zero six using IP tables. 358 00:19:49,940 --> 00:19:52,040 OK, so I'm on my MySchool server. 359 00:19:52,040 --> 00:19:57,590 I'm just going to type in IP tables five and there are no dedicated rules on these, so I'm going to 360 00:19:57,590 --> 00:19:59,660 create one four three three zero six. 361 00:19:59,870 --> 00:20:06,470 So if I scroll to the bottom there IP Table's input source or Farner ESP code dot net, that's my server 362 00:20:06,500 --> 00:20:09,020 destination ports three three zero six accept. 363 00:20:09,110 --> 00:20:15,150 So what this rule will do is accept incoming connections to this server from a server with the IP address, 364 00:20:15,150 --> 00:20:21,620 sacrifice code dot net supply into that, and if I do iptables l again, it has updated that domain 365 00:20:21,620 --> 00:20:23,180 name to be the actual IP address. 366 00:20:23,180 --> 00:20:25,880 So I could have actually just type the IP address in there, but it doesn't matter. 367 00:20:25,910 --> 00:20:28,580 You can type the domain of your grip on a server or the IP address of it. 368 00:20:28,640 --> 00:20:30,990 That is my IP address and that is my domain anyway. 369 00:20:31,010 --> 00:20:35,720 The other thing I should drop all other connections to ports three three, three six. 370 00:20:35,720 --> 00:20:36,130 Like that? 371 00:20:36,140 --> 00:20:37,100 IP tables? 372 00:20:37,160 --> 00:20:39,770 OK, now check that IP tables l OK. 373 00:20:39,770 --> 00:20:43,230 So I'm accepting connections to ports three three zero six. 374 00:20:43,250 --> 00:20:47,960 It's four place step with Moscow just there automatically and dropping everything else. 375 00:20:48,050 --> 00:20:54,630 So the only server that can remotely connect to my Moscow server is microphone a server. 376 00:20:54,650 --> 00:20:57,190 So these are things you should consider now with the username. 377 00:20:57,200 --> 00:21:03,350 It's especially important that that user that is connecting to the server has only read any permissions 378 00:21:03,350 --> 00:21:09,560 so that I can't do escalated commands such as drop database, drop table or read data from tables on 379 00:21:09,560 --> 00:21:10,180 the database. 380 00:21:10,210 --> 00:21:11,430 So these are things to consider. 381 00:21:11,450 --> 00:21:16,660 Anyway, this was a long video, a lot of steps involved in getting a more secure data source to work. 382 00:21:16,670 --> 00:21:21,250 I'm using one kind of dashboard that's called the Mike two simple dashboard. 383 00:21:21,290 --> 00:21:25,520 You will find other the dashboards for Moscow throughout the incident, and they'll all have a different 384 00:21:25,520 --> 00:21:27,480 process for setting them up. 385 00:21:27,500 --> 00:21:30,380 You can create a whole set up yourself. 386 00:21:30,440 --> 00:21:34,040 But this example has shown that it's quite a large jump in skill. 387 00:21:34,040 --> 00:21:38,840 You need to have some very good Moscow knowledge to be able to create a dashboard from the ground up 388 00:21:38,840 --> 00:21:42,620 for a more secure server, and that is the same for any data source you connect to. 389 00:21:42,650 --> 00:21:46,160 So that's why it's important understand that Gryphon doesn't actually exist by itself. 390 00:21:46,160 --> 00:21:52,430 Despite that, refineries actually promoted as a magic tool for everything, getting it to work properly 391 00:21:52,430 --> 00:21:55,820 does require in-depth knowledge of the data source that you're connecting to. 392 00:21:55,880 --> 00:21:59,360 Anyway, we'll move on to other data sources, and that will start to make more sense now. 393 00:21:59,360 --> 00:22:03,830 If you didn't understand all the things in this video doesn't matter, you have the video forever. 394 00:22:03,860 --> 00:22:08,840 It's a long video, and you can move on to other data sources and come back to that when you've had 395 00:22:08,840 --> 00:22:09,800 some time away from it. 396 00:22:09,890 --> 00:22:10,400 Excellent. 397 00:22:10,490 --> 00:22:13,850 In the next few videos, we'll do some more Moscow examples. 398 00:22:14,090 --> 00:22:14,540 Excellent.