Report AzureAD and MSOL with Azure LogAnalytics

Andres Bohren
Report AzureAD and MSOL with Azure LogAnalytics

Hi All,

It’s not much more than a Month, until Azure AD (AzureAD) and MSOnline (MSOL) PowerShell Modules will be depreciated.

Reference: Entra Changes

Did you know that you can list the Usage of these PowerShells with AzureAD Workbooks

Entra Portal > Montitoring & health > workbooks > Sign-ins

Select Timerange “Last 30 days” and Apps “Azure Active Directory PowerShell”

If you click on the LogAnalytics Icon you will jump to Azure LogAnalytics Workspace

You will see the Kusto Query Language (KQL)

let details = dynamic({"Name": "", "Type": "*"});
let data = SigninLogs
    | extend AppDisplayName = iff(AppDisplayName == '', 'Unknown', AppDisplayName)
    | where AppDisplayName in ('Azure Active Directory PowerShell') or '*' in ('Azure Active Directory PowerShell')
    | where UserDisplayName in ('*') or '*' in ('*')
    | extend Country = tostring(LocationDetails.countryOrRegion)
    | extend City = tostring(LocationDetails.city)
    | extend errorCode = Status.errorCode
    | extend SigninStatus = case(errorCode == 0, "Success", errorCode == 50058, "Pending user action", errorCode == 50140, "Pending user action", errorCode == 51006, "Pending user action", errorCode == 50059, "Pending user action", errorCode == 65001, "Pending user action", errorCode == 52004, "Pending user action", errorCode == 50055, "Pending user action", errorCode == 50144, "Pending user action", errorCode == 50072, "Pending user action", errorCode == 50074, "Pending user action", errorCode == 16000, "Pending user action", errorCode == 16001, "Pending user action", errorCode == 16003, "Pending user action", errorCode == 50127, "Pending user action", errorCode == 50125, "Pending user action", errorCode == 50129, "Pending user action", errorCode == 50143, "Pending user action", errorCode == 81010, "Pending user action", errorCode == 81014, "Pending user action", errorCode == 81012, "Pending user action", "Failure")
    | where SigninStatus == '*' or '*' == '*' or '*' == 'All Sign-ins'
    | where details.Type == '*'
        or (details.Type == 'Country' and Country == details.Name)
        or (details.Type == 'City' and City == details.Name);
data
| top 200 by TimeGenerated desc
| extend TimeFromNow = now() - TimeGenerated
| extend TimeAgo = strcat(case(TimeFromNow < 2m, strcat(toint(TimeFromNow / 1m), ' seconds'), TimeFromNow < 2h, strcat(toint(TimeFromNow / 1m), ' minutes'), TimeFromNow < 2d, strcat(toint(TimeFromNow / 1h), ' hours'), strcat(toint(TimeFromNow / 1d), ' days')), ' ago')
| project
    User = UserDisplayName,
    ['Sign-in Status'] = strcat(iff(SigninStatus == 'Success', '✔️', '❌'), ' ', SigninStatus),
    ['Sign-in Time'] = TimeAgo,
    App = AppDisplayName,
    ['Error code'] = errorCode,
    ['Result type'] = ResultType,
    ['Result signature'] = ResultSignature,
    ['Result description'] = ResultDescription,
    ['Conditional access policies'] = ConditionalAccessPolicies,
    ['Conditional access status'] = ConditionalAccessStatus,
    ['Operating system'] = DeviceDetail.operatingSystem,
    Browser = DeviceDetail.browser,
    ['Country or region'] = LocationDetails.countryOrRegion,
    ['State'] = LocationDetails.state,
    ['City'] = LocationDetails.city,
    ['Time generated'] = TimeGenerated,
    Status,
    ['User principal name'] = UserPrincipalName

I’ve logged in with MSOL and AzureAD PowerShell

Connect-MsolService
Get-Date
Connect-AzureAD
Get-Date

I’ve simplified the Query to see the Details of the last four Hours. It seems that the Sign-in with MSOL and AzureAD PowerShell Modules are logged both as “Azure Active Directory PowerShell”.

//Azure Active Directory PowerShell / MSOnline
// Last 4 Hours Detail
SigninLogs
| where TimeGenerated > ago(4h)
| where AppDisplayName in ('Azure Active Directory PowerShell') or '*' in ('Azure Active Directory PowerShell')
| where  ResultType == 0 //Sucessful Login
|project TimeGenerated, UserPrincipalName,AppDisplayName,ResultType

To summarize the SignIns by UserPrincipalName use the following Query

//Azure Active Directory PowerShell / MSOnline
// Last 30 Days Summary (Count by UPN)
SigninLogs
| where TimeGenerated > ago(30d)
| where AppDisplayName in ('Azure Active Directory PowerShell') or '*' in ('Azure Active Directory PowerShell')
| where  ResultType == 0 //Sucessful Login
| summarize count () by bin(TimeGenerated, 1d)
| render columnchart

To render the Results with a Chart use the following Query

//Azure Active Directory PowerShell / MSOnline
// Last 30 Days Summary (Count by UPN) with Columnchart
SigninLogs
| where TimeGenerated > ago(30d)
| where AppDisplayName in ('Azure Active Directory PowerShell') or '*' in ('Azure Active Directory PowerShell')
| where  ResultType == 0 //Sucessful Login
| summarize count () by bin(TimeGenerated, 1d)
| render columnchart

If you want, you can even Import Loganalytics Data into Power BI Report

Regards
Andres Bohren

Azure Logo