Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
user349287
Frequent Visitor

Connecting MS Secure Scores to Power Query

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

 

data blacked out for securitydata 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. 

2 REPLIES 2
Mattoas
Regular Visitor

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. 🙂

Thanks, works like a charm! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors