In this video, we get a chat about datasets.
And a really good analogy for a dataset is thinking about walking into a
restaurant.
Let's do that.
Let's imagine you and I are walking into a restaurant and we get seated and we
probably
want to order some food.
So how do we know what's available?
Well we have a menu.
And then from that menu, what would we do?
We would select certain information or certain details from that menu.
And that is a very close analogy regarding a dataset in the world of 40 analy
zer as we're WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
zer as we're
pulling information from the SQL database.
So the SQL database would represent everything that's possibly on the menu.
And what we're doing is we're selecting specific parts or pieces from that SQL
database.
Now also at the restaurant, going back to the menu analogy, we may have some
restrictions
on our diets.
For example, we may not be tolerant to gluten or we may not be able to have
nuts.
We may have a nut allergy or something.
So in addition to selecting from the menu, we'd also want to do some filtering
and say,
you know what, if there are any nuts, I'll go ahead and put a no symbol there, WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
you know what, if there are any nuts, I'll go ahead and put a no symbol there,
no nuts.
That would also be a consideration regarding things we're going to order from
the menu.
Or perhaps there's some element that they can actually remove.
So as a filter, we could say no nuts and that would also restrict what we're
going to get.
So the dataset is all about choosing or selecting data and pulling it from the
40 analyzer
that would then go into our report and then based on the charts and the other
layouts
inside of that report, that data from the 40 analyzer SQL database based on the
select
statement would populate that report. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
statement would populate that report.
And again, in the world of Fortinet, they refer to that as a dataset.
Again, behind the scenes, it's just a select statement with considerations
against the
SQL database.
And also if we're using macros, think of that like a subset of the datasets,
little mini
me versions of datasets looking for very refined, small pieces of information.
And because they can be predefined, we can pull and use them over and over
again.
So here's an example of a dataset.
Let's imagine that we need to create a custom dataset, although there's lots WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
Let's imagine that we need to create a custom dataset, although there's lots
and lots of
them already in place.
But let's imagine we want to create a wrong and let's imagine we want to look
for and pull
from the log files.
So the way we represent log files is a dollar sign log.
And then we use a select statement says pull from the log.
Now the log isn't literally the log anymore.
It's pulling from the SQL database in a database table that has been built
based on the log
information that came in to the 40 analyzer.
So the dataset is 100% all built on pulling data from the SQL database, not
from raw log
files. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
files.
Then we could put a filter in place like narrowing it down saying you know what
, instead of just
pulling everything from the log tables, let's go ahead and say filter where the
category
description equals let's go ahead and say proxy avoidance.
And when we put in the syntax, we're going to put an open and close single
quote there
because we're going to put a little space between proxy and avoidance.
And that way it knows we want to look for that exact category description.
So the single open quote and the single end quote here, that tells the select
statement
as part of the SQL database and what we're looking for, we're looking for just
the category
description of proxy avoidance. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
description of proxy avoidance.
And then we could group the upcoming information based on source IP and host
name and other
elements as well.
So when we build a report behind the scenes, it's also including a dataset that
's pulling
in the correct information so it can populate the charts and tables and so
forth in the
final report.
And for individuals who are comfortable, which is not the majority of people on
the planet,
but for people who are comfortable with SQL with SQL language and the actual
syntax for
SQL databases, they're going to have a much easier time in creating their own
custom datasets WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
custom datasets
if they already understand the SQL queries and how those commands are
structured.
For those people who are new to SQL queries, it's not going to be as easy, but
the good
news is this probably it don't have to create too many custom datasets, which
equate to
custom queries because on the 40 analyzer, there's tons and tons of datasets
already
predefined, ready to rock and roll.
So to help reinforce the concept of a dataset and how it's used, let's go back
to the 40
analyzer once again.
And here we go.
So back at the 40 analyzer, we'll back down to reports, expand that. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
So back at the 40 analyzer, we'll back down to reports, expand that.
And with report definition selected, we have all reports, templates, chart
library, macro
library, and then on the far right here, datasets.
So currently with this flavor of the 40 analyzer, there's close to 2000.
Look at the bottom right hand corner there close to 2000 pre built, ready to go
datasets.
So here with all these predefined datasets is showing the log type, for example
, where
the data would be coming from, so also showing over here, origin, in which WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
the data would be coming from, so also showing over here, origin, in which
means these are
all built in.
We also have options here.
If we click on view options, we can go ahead and say, I only want to see custom
, for
example, and we don't have any custom datasets yet, or we can say, I only want
to see the
built in ones, and they'll do it here.
And we have options for just seeing the 40 guard ones as well.
So I'll go ahead and say, let's show all of them.
And as an example, let's go ahead and grab one.
So how about this one right here, 360 security applications by bandwidth.
Now we hover there, it's actually showing us the select statement that it's WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
Now we hover there, it's actually showing us the select statement that it's
using with
all of its conditions regarding that dataset.
So think heavens, if we want to use that, we wouldn't have to create our own.
We could use this dataset.
Or if we go to another one, let's scroll down a little bit, how about right
here?
App risk virus discovered.
If we hover here and the pop up, it's showing us the actual SQL statement.
So if we double click, we also can take a closer look.
So here's the actual query that would bring all that back in.
And if we're creating our own, one of the other cool things is we can go ahead
and click
on validate, and it was just verified that our SQL query is correct and not WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
on validate, and it was just verified that our SQL query is correct and not
missing any
parts.
There's also an option here to analyze query.
So here's the original query.
Again, this is the default one that came with it.
Here's the transform SQL and it says, well, you know what, the one we gave you
is pretty
darn good.
We don't need to transform it.
And then there's an H-cache query list.
And what an H-cache query is, it's a query that's been done in advance.
So when you run a report, if it's already cached, the query is already cached,
the report can
be generated a lot faster.
So again, this query is perfect because it was provided by 40 analyzer, by Fort WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
So again, this query is perfect because it was provided by 40 analyzer, by Fort
inet.
So we'll click on cancel there.
And just as an example of creating a data set, if we needed to, probably one of
the best
options would be to right click one that comes close to what you need, clone it
, and then
go ahead and customize it.
Or if you want to create one from scratch, we can do that as well.
With data set selected, click here on create new.
We'll call our data set looking for proxy avoidance.
So for the log type, we'll click the drop down here and we'll go down and we'll WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
So for the log type, we'll click the drop down here and we'll go down and we'll
select
web filter, because that's what I want to look for, for the web category of
proxy avoidance.
And then here we would put in our SQL query.
So let's have some fun with this.
I'm going to type in select, which means go ahead and pull the data.
So I want to pull from source IP.
You can feel like a column of data in the SQL database.
I want to pull from source IP column and the host name column and also the
category description
column.
So for this next statement, I'm going to put in select source IP, host name,
and category
description. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
description.
But I have to tell where we're pulling from.
So here I'll type in from space.
And then I want to go ahead and pull from log, which is a table in the SQL
database.
So think of it like I want to look at or get the source IP information, host
name information,
category description information, think of those like columns from this
database called
log.
And if we hover here, it's also showing us all the various fields available.
So if we sorted through all those, we would find source IP, host name, and
category description WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
category description
as three of those.
So for readability, I'll press enter.
And then let's go ahead and put a filter in place because we don't want to see
all the
information from the log database regarding source IP, host name, and category
description.
We want to filter it down.
So I'm going to type in where space and then I'll put a dollar sign filter,
another space.
And after dollar sign filter, I'm also going to go ahead and put an and
category description.
So I'll select that here from the drop down, put in our space, where I have ADN
, I need
to fix that.
And it's not ADN. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
And it's not ADN.
So here I have where filter and category description, and I'll say equals, and
I have a single
quote and proxy avoidance and a close single quote.
And then we can group them.
Then I'll say group by source IP.
I'll pick that from the list there after I start typing, comma, then host name,
I'll
select that and comma, category description and select that.
I'll press enter one more time, then I'll type in order by and I'll go ahead
and order
by source IP and that looks good.
Now let me tell you what, in prepping for this, it's been a long time since I WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
Now let me tell you what, in prepping for this, it's been a long time since I
've had
to write any SQL statements whatsoever.
So I use some help here with the validate options.
I'll click on validate and it would tell me, oh, we need this.
Oh, we need that.
Oh, that doesn't make sense.
So after several practices in a simple demo here, I finally got my syntax
correct and
the validate option here helped me as well.
We could also click on format and that's just formats.
It's the same query, but just done in a slightly different look and feel, which
might make
it easier to read.
So once again, I'll click on validate here just to confirm I have all the WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
So once again, I'll click on validate here just to confirm I have all the
syntax it needs
and it says no validation issues found.
Now another cool thing here is we could actually test it.
So I'll scroll down a little bit and I'm going to say for the time period, let
's go ahead
and let's say anytime this year, it currently is 2025 and so I'd be from
January 1st all
the way to the end of the year and I'll click on go.
And that way you just validate for me that this dataset, which effectively is a
SQL query
with some filtering in place is actually working.
So if we scroll down here, sure enough, here's the source IP. WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
So if we scroll down here, sure enough, here's the source IP.
So I don't have IPv6 in place and that's what this represents right here, but I
do have
IPv4 addresses on that device.
Here's the host name where the proxy avoidance was triggered and there's the
category description.
So I know that this query works and we could use it.
So I'll go ahead and click on okay and now if we click here on view options and
we just
take a look at custom datasets, there's our custom dataset and if we hover over
it, it's
showing us what that query is looking for.
So now that we have this dataset in place, we could then use it as part of a WEBVTT X-TIMESTAMP-MAP=LOCAL:00:00:00.000,MPEGTS:900000
So now that we have this dataset in place, we could then use it as part of a
report that
we're creating.
However, before we get to that point, I also want to walk you through how we
can work with
existing charts or create new charts as well.
So in the next video, let's take a look at the chart library and many of the
options
we have there regarding charts that will be populated with the data pulled
based on the
dataset in use.
So I'll see you in the next video as we take a look at charts.