WEBVTT 00:00.330 --> 00:03.030 So going to credit customizable time series query. 00:03.060 --> 00:08.040 We're going to use the same data collector that we installed when we set up the moisture data source 00:08.040 --> 00:08.760 in the last video. 00:08.820 --> 00:12.510 So this is the diagram that is the collector there. 00:13.490 --> 00:18.140 It is being triggered by the event scheduled every 10 minutes and is running a command show, global 00:18.140 --> 00:24.620 status, plus a few other things, and it's saving that data into my two studies and my two card. 00:24.680 --> 00:30.390 What will do is create a custom query that will read data from the My two studies table. 00:30.410 --> 00:35.320 Let's have a look at the more to set a table saw this as I sat on to my MySchool server. 00:35.330 --> 00:39.110 I'm just going to create the MySchool prompt now just by typing MySchool. 00:39.260 --> 00:40.640 I I have the Moscow prompt. 00:40.730 --> 00:48.230 I'm going to use the Meitu database, use my two and you finish off all your commands with a semicolon. 00:48.260 --> 00:49.610 OK, so database change. 00:49.610 --> 00:53.150 Now if I do show tables, then we go. 00:53.180 --> 00:55.370 Now I'll do a simple query on status. 00:55.580 --> 00:57.680 Select all from. 00:58.800 --> 01:04.440 My two status, and I'll limit it just to 10 rows for now. 01:04.480 --> 01:08.210 OK, so that's a small section of the status table. 01:08.220 --> 01:09.660 I'm going to reorder that. 01:09.660 --> 01:12.850 So I'm getting the most recent first and the top 10 there. 01:12.870 --> 01:16.700 So order by time is to use my time column. 01:16.710 --> 01:21.120 Time is to descending, so I get the most recent first. 01:21.570 --> 01:23.550 OK, so that's just now. 01:23.550 --> 01:27.600 And these are the last ten rows that were saved into that table every 10 minutes. 01:27.600 --> 01:28.890 That information is going to be new. 01:28.920 --> 01:33.210 OK, so this table can be directly read and gravano through the mask, your data source, because it 01:33.210 --> 01:36.660 has at least a time column, at least one column for values. 01:36.660 --> 01:39.890 So it doesn't matter what the name of the column is, we can work with that. 01:39.900 --> 01:43.560 But the important thing is is a date that he can retrieve plus any value. 01:43.570 --> 01:48.930 But this table also has a name for each of the values, and these names can be used to group the data 01:48.930 --> 01:54.520 into series, so the series allows you to graph multiple lines on the same visualization. 01:54.540 --> 02:00.120 We look at the Mighta dashboard and we look at say this threads and arrows here threads connected threads 02:00.120 --> 02:01.260 running a board of clients. 02:01.260 --> 02:05.800 They are just different series and we'll find those in the variable name if I search for them. 02:05.830 --> 02:10.260 OK, so there are thousands of rows now that have been written for the last 24 hours, and we can see 02:10.260 --> 02:12.570 that information being shown these graphs. 02:12.590 --> 02:17.460 OK, so now that I'm happy that I've got a table that I can query in Carafano, there's a table with 02:17.460 --> 02:19.410 a time column and a value. 02:19.410 --> 02:25.470 At minimum, I can open up the Crafar Explore tab here and query that table directly through the data 02:25.470 --> 02:25.800 source. 02:25.800 --> 02:27.300 So I have my cue your selected. 02:27.300 --> 02:33.840 I'm going to go into edit SQL and I'm given a template that I'll need to modify for my own needs. 02:33.860 --> 02:39.000 Okay, so these less than and greater than symbols mean that I need to change this with the name of 02:39.000 --> 02:39.420 the column. 02:39.420 --> 02:46.160 So the name of my time column in the SQL in the end, datasource is time T, so I'm going to use that. 02:46.200 --> 02:51.280 So up here, I can just replace that with time s t as time seconds. 02:51.310 --> 02:57.870 Now Carafano will use that variable name internally, but we're saying pull the data from that column. 02:58.110 --> 03:03.570 So we also have the time filter down here, which also needs the name of the time column time as to 03:03.600 --> 03:04.410 the time filter. 03:04.410 --> 03:07.050 Is this thing here or demonstrate that more in a moment? 03:07.140 --> 03:10.780 Also, Order by time column also needs to be changed as well. 03:10.810 --> 03:15.110 OK, so that's our time stamp column value column. 03:15.120 --> 03:18.210 That's our value column there, and it's called variable value. 03:18.210 --> 03:18.990 I'm going to use that. 03:19.410 --> 03:22.710 So value column variable value as value. 03:22.740 --> 03:28.050 Now the series name column as metric, I'm going to use that column as to series, which is. 03:29.240 --> 03:35.000 Variable name like that from table name being my two dots status. 03:35.480 --> 03:37.900 OK, so I'm querying my TA status there. 03:37.970 --> 03:42.110 Now it's going to limit the amount of rows that are returns using this where statement here, where 03:42.110 --> 03:46.850 time filter time column, it will use the values from there and pass that to the database. 03:46.880 --> 03:52.340 OK, so that query has been successfully run now and we have data showing up down here. 03:52.940 --> 03:56.200 I can change how much data is turned by changing that. 03:56.210 --> 04:00.740 So there's not really anything the last five minutes, but there is something for the last 15 minutes. 04:00.770 --> 04:03.350 Now, if you're familiar with ASCII, well, that looks like an actual statement. 04:03.350 --> 04:06.110 But that statement isn't actually run at the end. 04:06.110 --> 04:09.120 DataSource What is actually running the generated Esquibel here? 04:09.140 --> 04:14.930 So we can see here that that is the actual SQL command that is passed across the network and executed 04:14.930 --> 04:16.430 on the MySchool server. 04:16.460 --> 04:22.550 Now, if we look at this line here where time est between that number and that number, and if I change 04:22.550 --> 04:25.570 the time filter here, we'll see those numbers actually change. 04:25.580 --> 04:27.230 So take note of those numbers now. 04:27.230 --> 04:31.580 If I change that the last 30 minutes, the numbers have actually changed and would change it again to 04:31.580 --> 04:33.260 last five minutes, the numbers have changed. 04:33.350 --> 04:39.530 So if I do last 15 minutes and if I copy that, I can actually run that on the Moscow server directly. 04:39.530 --> 04:44.420 So I'm on the module, serve up right click to paste and just finished it off with a semicolon. 04:44.690 --> 04:50.960 It's actually returning the same data that Carafano is using to write this table just down here. 04:51.320 --> 04:57.170 Now another thing or hide that now it's drawing that data as a table, even though I have time series 04:57.170 --> 04:57.750 selected this. 04:57.760 --> 05:00.580 So whether I have table or time series, it's the same thing. 05:00.590 --> 05:06.390 In order for the time series to be drawn as a graph, variable value has to be treated as a number. 05:06.410 --> 05:09.630 So right now is trading variable value as a string. 05:09.650 --> 05:15.860 So the quickest way to convert a string that looks like a no to a no is to add a plus zero at the end 05:15.860 --> 05:16.470 like that. 05:16.490 --> 05:20.990 So we're just adding zero to whatever variable value is in behind the scenes that will compare that 05:20.990 --> 05:21.500 to a number. 05:21.530 --> 05:23.270 Now, if I click out of that, it's running. 05:23.270 --> 05:25.430 The query is now drawing it as a graph. 05:26.560 --> 05:32.380 OK, so we're getting a whole lot of series coming back because we're selecting all the series, all 05:32.380 --> 05:35.230 variable names in the query, so we're getting a lot of data there. 05:35.290 --> 05:39.790 That's too much really to show if I just scroll that we can see there are many, many, many, many 05:39.790 --> 05:40.500 examples. 05:40.510 --> 05:47.920 I want to just limit the amount of metrics that are coming back or series to just a few, such as threads, 05:47.920 --> 05:50.560 created threads, connected threads running. 05:50.590 --> 05:57.190 I can modify the Esquibel statement here, the Koran aside as clear statement by adding a few more conditions 05:57.190 --> 05:58.490 to what I can return. 05:58.510 --> 06:05.080 So on my documentation here, copy this line that's highlighted in yellow and pasted in. 06:05.110 --> 06:11.650 So where the time filter is, whatever selected up there and the variable name in threads cached connected 06:11.650 --> 06:13.360 threads running or through its credits. 06:13.360 --> 06:15.250 So let's click out of that and run the query. 06:15.260 --> 06:22.420 OK, so now only getting four metrics or four series being returned from all that data in the meta status 06:22.420 --> 06:28.900 table, and it all fits within the time range that I have selected here, which is last 15 minutes. 06:29.230 --> 06:34.140 So let's format that as a table again and see what the table data looks like. 06:34.150 --> 06:39.270 We can see different numbers, a different metric to replace variable name as metric. 06:39.280 --> 06:43.070 So that's why it's this metric there and here variable value has value. 06:43.090 --> 06:46.960 So we're seeing value there and the time s to use time second that we go there. 06:46.960 --> 06:52.190 The column names the co-founder will use internally when it's creating the graph like that. 06:52.210 --> 06:54.130 Now my server isn't very busy. 06:54.130 --> 06:58.570 That's why the graph doesn't look very exciting if I move that down to last twenty four hours. 06:58.600 --> 06:59.620 That's a little more interesting. 06:59.620 --> 07:05.740 So any way you can see that the generated SQL now looks like that, with a time range being between 07:05.740 --> 07:06.880 that number and that number. 07:06.910 --> 07:12.250 If you actually want to know what that number actually means after it's converted to a date time, you 07:12.250 --> 07:18.190 can copy that go to your favorite search engine and type in something like long to date time and you'll 07:18.190 --> 07:24.400 get an epoch, but you can paste that in and press that, and that's that number converted to a daytime 07:24.400 --> 07:24.760 string. 07:24.790 --> 07:30.400 OK, so now we have a query that we can work with, and I've used the Explore tab to create that query. 07:30.430 --> 07:35.710 Explore tab is good because you can try all kinds of things out and make mistakes and fix them up or 07:35.710 --> 07:39.960 go backwards forwards until you're satisfied that you have a query you like. 07:39.970 --> 07:41.590 So I'm satisfied that query is good. 07:41.590 --> 07:42.630 So I'm going to copy that. 07:42.640 --> 07:47.800 I'm just going to create a new dashboard so I don't ruin my dashboard from the last video. 07:47.800 --> 07:49.020 Add an empty panel. 07:49.030 --> 07:51.160 I've got time series selected here. 07:51.160 --> 07:57.730 I'm going to select my skill, going to go into the school mode, select all paste and then just click 07:57.730 --> 07:58.900 out of that so that the bonds. 07:58.930 --> 07:59.200 Okay. 07:59.200 --> 08:04.090 So I can now modify the styles of my graph to be whatever I like. 08:04.120 --> 08:06.160 I can say that I'm happy with that. 08:06.160 --> 08:08.980 So far, I'm going to apply that and navigate. 08:08.980 --> 08:15.010 My new dashboard has a graph created from a customized keyword query that I've created using the Explore 08:15.040 --> 08:15.690 option here. 08:16.030 --> 08:17.500 So that's just a start. 08:17.530 --> 08:18.730 You have to start somewhere. 08:18.760 --> 08:25.600 I can now start creating a dashboard that suits my needs based on the information that is being saved 08:25.600 --> 08:27.100 into the My two status table. 08:27.100 --> 08:31.330 Using that collector, you don't have to use that collector if you want, but it's actually got a whole 08:31.330 --> 08:33.020 lot of data that it's very useful already. 08:33.040 --> 08:39.160 Just be aware that whatever table you read from in Moscow, this can go backwards, needs to have a 08:39.160 --> 08:41.200 time column and a value column. 08:41.200 --> 08:45.670 And if it has something that you can use for the metric name or the series name, then that's even better. 08:45.760 --> 08:46.900 And that's what I'm showing here. 08:46.990 --> 08:52.300 Just showing those metrics or series those values are those timestamps. 08:52.330 --> 08:59.590 OK, so I'm going to save that, save that pre and go into the My two dashboard, the Chase app, for 08:59.590 --> 09:01.630 twenty four hours, for example. 09:02.350 --> 09:08.200 You can look at all of these visualizations to find out the query behind them to have a better idea 09:08.200 --> 09:09.610 of how it was put together. 09:09.730 --> 09:14.920 For example, in our DB cache, hit their press a to look at it, and that is the query. 09:14.920 --> 09:18.040 So that's a little more sophisticated than the query that I wrote. 09:18.070 --> 09:23.380 It's using a group by course, and it's got quite a few conditions on what it should return with those 09:23.380 --> 09:26.290 variable names being those if we go backwards. 09:26.530 --> 09:33.040 There is the heatmap, which is also a much more sophisticated query using aggregates such as some and 09:33.040 --> 09:33.920 group by as well. 09:33.940 --> 09:38.650 So as you can see, it'll get gradually more complicated the more that you want from your visualization, 09:38.650 --> 09:40.030 but you have to start somewhere. 09:40.540 --> 09:47.110 And what I've demonstrated is really something very similar to this one here threats and errors, except 09:47.110 --> 09:53.050 I've got a few extra variables that I'm clicking there and I'm not using a second query. 09:53.410 --> 09:56.320 I'm just using one, and the style of my graph is also different. 09:56.920 --> 10:01.990 Leave that back into dashboards and click it again, dashboard three and then sit there and you can 10:01.990 --> 10:06.370 always edit that and modify your query here or take it out. 10:06.370 --> 10:15.460 Copy and go into the Explore, Edit, Slept or paste and modify it or tweak it using the Explore tab. 10:15.850 --> 10:16.210 Excellent. 10:16.220 --> 10:22.410 So that's a MySchool query, where we were pulling data manually from our MySQL database. 10:22.420 --> 10:25.960 I just happened to be using the my two status table because that's been. 10:26.090 --> 10:28.990 Populated by a collector that we installed in the last video. 10:29.020 --> 10:33.670 So I already have something that I can use, but you don't have to be reading from those tables. 10:33.670 --> 10:39.100 You can read many Typekit you like, provided it is something that has a time stamp in it and I value 10:39.100 --> 10:39.790 at minimum. 10:39.790 --> 10:46.990 Anyway, in the next video, I'll show you how to expand on that and graph data from a table that doesn't 10:46.990 --> 10:48.880 have a timestamp anyway. 10:48.910 --> 10:49.480 Next VIDEO. 10:49.570 --> 10:49.990 Excellent.