Create custom reports using Power BI

Important

Welcome to Microsoft Defender for Endpoint, the new name for Microsoft Defender Advanced Threat Protection. Read more about this and other updates here. We'll be updating names in products and in the docs in the near future.

Applies to: Microsoft Defender Advanced Threat Protection (Microsoft Defender ATP)

In this section you will learn create a Power BI report on top of Microsoft Defender ATP 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

  • 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:

	let 
		AdvancedHuntingQuery = "DeviceEvents | where ActionType contains 'Anti'",

		HuntingUrl = "https://api.securitycenter.windows.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

  • Click Done

  • 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

  • Now the results of your query will appear as table and you can start 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 above example is the query inside the editor.

  • Copy the below and paste it in the editor to pull all Machine Actions from your organization:

	let

		Query = "MachineActions",

		Source = OData.Feed("https://api.securitycenter.windows.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

For more information see the Power BI report templates.

Sample reports

View the Microsoft Defender ATP Power BI report samples. For more information, see Browse code samples.