1 00:00:00,330 --> 00:00:03,030 So going to credit customizable time series query. 2 00:00:03,060 --> 00:00:08,039 We're going to use the same data collector that we installed when we set up the moisture data source 3 00:00:08,039 --> 00:00:08,760 in the last video. 4 00:00:08,820 --> 00:00:12,510 So this is the diagram that is the collector there. 5 00:00:13,490 --> 00:00:18,140 It is being triggered by the event scheduled every 10 minutes and is running a command show, global 6 00:00:18,140 --> 00:00:24,620 status, plus a few other things, and it's saving that data into my two studies and my two card. 7 00:00:24,680 --> 00:00:30,390 What will do is create a custom query that will read data from the My two studies table. 8 00:00:30,410 --> 00: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. 9 00:00:35,330 --> 00:00:39,110 I'm just going to create the MySchool prompt now just by typing MySchool. 10 00:00:39,260 --> 00:00:40,640 I I have the Moscow prompt. 11 00:00:40,730 --> 00:00:48,230 I'm going to use the Meitu database, use my two and you finish off all your commands with a semicolon. 12 00:00:48,260 --> 00:00:49,610 OK, so database change. 13 00:00:49,610 --> 00:00:53,150 Now if I do show tables, then we go. 14 00:00:53,180 --> 00:00:55,370 Now I'll do a simple query on status. 15 00:00:55,580 --> 00:00:57,680 Select all from. 16 00:00:58,800 --> 00:01:04,440 My two status, and I'll limit it just to 10 rows for now. 17 00:01:04,480 --> 00:01:08,210 OK, so that's a small section of the status table. 18 00:01:08,220 --> 00:01:09,660 I'm going to reorder that. 19 00:01:09,660 --> 00:01:12,850 So I'm getting the most recent first and the top 10 there. 20 00:01:12,870 --> 00:01:16,700 So order by time is to use my time column. 21 00:01:16,710 --> 00:01:21,120 Time is to descending, so I get the most recent first. 22 00:01:21,570 --> 00:01:23,550 OK, so that's just now. 23 00:01:23,550 --> 00:01:27,600 And these are the last ten rows that were saved into that table every 10 minutes. 24 00:01:27,600 --> 00:01:28,890 That information is going to be new. 25 00:01:28,920 --> 00:01:33,210 OK, so this table can be directly read and gravano through the mask, your data source, because it 26 00:01:33,210 --> 00:01:36,660 has at least a time column, at least one column for values. 27 00:01:36,660 --> 00:01:39,890 So it doesn't matter what the name of the column is, we can work with that. 28 00:01:39,900 --> 00:01:43,560 But the important thing is is a date that he can retrieve plus any value. 29 00:01:43,570 --> 00: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 30 00:01:48,930 --> 00:01:54,520 into series, so the series allows you to graph multiple lines on the same visualization. 31 00:01:54,540 --> 00:02:00,120 We look at the Mighta dashboard and we look at say this threads and arrows here threads connected threads 32 00:02:00,120 --> 00:02:01,260 running a board of clients. 33 00:02:01,260 --> 00:02:05,800 They are just different series and we'll find those in the variable name if I search for them. 34 00:02:05,830 --> 00:02:10,259 OK, so there are thousands of rows now that have been written for the last 24 hours, and we can see 35 00:02:10,259 --> 00:02:12,570 that information being shown these graphs. 36 00:02:12,590 --> 00: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 37 00:02:17,460 --> 00:02:19,410 a time column and a value. 38 00:02:19,410 --> 00:02:25,470 At minimum, I can open up the Crafar Explore tab here and query that table directly through the data 39 00:02:25,470 --> 00:02:25,800 source. 40 00:02:25,800 --> 00:02:27,300 So I have my cue your selected. 41 00:02:27,300 --> 00: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. 42 00:02:33,860 --> 00:02:39,000 Okay, so these less than and greater than symbols mean that I need to change this with the name of 43 00:02:39,000 --> 00:02:39,420 the column. 44 00:02:39,420 --> 00: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. 45 00:02:46,200 --> 00:02:51,280 So up here, I can just replace that with time s t as time seconds. 46 00:02:51,310 --> 00:02:57,870 Now Carafano will use that variable name internally, but we're saying pull the data from that column. 47 00:02:58,110 --> 00:03:03,570 So we also have the time filter down here, which also needs the name of the time column time as to 48 00:03:03,600 --> 00:03:04,410 the time filter. 49 00:03:04,410 --> 00:03:07,050 Is this thing here or demonstrate that more in a moment? 50 00:03:07,140 --> 00:03:10,780 Also, Order by time column also needs to be changed as well. 51 00:03:10,810 --> 00:03:15,110 OK, so that's our time stamp column value column. 52 00:03:15,120 --> 00:03:18,210 That's our value column there, and it's called variable value. 53 00:03:18,210 --> 00:03:18,990 I'm going to use that. 54 00:03:19,410 --> 00:03:22,710 So value column variable value as value. 55 00:03:22,740 --> 00:03:28,050 Now the series name column as metric, I'm going to use that column as to series, which is. 56 00:03:29,240 --> 00:03:35,000 Variable name like that from table name being my two dots status. 57 00:03:35,480 --> 00:03:37,900 OK, so I'm querying my TA status there. 58 00:03:37,970 --> 00:03:42,110 Now it's going to limit the amount of rows that are returns using this where statement here, where 59 00:03:42,110 --> 00:03:46,850 time filter time column, it will use the values from there and pass that to the database. 60 00:03:46,880 --> 00:03:52,340 OK, so that query has been successfully run now and we have data showing up down here. 61 00:03:52,940 --> 00:03:56,200 I can change how much data is turned by changing that. 62 00:03:56,210 --> 00:04:00,740 So there's not really anything the last five minutes, but there is something for the last 15 minutes. 63 00:04:00,770 --> 00:04:03,350 Now, if you're familiar with ASCII, well, that looks like an actual statement. 64 00:04:03,350 --> 00:04:06,110 But that statement isn't actually run at the end. 65 00:04:06,110 --> 00:04:09,120 DataSource What is actually running the generated Esquibel here? 66 00:04:09,140 --> 00:04:14,930 So we can see here that that is the actual SQL command that is passed across the network and executed 67 00:04:14,930 --> 00:04:16,430 on the MySchool server. 68 00:04:16,459 --> 00:04:22,550 Now, if we look at this line here where time est between that number and that number, and if I change 69 00:04:22,550 --> 00:04:25,570 the time filter here, we'll see those numbers actually change. 70 00:04:25,580 --> 00:04:27,230 So take note of those numbers now. 71 00:04:27,230 --> 00:04:31,580 If I change that the last 30 minutes, the numbers have actually changed and would change it again to 72 00:04:31,580 --> 00:04:33,260 last five minutes, the numbers have changed. 73 00:04:33,350 --> 00: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. 74 00:04:39,530 --> 00:04:44,420 So I'm on the module, serve up right click to paste and just finished it off with a semicolon. 75 00:04:44,690 --> 00:04:50,960 It's actually returning the same data that Carafano is using to write this table just down here. 76 00:04:51,320 --> 00:04:57,170 Now another thing or hide that now it's drawing that data as a table, even though I have time series 77 00:04:57,170 --> 00:04:57,750 selected this. 78 00:04:57,760 --> 00:05:00,580 So whether I have table or time series, it's the same thing. 79 00:05:00,590 --> 00:05:06,390 In order for the time series to be drawn as a graph, variable value has to be treated as a number. 80 00:05:06,410 --> 00:05:09,630 So right now is trading variable value as a string. 81 00:05:09,650 --> 00: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 82 00:05:15,860 --> 00:05:16,470 like that. 83 00:05:16,490 --> 00:05:20,990 So we're just adding zero to whatever variable value is in behind the scenes that will compare that 84 00:05:20,990 --> 00:05:21,500 to a number. 85 00:05:21,530 --> 00:05:23,270 Now, if I click out of that, it's running. 86 00:05:23,270 --> 00:05:25,430 The query is now drawing it as a graph. 87 00:05:26,560 --> 00:05:32,380 OK, so we're getting a whole lot of series coming back because we're selecting all the series, all 88 00:05:32,380 --> 00:05:35,230 variable names in the query, so we're getting a lot of data there. 89 00:05:35,290 --> 00:05:39,790 That's too much really to show if I just scroll that we can see there are many, many, many, many 90 00:05:39,790 --> 00:05:40,500 examples. 91 00:05:40,510 --> 00: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, 92 00:05:47,920 --> 00:05:50,560 created threads, connected threads running. 93 00:05:50,590 --> 00:05:57,190 I can modify the Esquibel statement here, the Koran aside as clear statement by adding a few more conditions 94 00:05:57,190 --> 00:05:58,490 to what I can return. 95 00:05:58,510 --> 00:06:05,080 So on my documentation here, copy this line that's highlighted in yellow and pasted in. 96 00:06:05,110 --> 00:06:11,650 So where the time filter is, whatever selected up there and the variable name in threads cached connected 97 00:06:11,650 --> 00:06:13,360 threads running or through its credits. 98 00:06:13,360 --> 00:06:15,250 So let's click out of that and run the query. 99 00:06:15,260 --> 00:06:22,420 OK, so now only getting four metrics or four series being returned from all that data in the meta status 100 00:06:22,420 --> 00:06:28,900 table, and it all fits within the time range that I have selected here, which is last 15 minutes. 101 00:06:29,230 --> 00:06:34,140 So let's format that as a table again and see what the table data looks like. 102 00:06:34,150 --> 00:06:39,270 We can see different numbers, a different metric to replace variable name as metric. 103 00:06:39,280 --> 00:06:43,070 So that's why it's this metric there and here variable value has value. 104 00:06:43,090 --> 00:06:46,960 So we're seeing value there and the time s to use time second that we go there. 105 00:06:46,960 --> 00:06:52,190 The column names the co-founder will use internally when it's creating the graph like that. 106 00:06:52,210 --> 00:06:54,130 Now my server isn't very busy. 107 00:06:54,130 --> 00:06:58,570 That's why the graph doesn't look very exciting if I move that down to last twenty four hours. 108 00:06:58,600 --> 00:06:59,620 That's a little more interesting. 109 00:06:59,620 --> 00:07:05,740 So any way you can see that the generated SQL now looks like that, with a time range being between 110 00:07:05,740 --> 00:07:06,880 that number and that number. 111 00:07:06,910 --> 00:07:12,250 If you actually want to know what that number actually means after it's converted to a date time, you 112 00:07:12,250 --> 00:07:18,190 can copy that go to your favorite search engine and type in something like long to date time and you'll 113 00:07:18,190 --> 00:07:24,400 get an epoch, but you can paste that in and press that, and that's that number converted to a daytime 114 00:07:24,400 --> 00:07:24,760 string. 115 00:07:24,790 --> 00: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. 116 00:07:30,430 --> 00:07:35,710 Explore tab is good because you can try all kinds of things out and make mistakes and fix them up or 117 00:07:35,710 --> 00:07:39,960 go backwards forwards until you're satisfied that you have a query you like. 118 00:07:39,970 --> 00:07:41,590 So I'm satisfied that query is good. 119 00:07:41,590 --> 00:07:42,630 So I'm going to copy that. 120 00:07:42,640 --> 00:07:47,800 I'm just going to create a new dashboard so I don't ruin my dashboard from the last video. 121 00:07:47,800 --> 00:07:49,020 Add an empty panel. 122 00:07:49,030 --> 00:07:51,160 I've got time series selected here. 123 00:07:51,160 --> 00:07:57,730 I'm going to select my skill, going to go into the school mode, select all paste and then just click 124 00:07:57,730 --> 00:07:58,900 out of that so that the bonds. 125 00:07:58,930 --> 00:07:59,200 Okay. 126 00:07:59,200 --> 00:08:04,090 So I can now modify the styles of my graph to be whatever I like. 127 00:08:04,120 --> 00:08:06,160 I can say that I'm happy with that. 128 00:08:06,160 --> 00:08:08,980 So far, I'm going to apply that and navigate. 129 00:08:08,980 --> 00:08:15,010 My new dashboard has a graph created from a customized keyword query that I've created using the Explore 130 00:08:15,040 --> 00:08:15,690 option here. 131 00:08:16,030 --> 00:08:17,500 So that's just a start. 132 00:08:17,530 --> 00:08:18,730 You have to start somewhere. 133 00:08:18,760 --> 00:08:25,600 I can now start creating a dashboard that suits my needs based on the information that is being saved 134 00:08:25,600 --> 00:08:27,100 into the My two status table. 135 00:08:27,100 --> 00:08:31,330 Using that collector, you don't have to use that collector if you want, but it's actually got a whole 136 00:08:31,330 --> 00:08:33,020 lot of data that it's very useful already. 137 00:08:33,039 --> 00:08:39,159 Just be aware that whatever table you read from in Moscow, this can go backwards, needs to have a 138 00:08:39,159 --> 00:08:41,200 time column and a value column. 139 00:08:41,200 --> 00: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. 140 00:08:45,760 --> 00:08:46,900 And that's what I'm showing here. 141 00:08:46,990 --> 00:08:52,300 Just showing those metrics or series those values are those timestamps. 142 00:08:52,330 --> 00: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 143 00:08:59,590 --> 00:09:01,630 twenty four hours, for example. 144 00:09:02,350 --> 00:09:08,200 You can look at all of these visualizations to find out the query behind them to have a better idea 145 00:09:08,200 --> 00:09:09,610 of how it was put together. 146 00:09:09,730 --> 00:09:14,920 For example, in our DB cache, hit their press a to look at it, and that is the query. 147 00:09:14,920 --> 00:09:18,040 So that's a little more sophisticated than the query that I wrote. 148 00:09:18,070 --> 00: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 149 00:09:23,380 --> 00:09:26,290 variable names being those if we go backwards. 150 00:09:26,530 --> 00:09:33,040 There is the heatmap, which is also a much more sophisticated query using aggregates such as some and 151 00:09:33,040 --> 00:09:33,920 group by as well. 152 00:09:33,940 --> 00:09:38,650 So as you can see, it'll get gradually more complicated the more that you want from your visualization, 153 00:09:38,650 --> 00:09:40,030 but you have to start somewhere. 154 00:09:40,540 --> 00:09:47,110 And what I've demonstrated is really something very similar to this one here threats and errors, except 155 00:09:47,110 --> 00:09:53,050 I've got a few extra variables that I'm clicking there and I'm not using a second query. 156 00:09:53,410 --> 00:09:56,320 I'm just using one, and the style of my graph is also different. 157 00:09:56,920 --> 00:10:01,990 Leave that back into dashboards and click it again, dashboard three and then sit there and you can 158 00:10:01,990 --> 00:10:06,370 always edit that and modify your query here or take it out. 159 00:10:06,370 --> 00:10:15,460 Copy and go into the Explore, Edit, Slept or paste and modify it or tweak it using the Explore tab. 160 00:10:15,850 --> 00:10:16,210 Excellent. 161 00:10:16,220 --> 00:10:22,410 So that's a MySchool query, where we were pulling data manually from our MySQL database. 162 00:10:22,420 --> 00:10:25,960 I just happened to be using the my two status table because that's been. 163 00:10:26,090 --> 00:10:28,990 Populated by a collector that we installed in the last video. 164 00:10:29,020 --> 00:10:33,670 So I already have something that I can use, but you don't have to be reading from those tables. 165 00:10:33,670 --> 00:10:39,100 You can read many Typekit you like, provided it is something that has a time stamp in it and I value 166 00:10:39,100 --> 00:10:39,790 at minimum. 167 00:10:39,790 --> 00: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 168 00:10:46,990 --> 00:10:48,880 have a timestamp anyway. 169 00:10:48,910 --> 00:10:49,480 Next VIDEO. 170 00:10:49,570 --> 00:10:49,990 Excellent.