Create custom Microsoft Defender XDR reports using Microsoft Graph security API and Power BI

Applies to: Microsoft Defender XDR

Empowering security professionals to visualize their data enables them to quickly recognize complex patterns, anomalies, and trends that might otherwise be lurking underneath the noise. With visualizations, SOC teams can rapidly identify threats, make informed decisions, and communicate insights effectively across the organization.

There are multiple ways to visualize Microsoft Defender security data:

Navigating built-in reports in the Microsoft Defender portal.
Using Microsoft Sentinel workbooks with prebuilt templates for every Defender product (requires integration with Microsoft Sentinel).
Applying the render function in Advanced Hunting.
Using Power BI to expand existing reporting capabilities.

In this article, we create a sample Security Operations Center (SOC) efficiency dashboard in Power BI using Microsoft Graph security API. We access the Microsoft Graph security API in user context, therefore the user must have corresponding permissions to be able to view alerts and incidents data.

Note

Example below is based on our new MS Graph security API. Find out more at: Use the Microsoft Graph security API.

Importing data into Power BI

The following steps show how to get Microsoft Defender alerts data into Power BI.

Open Microsoft Power BI Desktop.
Select Get Data > Blank Query.
Select Advanced Editor.

Paste in Query:

let
    Source = OData.Feed("https://graph.microsoft.com/v1.0/security/alerts_v2", null, [Implementation="2.0"])
in
    Source

Select Done.
When you're prompted for credentials, select Edit Credentials:
Select Organizational account > Sign in.
Enter credentials for account with access to Microsoft Defender XDR incidents data.
Select Connect.

Now the results of your query appear as a table, and you can start building visualizations on top of it.

Tip

If you are looking to visualize other forms of Microsoft Graph security data like Incidents, Advanced Hunting, Secure Score, etc., see Microsoft Graph security API Overview.

Filter Microsoft Defender XDR report data in Power BI

Microsoft Graph API supports the OData (Open Data Protocol) query protocol for filtering and pagination, so users don't have to worry about pagination - or requesting the next page of results. However, filtering data is essential to improving load times in a busy environment.

Microsoft Graph API supports query parameters. Here are few examples of filters used in the report:

The following query returns the list of alerts generated over the past three days. Using this query in environments with high volumes of data might result in hundreds of megabytes of data that could take a moment to load. By using this hardcoded approach, you're able to quickly see your most recent alerts over the last three days as soon as you open the report.
```
let
    AlertDays = "3",
    TIME = "" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -AlertDays), "yyyy-MM-dd") & "",
    Source = OData.Feed("https://graph.microsoft.com/v1.0/security/alerts_v2?$filter=createdDateTime ge " & TIME & "", null, [Implementation="2.0"])
in
    Source
```

Instead of collecting data across a date range, we can gather alerts across more precise dates by inputting a date using the YYYY-MM-DD format.

let
    StartDate = "YYYY-MM-DD",
    EndDate = "YYYY-MM-DD",
    Source = OData.Feed("https://graph.microsoft.com/v1.0/security/ alerts_v2?$filter=createdDateTime ge " & StartDate & " and createdDateTime lt " & EndDate & "", null, [Implementation="2.0"])
in
    Source

When historical data is required (for example, comparing the number of incidents per month), filtering by date isn't an option (since we want to go as far back as possible). In this case, pull only selected fields such as id, title, severity, and createdDateTime:

let
    Source = OData.Feed("https://graph.microsoft.com/v1.0/security/alerts_v2?$filter=createdDateTime ge " & StartLookbackDate & " and createdDateTime lt " & EndLookbackDate &
"&$select=id,title,severity,createdDateTime", null, [Implementation="2.0"])
in
    Source

Create report parameters in Power BI

Instead of constantly querying the code to adjust the timeframe, use parameters to set a Start and End Date each time you open the report.

Go to Query Editor.
Select Manage Parameters > New Parameter.
Set desired parameters.

In the following example, we use two different time frames, Start and End dates.

Remove hardcoded values from the queries and make sure that StartDate and EndDate variable names correspond to parameter names:

let
    Source = OData.Feed("https://graph.microsoft.com/v1.0/security/incidents?$filter=createdDateTime ge " & StartDate & " and createdDateTime lt " & EndDate & "", null, [Implementation="2.0"])
in
    Source

Review the custom Microsoft Defender XDR report

Once the data has been queried and the parameters are set, now we can review the report. During the first launch of the Power BI template (.pbit) file, you're prompted to provide the StartDate and EndDate parameters:

The dashboard offers three tabs intended to provide SOC insights. The first tab provides a summary of all recent alerts (depending on the selected timeframe). This tab helps analysts clearly understand the security state over their environment using alert details broken down by detection source, severity, total number of alerts and mean-time-to-resolution.

The second tab offers more insight into the attack data collected across the incidents and alerts. This view can provide analysts with greater perspective into the types of attacks executed and how they map to the MITRE ATT&CK framework, a knowledge base that categorizes adversary tactics and techniques.