Stu Clark: Hi. I'm Stu Clark, a Program Manager on the
Microsoft 365 Security team. And I want to give you a quick
tour of using the API that Microsoft Secure Score offers. If
you watched the video where I gave you a tour of the
Microsoft Secure Score API, you might remember that we
created a JSON using that API. Let's look at how we can now
read that data in Power BI and some of the reports that we
can create.
So, the first thing we want to do is obviously open up Power
BI. So here we are. Power BI gives you numerous options for
getting data. So if you've taken the option to not take the
data into JSON, but you've put it into a local SQL
implementation, you can use that. But for these purposes, we
are going to be using the JSON file.
So, we just go to Get Data. We click on More. When the dialog
comes up, we'll see an option in here for JSON. All we do now
is press Connect, and we select the file that we exported
when we did our API in the previous video—as you can see
here, secure score data.json. We then open this file.
Now, one of the things we need to do is actually we need to
do some data transformation, because obviously the data has
come back in JSON format. So, we need to do some tweaking to
let Power BI know how we want certain columns to be
structured in order to apply the report views that we wish to
do. So, we're just waiting for the data model to be created.
And we're then going to go and edit the query itself.
So, in the query itself we can now see we have the root of
our JSON file in here. The first thing we need to do is
convert this into a table. It's very simple. We just select
that first list, and we convert to table. Power BI will now
extract all of the columns within the JSON file so that we
can start to do them. We need to keep expanding the columns
until we've gone through the data within the JSON to the
appropriate level. So, we just keep clicking this separator
icon here until we get to the desired level.
Power BI will tell us when we're there. So, as you can see,
the data from Secure Score is now starting to appear as we
walk through the JSON structure. And so now we're getting
closer and closer. So, we can start to see things like the
licensed user count, our active user count, and what the
actual Secure Score is. Now, because of the way the JSON
holds its data, we do still need to expand some of these out.
So, the first thing I want to do is expand out the date. So,
again, within this particular column, we have year, month and
day, which we obviously need. So, we expand those out. Now,
what we want to do on these columns is we want to merge that
into a single date column. So, we just select Merge Columns.
We select a separator. We are just going to use comma for
this example. And we click OK. And that now takes that into a
single column that we can use.
Important tip—Power BI is very sensitive about the column
type. So, we need to make sure that we specify that this is
going to be a date column. As you can now see, it has moved
across into that correct format. It's also worth renaming
your columns into something that's relevant so you can
understand exactly what data you're working with. So, we now
have our tenant ID. And we have the date data plus the
relevant data for a particular day.
One other final thing we can do, as I mentioned in the API,
we get a lot more data come back through the API than we can
through the score analyzer. And this is held in this control
scores list here. So, if we look within this list itself, we
can actually start to expand those out and start to see the
actual detail within. So, again, we go through the same
exercise. We split this column out. We expand it to a new
row.
Now, we have expanded out the controls. We have one more step
that we need to take. And again, this is a crucial one to
take, as I mentioned. Power BI needs to know the column type.
So, by default, as you can see, it's classing the scores as a
string. So, what we need to do is again tell Power BI that
these are actually numeric strings that we want.
So, for every numeric representation, we just want to make
sure that we convert that column type into an actual numeric.
So, again, we just select the ones that we're interested in.
Once we have them all, we go to the transform column, data
type, and we just change to a decimal so it's classed as a
numeric number. And this becomes important if we want to kind
of do any arithmetic on the controls when we're further into
the process, such as we want to know the average or the
median of the figures that we're going through.
So, now we have done this. We literally click on Close and
Apply. And we're now taken back to the Power BI dashboard.
Now, this is where we start to model our data to the graphics
that we're interested in viewing. So, how do we start doing
that? So, the first thing we have to do is kind of pick our
visualization. A really nice, easy way to show this is to
kind of replicate what Secure Score gives you on the analyzer
tab.
So, we can select a line chart. We can expand that canvas
out. And we then basically start to drag and drop the data
that we're interested in showing on this particular
visualization. So, for this one, for my axis, I'm going to
have the date. The date lets you select several hierarchies.
You can look at year. You can look at quarter. You can look
at month. Typically, though, you're best just picking the
date range itself to give you the view of the number of days
that you took in through the API.
We can then start to add in the various components. So, let's
take a look here. So, we can add in our Secure Score. Now, as
we can see, we start to get the view that the dashboard
analyzer gives you. So, we want to make sure again that it's
not doing any additions. So, we just want to take the minimum
score. And here we can see this is this particular tenant's
Secure Score trend of that 30 days' worth of data that we
brought back in.
Now, I may be curious to know what's causing these rises and
troughs in the environment. So, we can bring back some of the
categories. So, we can bring back account score. So, account
score is basically all of the controls that fall underneath
that filter of account, as we demonstrated in an earlier
video. So we can then start to see, how did my scores and
controls that fell under the account bracket start to
influence my overall Secure Score?
So, as we can see here, there was a rise in the number of
points that we gave for controls in the account category. And
we can see the corresponding rise in Secure Score, the
average score over time. We then start to bring in, okay,
what else are we interested in here? What else could have
influenced this particular score?
So, we can take data. Let's add data into here and see how
that influences. So, again, we can now see that data again.
There was an uptick in data that starts to influence your
overall Secure Score. Now, the granularity of this you can
really kind of take to the next level. You can start to build
out very specific visualizations of what actual controls
within a category are influencing your score in the most
aggressive way.
I've created a pre-sample of this using the exact same data
set which I'm just going to show to you now to kind of give
you an idea of how those visualizations look. So, this is the
exact same graph we've just created. We've just made the
lines a bit bolder so it's easier for you to see. The beauty
we have in Power BI is that hovering over any data point, we
show you what particular controls were happening on a day.
Where the power of this really starts to come in is when we
start to add in all of the individual controls. So, it's a
bit hard to see on this screen here. But basically, this is
every single control that Secure Score is offering. And what
is the weighting that this particular control is giving to
your overall Secure Score?
So, if we look at this itself, by default I have all of my
Secure Score controls in here. Now, I can start to filter
these out. So, as you can see here in this list, I have
things like admin MFA. So, if I'm only interested in
selecting a few controls that I'm interested in to see how
that was influencing, I can pick admin MFA. So, here I can see
just the trend of the admin MFA control and how its points
raised and fell over a period of time.
I could add in the block transport rules. And it would now
start to show me what was the movement of that particular
control over this specific period of time. I can go through
and add as many as I want, allowing me to see how individual
controls can influence your overall score. This is great for
taking back to senior leadership teams to show them how their
score is being influenced within certain areas of your
business.
It's also great to see why a certain control's moving. If
you've had a certain level of MFA points for a particular
time, and you notice through the visualization there's been a
significant drop, it's something that you really want to go
and investigate further.
So, we've really just scratched the surface of what you can
do within Power BI. There are many visualizations from
doughnuts to flow charts to funnels that can give you some
fantastic views of how you can manipulate the data and
improve your overall security posture. So, that's just a few
examples of how you can use the data from the API to do your
advanced reporting and increase your security position.
Check out our other videos that give you a quick tour of the
Microsoft Secure Score dashboard, how you can see your score
progressing over time, and how to use the Secure Score API.
Thanks for watching.