Defender Training Campaigns - Marrying the Training Campaign to the User Training

Wayne74 

It seems like you're dealing with multiple tables of data from Microsoft Defender via the Microsoft Graph API, and the main challenge is linking the Campaign Master Records with the User Training Results to get a complete view of when the training campaign was launched and completed. Since the data isn't directly linked in the API, you’ll need to find an indirect way to establish that connection.

Here’s how you can approach this:

1. Understand the Data Model:

• Training Master Records: Contains the training content information.

• Campaign Master Records: Contains campaign information, including scheduling but no direct link to training content.

• User Training Results: Contains user completion data, but doesn't explicitly link to the campaign that the training was part of.

The challenge is in linking the Campaign Master Records (which contain the campaign schedule) to the User Training Results (which contain user data and the training they completed).

2. Looking for Indirect Links Between Campaign and User Training:

You mentioned that in Defender, you found a possible link between training content and campaign. If there's an internal mapping or reference to campaigns in your system, you might be able to derive the link from there.

3. Possible Solutions:

a. Use the Campaign Master Data's Schedule Information:

• Campaign Master Records contain schedule information for when the campaign runs. Even though the API doesn't directly link user training to a campaign, you can use the schedule data from the Campaign Master Records to infer the month or time period the campaign ran.

• You can then match the user training results (which includes completion date) against the campaign schedule. If a user completed training during the campaign’s scheduled period, you can assume that training was part of that campaign.

b. Use Display Names as a Potential Link:

• You mentioned that the DisplayName is not unique, but you can still try to group by this field along with the completion date to correlate which training content was likely part of which campaign.

• If multiple campaigns use similar content, you could try to map content to campaign schedules based on patterns or time periods (e.g., content created at the same time as the campaign).

c. Check for Hidden Fields in the API:

• Sometimes, the API may return additional hidden or less documented fields. Double-check the trainingCampaigns API and getAttackSimulationTrainingUserCoverage to see if there are any hidden links or additional fields that could provide the connection between campaigns and training.

• If there’s an ID for each campaign or content (even if it’s not exposed directly), you might be able to find an indirect way to match the training content to the campaign.

d. Potential Workaround with Power Query:

• In Power BI, if you cannot find an exact API link between the Campaign and User Training Results, you might need to use a Power Query transformation:

  • Load the Campaign Master Records, Training Master Records, and User Training Results into Power BI.

  • Create custom relationships or mappings based on display names, schedule dates, or training content.

  • Using Power Query’s M language or DAX, you could create a time-based relationship between the Campaign’s launch date and user completion dates.

e. Look for Graph API Enhancements or Beta Updates:

• The API you’re using is currently in beta, which means it's still evolving. Keep an eye on any changes or new releases (especially in v1.0 when it becomes available) to see if they add additional metadata or relations between campaigns and training results.

• You can also explore Microsoft Defender API documentation for any hidden relationships or metadata.

4. Step-by-Step Approach for Power BI:

1. Pull Data: Import the relevant tables from the Graph API.

   • Training Master Records

   • Campaign Master Records

   • User Training Results

2. Create Key Relationships:

   • In Power Query, create a custom relationship between campaigns and users based on the completion date and campaign schedule.

   • Use DisplayName and other fields (like training content type, if possible) as a secondary key.

3. Add Calculated Columns/Measures:

   • Use DAX to calculate the campaign period and check if training results fall within that period. You can use time intelligence functions to match user training results against campaign schedules.

4. Validate the Mapping:

   Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Validate the logic by checking if the training content corresponds to the right campaign. If the mapping is correct, you should see your data coming together in a logical way.