Forum Discussion
How to Automatically Export Microsoft Defender Security Recommendations with Historical Tracking
Hi everyone,
I'm currently using Microsoft Defender for Endpoint, and I'm looking for a way to automate the export of security recommendations. Right now, the only available option is to manually export these recommendations as a CSV using the "Export" button in the portal. However, I’d like to:
- Automatically pull these recommendations regularly
- Store them in an Azure SQL database/Azure Storage
- Use Power BI to create dashboards and track trends over time (since Defender does not provide historical views)
Is there a way to fetch this data programmatically?
My Goal:
- Automatically query this API daily (via Azure Function or Azure Automation or any other way)
- Store each day's results in an Azure SQL table/Storage account with timestamps
- Build Power BI reports for:
Most frequent vulnerabilities
Exposure trends over time
Recommendation coverage and progress
Hi Tammy,
Have you checked out the https://github.com/microsoft/MicrosoftDefenderForEndpoint-PowerBI/blob/master/TVM/TVM%20report%20templates%20June%202021/vulnerabilities_report_V6_full_dataset.pbit .
We discovered these a few months ago, these helped us build a PowerBI page that has a daily scheduled sync, we've connected it to an exported/synced software list in an Excel workbook to help us keep track of sanctioned software.
Your idea of using Azure SQL Tables is great—it's scalable and should work smoothly with Power BI.Cheers and Good luck.
Notes: To get a full list of software we use this source https://api.securitycenter.microsoft.com/api/software .Just a heads-up: users with Security Reader roles may only see software with CPE identifiers. Elevated permissions help access the full dataset.
Cheers,
Keith
2 Replies
Hello TammyJha
You could possibly build what you want using Logic Apps and running a KQL query at Advanced Hunting and leveraging the ExposureGraphNodes table.
- Build your logic app
- Create a Recurrence as a first step and indicate when you want to trigger for results
- Create a service principal with AdvancedQuery.Read.All permissions
- Choose Advanced Hunting as a next step and add the following query:
ExposureGraphNodes | where NodeLabel == "mdcSecurityRecommendation" | extend RecomDescription = parse_json(NodeProperties)["rawData"]["description"] | extend RecomSeverity = parse_json(NodeProperties)["rawData"]["severity"] | project Recommendation=NodeName, Description=RecomDescription, Severity=RecomSeverityYou might need to run it in your environment first and choose exactly which results are of interest for you.
- Choose where/how you would like to save the results as per your requirements described
If I have answered your question, please mark your post as Solved
If you like my response, please consider giving it a like
Hi Tammy,
Have you checked out the https://github.com/microsoft/MicrosoftDefenderForEndpoint-PowerBI/blob/master/TVM/TVM%20report%20templates%20June%202021/vulnerabilities_report_V6_full_dataset.pbit .
We discovered these a few months ago, these helped us build a PowerBI page that has a daily scheduled sync, we've connected it to an exported/synced software list in an Excel workbook to help us keep track of sanctioned software.
Your idea of using Azure SQL Tables is great—it's scalable and should work smoothly with Power BI.Cheers and Good luck.
Notes: To get a full list of software we use this source https://api.securitycenter.microsoft.com/api/software .Just a heads-up: users with Security Reader roles may only see software with CPE identifiers. Elevated permissions help access the full dataset.
Cheers,
Keith
