Query Details
Identity Summarize App Usage Monthon Month
Query
//Summarize the usage of all your applications from last month to this month. Usage is calculated by distinct users per application.
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago(60d)
| where ResultType == "0"
| summarize
['Last Month Signins']=dcountif(UserPrincipalName, TimeGenerated > ago(60d) and TimeGenerated < ago(30d)),
['This Month Signins']=dcountif(UserPrincipalName, TimeGenerated > ago(30d))
by AppId, AppDisplayName
| extend ['Percentage Change']=(todouble(['This Month Signins'] - todouble(['Last Month Signins'])) / todouble(['Last Month Signins']) * 100)
| sort by AppDisplayName asc Explanation
This query summarizes the usage of all applications from last month to this month. It calculates the usage based on the number of distinct users per application. The query requires the Azure Active Directory - Signin Logs data connector. It filters the logs to include only those generated in the last 60 days and with a result type of "0" (successful sign-ins).
The query then groups the sign-ins by application ID and display name. It calculates the number of sign-ins in the last month and this month using the dcountif function. It also calculates the percentage change in sign-ins between the two periods. Finally, the results are sorted by the application display name in ascending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators