In this article:
A while back, Microsoft introduced the Application credential activity report, and exposed additional Graph API endpoints to fetch data out of the existing application/service principals reports.
Those were all welcome additions, as they allowed us to supplement the service principal and application permissions reports. In fact, incorporating data from said reports was one of the major reasons for updating the corresponding PowerShell scripts, and helped expose sign-in details with minimum overhead.
That said, at some point the Application credentials activity report stopped working, and it currently returns empty output.
Luckily, we have all the data we need to build such report on our own, thanks to the availability of service principal sign-in logs!
So in this article, we will introduce an alternative to the built-in Application credentials activity (or usage) report, which will give you the same amount of details. And as with every other PowerShell-based solution, it allows you to easily include additional data as needed. Let's get started!
The process we will use for generating such report is fairly simple. First, we get the set of application objects within the tenant. We then get the set of password and/or key credentials configured for the application, and make a query against the service principal sign-in logs, filtered by both the application ID and the credential ID. This gives us all the "success" login events, and as those events are returned in descending order based on their timestamp, we can optimize the data gathering part of the script by only requesting the most recent entry. After that, it's all a matter of organizing the output and exporting it.
As with all our Graph-based script samples, we will need to discuss the required permissions first.
In order to get the set of application objects within the organization and their credentials, you will need the Application.Read.All permission.
To fetch the service principal sign-in logs, the AuditLog.Read.All permission is required.
Both application and delegate permissions will do.
As has become customary for our script samples lately, we will provide two flavors of the report - one based on the Graph SDK for PowerShell and leveraging Delegate permissions, and one based on"raw" Graph API requests and leveraging application permissions.
Once authentication is handled, the script will fetch all of the organization's application registrations. In order to determine where a given credential was last used, we can rely on the information exposed within the service principal sign-in logs data, which for the time being is still only available under the /beta/auditLogs endpoint. There are few ways we can go about this, the "lazy" one being to fetch the full set of events (remember that Entra keeps sign-in logs for maximum of 30 days), then filter the data client-side based on the appId value exposed therein.
However, as the sign-in logs include events from both third-party integrated applications and our own (well, and the first-party Microsoft ones), this approach will result in a large amount of unneeded data,and might hinder the performance of the solution in large organizations.
Instead, we can use the filtering capabilities of the Graph API to narrow down the set of events returned.
Again we have few options to consider - filter by only appId and fetch all the sign-in events for the corresponding service principal from the past 30 days or get just the latest successful login.
While there is no built-in filter for the latter, we can use a workaround - filter based on the Id of the credentials (servicePrincipalCredentialKeyId). This will only return successful logins, if any (events corresponding to failed logins do not populate the key Id, as one might expect). And as data from the /auditLogs endpoint is returned in chronological order, we can simply combine our $filter query with $top=1 to get the latest successful login:
Now, in case you want a comprehensive report, listing app application registrations including those with no credentials, the method above is not appropriate, as it will automatically exclude any such entries. Then again, the task at hand is to prepare a report of application credential activity, so every app that does not show in the report will directly fall into the "not used" category, as will any credentials associated with said app.
There is one scenario that we might want to address though -the case when a credential has been used in the past 30 days, but was removed from the app before the report was generated. This limitation can result in missing data for infrequently used applications, leading to potentially incorrect assumptions about application usage.
If this is something you feel needs to be included, feel free to update the script to leverage the appId-only filter method mentioned above.
There is another factor to consider. Namely, the fact that the Entra sign-in log events are retained for 30 days only, which in turn is the reason why you might see entries with no usage in the generated report.
While this 30-day window might be more than sufficient for some organizations to declare a given app "unused", there are many scenarios where business processed, automation scripts or workflows are executed on a cadence that will clash with the 30 days window.
The point being, it is recommended to monitor application credentials usage continuously, either by ingesting the sign-in logs data to a SIEM solution and running a similar query therein, or simply running this script periodically, say on a weekly basis, and collating the input.
Returning back to the script's logic, once we fetch the set of events matching the current appId and credential combo, it is time to prepare the script's output.
Here is where a custom-built solution can truly shine, as you can add any of the application/service principal properties that you are interested in to the generated report. For example, you can include the set of Owners assigned to the app, or the set of permissions it requires, etc.
The default set of properties is selected so that it mimics Microsoft's built in Application credential activity report, but please feel free make any changes you see fit here. Here’s Microsoft’s built-in Entra Application Credential Activity Report:
To run the script, first download your copy from the GitHub repo for either the GraphSDK or the Graph API version.
If using the latter, make sure to fill in the authentication variables first (lines 18-21)! And of course make sure you have granted the necessary permissions (Application.Read.All and AuditLog.Read.All).
The script does not accept any parameters, just the"common" -Verbose/-OutVariable ones that you can use for troubleshooting purposes.
Here's how to run the script (for the Graph API version,replace the corresponding file name):
And here’s what the generated report will look like:
If you compare the output with the screenshot from Microsoft's official documentation or the actual report within your Entra portal (once it starts working), you will find the exact level of detail represented.
In effect, we have generated a report representing the credential usage for all our Entra application registrations, which we can now execute on demand, email or otherwise leverage in troubleshooting and cleanup tasks!
