I'm frustrated that the Intune and Defender dashboards mostly show a current or slightly stale snapshot instead of useful historical trends. I wanted to track things such as Windows patch levels, quality update status, device health, and whether Defender Antivirus was active rather than EDR block mode while removing an expired antivirus product.
My solution was to query raw Intune data through Microsoft Graph using PowerShell in a scheduled Azure Automation runbook. The runbook authenticates with an automated identity and writes a historical record for each visible device to a Microsoft List in SharePoint. Power BI then reads that list to provide trend dashboards and reports.
The screenshots are anonymized mock-ups, but I'd appreciate a sanity check. Has anyone built something similar, and would you recommend a different storage or monitoring approach as the dataset grows?
5 Answers
This is a sensible approach, especially when the built-in dashboards don’t provide the historical view you need. Are you using the Microsoft Graph SDK or making direct API calls?
How long do you need to retain the trend data? The overall design is fine, but a SharePoint/Microsoft List becomes awkward once it grows beyond roughly 5,000 items. Polling twice per day for every device can consume that space surprisingly quickly.
For longer retention, I’d consider moving the historical records to a more suitable store. If you stay with the list, a rolling FIFO cleanup that removes the oldest records after reaching your limit could work, although it reduces the amount of history available.
Depending on the reporting requirements, Power BI may be able to call the Graph endpoints directly and retain the results in its model instead of using a separate list and runbook. That could simplify the architecture, though it’s worth checking refresh limits, authentication, API throttling, and whether the model provides the retention and incremental-history behavior you need.
Do you mean the Power BI model itself would act as the historical data store? I’d need to understand how it handles retaining each refresh rather than only replacing the current snapshot.
A monitoring platform such as Zabbix could handle the operational side well. You could query Defender data through the Graph Security API, aggregate endpoint statistics with Advanced Hunting, and return a compact JSON result. Dependent items could then produce graphs and triggers for endpoint counts, stale devices, unhealthy sensors, high-risk machines, incidents, and alert severity.
That wouldn’t replace Power BI for detailed analysis or polished reporting, but it could put Defender alongside server, network, firewall, and application monitoring with live alerts and historical trends.
There are also commercial options with prebuilt Intune and Defender datasets and Power BI reports. They may be worth comparing against the maintenance cost of keeping the Graph queries, automation, data model, and dashboards working yourself.
Just direct API calls from PowerShell in the Azure runbook. I’m a sysadmin rather than a developer, so I went with the simplest thing that solved the problem.