I'm looking for advice on creating accurate inactivity reports for user logins in both Active Directory and Azure Active Directory. My goal is to identify inactive accounts to free up Microsoft licenses and enhance security. The issue is that Azure AD logs only keep data for one month (at least for our tenant), and when I export results, I hit a limit of 10,000 rows in Excel, which prevents me from getting a full overview of all users. Even when I've tried filtering to only successful sign-ins, the data is still too large. Each user might have numerous logins in a single day, which compounds the problem. I've consulted with our infrastructure team several times, but I'm not sure they're providing the help I need. I'm a second-line team leader managing approximately 10,000 accounts in a large organization, and while Active Directory tracks on-premise sign-ins well, I need a better solution for mobile and web-based applications through Azure AD. Any insights or solutions would be greatly appreciated!
3 Answers
I totally get what you're dealing with! We faced the same issues with both AD and Azure AD and constantly hit those export limits, leading to disorganized data. A major pain point for us was managing license usage and tracking who was using which cloud apps. We ended up implementing a virtual data platform that could pull data from all our sources. It did a good job of cleaning things up and finding duplicates, which saved us a lot of manual work. We even set up no-code ETLs for generating inactivity reports, which really improved our reporting and security management. If you haven't looked into similar solutions yet, I highly recommend exploring that route!
You might want to consider using Microsoft Graph API. It can help pull data from both Active Directory and Azure AD, giving you a more comprehensive view without getting bogged down by row limits. It’s definitely worth checking out if you need better access to user data!
I know this might not be the most straightforward solution, but have you tried just relying on the last logon date for accounts? It could potentially reduce the volume of rows you're working with if that's feasible for the types of reports you're generating. Though, I understand you might still need those Azure AD specifics!
I appreciate the suggestion! I do use the last logon date for on-premise AD in my reports, but I need to factor in Azure AD as well for complete accuracy. That's the tough part!