Connecting MS Secure Scores to Power Query

[user349287]

The Microsoft 365 Defender Portal (https://security.microsoft.com/) has a 'Secure Score' page, which contains the following:

data blacked out for security

An overall secure score which is then broken down by Identity, Data, Device, and Application secure scores.

I would like to be able to pull these four scores into a Power BI report; however, I have had some difficulty in putting together a solution. This data seems like it could be found in the Microsoft Graph API, but MS seems to have made connecting Graph to Power Query rather difficult. I've tried other Defender APIs, but they all seem either outdated or out of scope for what I'm trying to pull. 

Can anyone advise? Thanks for reading. 

[Mattoas]

Hey, might be a little late to the party but i have been sitting with the same project and I've found a solution that works great. 

First you would need to create a app registration in azure with the api permissions SecurityEvents.Read.All.

Create a Client Secret for the app in Azure.

In Power BI Desktop create Parameters for

"TenantID" = The Tenant ID in Azure

"resourceGraphUri" = https://graph.microsoft.com/

"AppID" = Application (client) ID for the app in Azure

"AppSecret" = Client Secret Value for the app in Azure

"clientCredentials" = client_credentials

"secureScoresUri" = https://graph.microsoft.com/beta/security/secureScores?$top=1

"secureScoreControlProfileUri" = https://graph.microsoft.com/beta/security/secureScoreControlProfiles?$top=395

Create a blank query named AccessToken, open Advanced Editor. Paste following code:

let
    //POST Request
    Url = "https://login.microsoftonline.com/"&TenantId&"/oauth2/token",
    Body = "resource="&resourceGraphUri&"&client_id="&AppId&"&client_secret="&AppSecret&"&grant_type="&clientCredentials,
    Options = [
        Content = Text.ToBinary(Body)
    ],

    //Get token
    Response = Web.Contents(Url,Options),
    ParsedJson = Json.Document(Response),
    ConvertedToken = "Bearer "&ParsedJson[access_token]
in
    ConvertedToken

Save the query and create another blank query named what ever you'd like. Open Advanced Editor and paste following code:

let
    AuthHeader = #"AccessToken",
    Options = [
        Headers = [
            #"Authorization" = AuthHeader,
            #"Content-Type" = "Application/Json"
        ]
    ],
    WebRequestContent = Web.Contents(secureScoresUri,Options),
    JsonContent = Json.Document(WebRequestContent),
    ParsedResults = JsonContent[value],
    Converted = Table.FromList(ParsedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Converted,"Column1",
                    Record.FieldNames(Converted[Column1]{0}),
                    Record.FieldNames(Converted[Column1]{0})),
    #"Changed Type" = Table.TransformColumnTypes(Expanded,{{"activeUserCount", Int64.Type}, {"currentScore", type number}, {"maxScore", type number}})
in
    #"Changed Type"

Save the query. This will get you all secure score details for the tenant. 

If you'd like to get all the details for each controlScore like Category as you mentioned. Create another blank query and paste following code:

let
    AuthHeader = #"AccessToken",
    Options = [
        Headers = [
            #"Authorization" = AuthHeader,
            #"Content-Type" = "Application/Json"
        ]
    ],
    WebRequestContent = Web.Contents(secureScoresUri,Options),
    JsonContent = Json.Document(WebRequestContent),
    ParsedResults = JsonContent[value],
    Converted = Table.FromList(ParsedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Converted,"Column1",
                    Record.FieldNames(Converted[Column1]{0}),
                    Record.FieldNames(Converted[Column1]{0})),
    controlScores1 = Expanded[controlScores],
    controlScores2 = controlScores1{0},
    #"Converted to Table" = Table.FromList(controlScores2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"controlCategory", "controlName", "description", "score", "lastSynced", "implementationStatus", "on", "scoreInPercentage"}, {"controlCategory", "controlName", "description", "score", "lastSynced", "implementationStatus", "on", "scoreInPercentage"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"controlName", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"score", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DescriptionFormatted", each if [description] = null then null else   Html.Table([description] , {{"text",":root"}})),
    #"Expanded DescriptionFormatted" = Table.ExpandTableColumn(#"Added Custom", "DescriptionFormatted", {"text"}, {"DescriptionFormatted.text"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded DescriptionFormatted", "implementationStatusFormatted", each if [implementationStatus] = null then null else   Html.Table([implementationStatus] , {{"text",":root"}})),
    #"Expanded implementationStatusFormatted" = Table.ExpandTableColumn(#"Added Custom1", "implementationStatusFormatted", {"text"}, {"implementationStatusFormatted.text"})
in
    #"Expanded implementationStatusFormatted"

Hope this helped. 🙂

[acatamas]

How did you get past this?

[Quste]

Thanks, works like a charm!