Redaguoti

Bendrinti naudojant


Create custom reports using Power BI

Applies to:

Want to experience Microsoft Defender for Endpoint? Sign up for a free trial.

Note

If you are a US Government customer, please use the URIs listed in Microsoft Defender for Endpoint for US Government customers.

Tip

For better performance, you can use server closer to your geo location:

  • us.api.security.microsoft.com
  • eu.api.security.microsoft.com
  • uk.api.security.microsoft.com
  • au.api.security.microsoft.com
  • swa.api.security.microsoft.com
  • ina.api.security.microsoft.com

In this section, you learn to create a Power BI report on top of Defender for Endpoint APIs.

The first example demonstrates how to connect Power BI to Advanced Hunting API, and the second example demonstrates a connection to our OData APIs, such as Machine Actions or Alerts.

Connect Power BI to Advanced Hunting API

  1. Open Microsoft Power BI.

  2. Select Get Data > Blank Query.

    The Blank Query option under the Get Data menu item

  3. Select Advanced Editor.

    The Advanced Editor menu item

  4. Copy the code snippet below and paste it in the editor:

        let
            AdvancedHuntingQuery = "DeviceEvents | where ActionType contains 'Anti' | limit 20",
    
            HuntingUrl = "https://api.securitycenter.microsoft.com/api/advancedqueries",
    
            Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])),
    
            TypeMap = #table(
                { "Type", "PowerBiType" },
                {
                    { "Double",   Double.Type },
                    { "Int64",    Int64.Type },
                    { "Int32",    Int32.Type },
                    { "Int16",    Int16.Type },
                    { "UInt64",   Number.Type },
                    { "UInt32",   Number.Type },
                    { "UInt16",   Number.Type },
                    { "Byte",     Byte.Type },
                    { "Single",   Single.Type },
                    { "Decimal",  Decimal.Type },
                    { "TimeSpan", Duration.Type },
                    { "DateTime", DateTimeZone.Type },
                    { "String",   Text.Type },
                    { "Boolean",  Logical.Type },
                    { "SByte",    Logical.Type },
                    { "Guid",     Text.Type }
                }),
    
            Schema = Table.FromRecords(Response[Schema]),
            TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}),
            Results = Response[Results],
            Rows = Table.FromRecords(Results, Schema[Name]),
            Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}}))
    
        in Table
    
  5. Select Done.

  6. Select Edit Credentials.

    The Edit Credentials menu item

  7. Select Organizational account > Sign in.

    The Sign in option in the Organizational account menu item

  8. Enter your credentials and wait to be signed in.

  9. Select Connect.

    The sign-in confirmation message in the Organizational account menu item

Now the results of your query appear as a table and you can start to build visualizations on top of it! You can duplicate this table, rename it, and edit the Advanced Hunting query inside to get any data you would like.

Connect Power BI to OData APIs

The only difference from the previous example and this example is the query inside the editor.

  1. Open Microsoft Power BI.

  2. Select Get Data > Blank Query.

    The Blank Query option under the Get Data menu item

  3. Select Advanced Editor.

    The Advanced Editor menu item

  4. Copy the following code, and paste it in the editor to pull all Machine Actions from your organization:

        let
    
            Query = "MachineActions",
    
            Source = OData.Feed("https://api.securitycenter.microsoft.com/api/" & Query, null, [Implementation="2.0", MoreColumns=true])
        in
            Source
    

    You can do the same for Alerts and Machines. You also can use OData queries for queries filters. See Using OData Queries.

Power BI dashboard samples in GitHub

See the Power BI report templates.

Sample reports

View the Microsoft Defender for Endpoint Power BI report samples.

Tip

Do you want to learn more? Engage with the Microsoft Security community in our Tech Community: Microsoft Defender for Endpoint Tech Community.