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

[Wayne74]

Hi All,

I am writing a PowerBI dashboard to get training data from defender via MS graph.  I can get the data and display it etc, however I want to be able to marry the Campaign Master records to the User Training Results.  This will tell me when the campaign was launched.  I cant use the assigned date on the user training table as on occasion they are assigned the training after the campaign close and I want to put the results in the correct month (for context we push out a new training campaign each month).

So I am using the following API's

• Training Master Records
  • This gives me the training content masters (eg, when Microsoft created the training video) but no link to a campaign or user training as you may expect.
  • The DisplayNames are not unique either so I cant use this table for anything in this scenario
  • https://graph.microsoft.com/beta/security/attackSimulation/trainings
• Campaign Master Records
  • This gives me the campaigns, including the schedule information, but not the content that was used
  • There is a campaign users object but it errors when i try to expand it
  • There is no v1.0 of this API only beta
  • https://graph.microsoft.com/beta/security/attackSimulation/trainingCampaigns
• User Training Results
  • Now this is the transactional table I am using for the majority of my report
  • It gives me the user, the training content they did (display name only not an ID), the status and when they completed it
  • It does not give me any link back to the campaign that they completed this training as part of
  • https://graph.microsoft.com/beta/reports/security/microsoft.graph.getAttackSimulationTrainingUserCov...

If anyone has hit this before or has any idea how to link the campaign to the user training data, it would be greatly appreciated.

I found this in Defender so there is a link from the content to the campaign somewhere:

Thank you,

[Wayne74 · Accepted solution]

Still no resolution to this one.  I just dont think its possible.  I will mark this as the solution, since there is no solution, so it can be closed.

[v-mdharahman]

Hi @Wayne74,

Thanks for reaching out to the Microsoft fabric community forum.

As @johnbasha33 already responded to your query, please go through his response and check if it solves your issue. 

I would also take a moment to thank @johnbasha33, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Wayne74,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[Wayne74]

I havent yet been able to resolve the issue sorry.  I will have another look today, but from what I can see there is no obvious way to marry the two.  Essentially if I could link the training to the campaign via a unique ID that would be perfect.

@johnbasha33 you mentioned hidden API fields.  Is there a way to find out more about this please?  Maybe there is a hidden ID field somewhere?

[johnbasha33]

@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.

[Wayne74]

@johnbasha33 

Firstly thank you very much for the reply, it is appreciated.

I know they retro assign people to training, so not sure the dates will work, but I will have a play with it today and see if I can make any of your suggestions work in this scenario. 

One question.  You mention checking for Hidden fields in the API.  How do I do this sorry?