Create custom reports using Power BI (user authentication)

Applies to:

Important

Some information relates to prereleased product which may be substantially modified before it's commercially released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

Run advanced queries and show results in Microsoft Power BI. Please read about Advanced Hunting API before.

In this section we share Power BI query sample to run a query using user token.

If you want to use application token instead please refer to this tutorial.

Before you begin

You first need to create an app.

Run a query

  • Open Microsoft Power BI

  • Click Get Data > Blank Query

    Image of create blank query

  • Click Advanced Editor

    Image of open advanced editor

  • Copy the below and paste it in the editor, after you update the values of Query

    let 
    
        Query = "MachineInfo | where EventTime > ago(7d) | summarize EventCount=count(), LastSeen=max(EventTime) by MachineId",
    
        FormattedQuery= Uri.EscapeDataString(Query),
    
        AdvancedHuntingUrl = "https://api.securitycenter.windows.com/api/advancedqueries?key=" & FormattedQuery,
    
        Response = Json.Document(Web.Contents(AdvancedHuntingUrl)),
    
        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
    
  • Click Done

    Image of create advanced query

  • Click Edit Credentials

    Image of edit credentials

  • Select Organizational account > Sign in

    Image of set credentials

  • Enter your credentials and wait to be signed in

  • Click Connect

    Image of set credentials

  • View the results of your query

    Image of query results