So going to credit customizable time series query.
We're going to use the same data collector that we installed when we set up the moisture data source
in the last video.
So this is the diagram that is the collector there.
It is being triggered by the event scheduled every 10 minutes and is running a command show, global
status, plus a few other things, and it's saving that data into my two studies and my two card.
What will do is create a custom query that will read data from the My two studies table.
Let's have a look at the more to set a table saw this as I sat on to my MySchool server.
I'm just going to create the MySchool prompt now just by typing MySchool.
I I have the Moscow prompt.
I'm going to use the Meitu database, use my two and you finish off all your commands with a semicolon.
OK, so database change.
Now if I do show tables, then we go.
Now I'll do a simple query on status.
Select all from.
My two status, and I'll limit it just to 10 rows for now.
OK, so that's a small section of the status table.
I'm going to reorder that.
So I'm getting the most recent first and the top 10 there.
So order by time is to use my time column.
Time is to descending, so I get the most recent first.
OK, so that's just now.
And these are the last ten rows that were saved into that table every 10 minutes.
That information is going to be new.
OK, so this table can be directly read and gravano through the mask, your data source, because it
has at least a time column, at least one column for values.
So it doesn't matter what the name of the column is, we can work with that.
But the important thing is is a date that he can retrieve plus any value.
But this table also has a name for each of the values, and these names can be used to group the data
into series, so the series allows you to graph multiple lines on the same visualization.
We look at the Mighta dashboard and we look at say this threads and arrows here threads connected threads
running a board of clients.
They are just different series and we'll find those in the variable name if I search for them.
OK, so there are thousands of rows now that have been written for the last 24 hours, and we can see
that information being shown these graphs.
OK, so now that I'm happy that I've got a table that I can query in Carafano, there's a table with
a time column and a value.
At minimum, I can open up the Crafar Explore tab here and query that table directly through the data
source.
So I have my cue your selected.
I'm going to go into edit SQL and I'm given a template that I'll need to modify for my own needs.
Okay, so these less than and greater than symbols mean that I need to change this with the name of
the column.
So the name of my time column in the SQL in the end, datasource is time T, so I'm going to use that.
So up here, I can just replace that with time s t as time seconds.
Now Carafano will use that variable name internally, but we're saying pull the data from that column.
So we also have the time filter down here, which also needs the name of the time column time as to
the time filter.
Is this thing here or demonstrate that more in a moment?
Also, Order by time column also needs to be changed as well.
OK, so that's our time stamp column value column.
That's our value column there, and it's called variable value.
I'm going to use that.
So value column variable value as value.
Now the series name column as metric, I'm going to use that column as to series, which is.
Variable name like that from table name being my two dots status.
OK, so I'm querying my TA status there.
Now it's going to limit the amount of rows that are returns using this where statement here, where
time filter time column, it will use the values from there and pass that to the database.
OK, so that query has been successfully run now and we have data showing up down here.
I can change how much data is turned by changing that.
So there's not really anything the last five minutes, but there is something for the last 15 minutes.
Now, if you're familiar with ASCII, well, that looks like an actual statement.
But that statement isn't actually run at the end.
DataSource What is actually running the generated Esquibel here?
So we can see here that that is the actual SQL command that is passed across the network and executed
on the MySchool server.
Now, if we look at this line here where time est between that number and that number, and if I change
the time filter here, we'll see those numbers actually change.
So take note of those numbers now.
If I change that the last 30 minutes, the numbers have actually changed and would change it again to
last five minutes, the numbers have changed.
So if I do last 15 minutes and if I copy that, I can actually run that on the Moscow server directly.
So I'm on the module, serve up right click to paste and just finished it off with a semicolon.
It's actually returning the same data that Carafano is using to write this table just down here.
Now another thing or hide that now it's drawing that data as a table, even though I have time series
selected this.
So whether I have table or time series, it's the same thing.
In order for the time series to be drawn as a graph, variable value has to be treated as a number.
So right now is trading variable value as a string.
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
like that.
So we're just adding zero to whatever variable value is in behind the scenes that will compare that
to a number.
Now, if I click out of that, it's running.
The query is now drawing it as a graph.
OK, so we're getting a whole lot of series coming back because we're selecting all the series, all
variable names in the query, so we're getting a lot of data there.
That's too much really to show if I just scroll that we can see there are many, many, many, many
examples.
I want to just limit the amount of metrics that are coming back or series to just a few, such as threads,
created threads, connected threads running.
I can modify the Esquibel statement here, the Koran aside as clear statement by adding a few more conditions
to what I can return.
So on my documentation here, copy this line that's highlighted in yellow and pasted in.
So where the time filter is, whatever selected up there and the variable name in threads cached connected
threads running or through its credits.
So let's click out of that and run the query.
OK, so now only getting four metrics or four series being returned from all that data in the meta status
table, and it all fits within the time range that I have selected here, which is last 15 minutes.
So let's format that as a table again and see what the table data looks like.
We can see different numbers, a different metric to replace variable name as metric.
So that's why it's this metric there and here variable value has value.
So we're seeing value there and the time s to use time second that we go there.
The column names the co-founder will use internally when it's creating the graph like that.
Now my server isn't very busy.
That's why the graph doesn't look very exciting if I move that down to last twenty four hours.
That's a little more interesting.
So any way you can see that the generated SQL now looks like that, with a time range being between
that number and that number.
If you actually want to know what that number actually means after it's converted to a date time, you
can copy that go to your favorite search engine and type in something like long to date time and you'll
get an epoch, but you can paste that in and press that, and that's that number converted to a daytime
string.
OK, so now we have a query that we can work with, and I've used the Explore tab to create that query.
Explore tab is good because you can try all kinds of things out and make mistakes and fix them up or
go backwards forwards until you're satisfied that you have a query you like.
So I'm satisfied that query is good.
So I'm going to copy that.
I'm just going to create a new dashboard so I don't ruin my dashboard from the last video.
Add an empty panel.
I've got time series selected here.
I'm going to select my skill, going to go into the school mode, select all paste and then just click
out of that so that the bonds.
Okay.
So I can now modify the styles of my graph to be whatever I like.
I can say that I'm happy with that.
So far, I'm going to apply that and navigate.
My new dashboard has a graph created from a customized keyword query that I've created using the Explore
option here.
So that's just a start.
You have to start somewhere.
I can now start creating a dashboard that suits my needs based on the information that is being saved
into the My two status table.
Using that collector, you don't have to use that collector if you want, but it's actually got a whole
lot of data that it's very useful already.
Just be aware that whatever table you read from in Moscow, this can go backwards, needs to have a
time column and a value column.
And if it has something that you can use for the metric name or the series name, then that's even better.
And that's what I'm showing here.
Just showing those metrics or series those values are those timestamps.
OK, so I'm going to save that, save that pre and go into the My two dashboard, the Chase app, for
twenty four hours, for example.
You can look at all of these visualizations to find out the query behind them to have a better idea
of how it was put together.
For example, in our DB cache, hit their press a to look at it, and that is the query.
So that's a little more sophisticated than the query that I wrote.
It's using a group by course, and it's got quite a few conditions on what it should return with those
variable names being those if we go backwards.
There is the heatmap, which is also a much more sophisticated query using aggregates such as some and
group by as well.
So as you can see, it'll get gradually more complicated the more that you want from your visualization,
but you have to start somewhere.
And what I've demonstrated is really something very similar to this one here threats and errors, except
I've got a few extra variables that I'm clicking there and I'm not using a second query.
I'm just using one, and the style of my graph is also different.
Leave that back into dashboards and click it again, dashboard three and then sit there and you can
always edit that and modify your query here or take it out.
Copy and go into the Explore, Edit, Slept or paste and modify it or tweak it using the Explore tab.
Excellent.
So that's a MySchool query, where we were pulling data manually from our MySQL database.
I just happened to be using the my two status table because that's been.
Populated by a collector that we installed in the last video.
So I already have something that I can use, but you don't have to be reading from those tables.
You can read many Typekit you like, provided it is something that has a time stamp in it and I value
at minimum.
Anyway, in the next video, I'll show you how to expand on that and graph data from a table that doesn't
have a timestamp anyway.
Next VIDEO.
Excellent.