Intune and Defender mostly provide current-state views, which makes it difficult to answer questions about fleet trends, such as Windows patch levels, quality update status, stale devices, or whether Defender Antivirus is active instead of EDR block mode. I wanted historical data rather than another snapshot, so I created a scheduled Azure Automation PowerShell runbook that queries device data through Microsoft Graph using an Azure automation identity. The runbook writes each machine's status to a Microsoft List, and Power BI reads that list to produce trend dashboards. The screenshots are anonymized examples. Does this approach seem reasonable, and are there any major limitations or better storage options I should consider?
4 Answers
A monitoring platform such as Zabbix could work well if the main goal is live operational visibility and alerting. You could collect Defender and Intune statistics through Graph or Advanced Hunting, then create triggers for stale devices, unhealthy sensors, risky endpoints, incidents, and alert severity. Power BI would still be the better choice for detailed analysis and polished reporting.
This is a sensible practical solution. You can use ordinary Graph API calls from PowerShell; you do not need the Graph SDK for this. The important parts are using least-privilege permissions, handling pagination and throttling, and recording a timestamp with each device snapshot so the Power BI trends are meaningful.
The main concern is the Microsoft List. Around 5,000 items, filtering and querying can become awkward even though the list can technically hold more. At two snapshots per day, your retention calculation gives you roughly a year for the current fleet, but growth in device count or extra fields will reduce that quickly. A proper database, Azure Table Storage, Log Analytics, or another scalable historical store would be safer long term.
You could also query Graph directly from Power BI and keep the results in the model as a non-refreshing source, which removes the list and runbook. That is simpler for a one-time snapshot, but it is not a great replacement if you need scheduled collection, reliable historical retention, or an audit trail. A separate data store gives you much more control over refreshes and retention.
So in that setup, the Power BI model effectively becomes the historical store? I can see that working for a smaller or short-lived dataset, but I am wary of relying on it for long-term collection.
That is my concern too. I may start with a FIFO cleanup process that removes the oldest records once the list reaches its practical limit, then move to more robust storage if the dataset grows.