EntraID Workbook

signin-analysis.kql

let data = SigninLogs
    | where AppDisplayName in ('*') or '*' in ('*')
    | where UserDisplayName in ('*') or '*' in ('*')
    | extend errorCode = Status.errorCode
    | extend SigninStatus = case(errorCode == 0, "Success", errorCode == 50058, "Pending action (Interrupts)", errorCode == 50140, "Pending action (Interrupts)", errorCode == 51006, "Pending action (Interrupts)", errorCode == 50059, "Pending action (Interrupts)", errorCode == 65001, "Pending action (Interrupts)", errorCode == 52004, "Pending action (Interrupts)", errorCode == 50055, "Pending action (Interrupts)", errorCode == 50144, "Pending action (Interrupts)", errorCode == 50072, "Pending action (Interrupts)", errorCode == 50074, "Pending action (Interrupts)", errorCode == 16000, "Pending action (Interrupts)", errorCode == 16001, "Pending action (Interrupts)", errorCode == 16003, "Pending action (Interrupts)", errorCode == 50127, "Pending action (Interrupts)", errorCode == 50125, "Pending action (Interrupts)", errorCode == 50129, "Pending action (Interrupts)", errorCode == 50143, "Pending action (Interrupts)", errorCode == 81010, "Pending action (Interrupts)", errorCode == 81014, "Pending action (Interrupts)", errorCode == 81012, "Pending action (Interrupts)", "Failure")
    | where SigninStatus == '*' or '*' == '*' or '*' == 'All Sign-ins';
let appData = data
    | summarize
        TotalCount = count(),
        SuccessCount = countif(SigninStatus == "Success"),
        FailureCount = countif(SigninStatus == "Failure"),
        InterruptCount = countif(SigninStatus == "Pending user action")
        by Os = tostring(DeviceDetail.operatingSystem)
    | where Os != ''
    | join kind=inner (data
        | make-series Trend = count() default = 0 on TimeGenerated in range(ago(30d), now(), 1d) by Os = tostring(DeviceDetail.operatingSystem)
        | project-away TimeGenerated)
        on Os
    | order by TotalCount desc, Os asc
    | project Os, TotalCount, SuccessCount, FailureCount, InterruptCount, Trend
    | serialize Id = row_number();
data
| summarize
    TotalCount = count(),
    SuccessCount = countif(SigninStatus == "Success"),
    FailureCount = countif(SigninStatus == "Failure"),
    InterruptCount = countif(SigninStatus == "Pending user action")
    by
    Os = tostring(DeviceDetail.operatingSystem),
    Browser = tostring(DeviceDetail.browser)
| join kind=inner (data
    | make-series Trend = count() default = 0
        on TimeGenerated
        in range(ago(30d), now(), 1d)
        by
        Os = tostring(DeviceDetail.operatingSystem),
        Browser = tostring(DeviceDetail.browser)
    | project-away TimeGenerated)
    on Os, Browser
| order by TotalCount desc, Os asc
| project Os, Browser, TotalCount, SuccessCount, FailureCount, InterruptCount, Trend
| serialize Id = row_number(1000000)
| join kind=inner (appData) on Os
| project
    Id,
    Name = Browser,
    Type = 'Browser',
    ['Sign-in Count'] = TotalCount,
    Trend,
    ['Failure Count'] = FailureCount,
    ['Interrupt Count'] = InterruptCount,
    ['Success Rate'] = 1.0 * SuccessCount / TotalCount,
    ParentId = Id1
| union (appData 
    | project
        Id,
        Name = Os,
        Type = 'Operating System',
        ['Sign-in Count'] = TotalCount,
        Trend,
        ['Failure Count'] = FailureCount,
        ['Interrupt Count'] = InterruptCount,
        ['Success Rate'] = 1.0 * SuccessCount / TotalCount,
        ParentId = -1)
| order by ['Sign-in Count'] desc, Name asc