时间戳

首页 > 解决方案 > 尝试使用过去 30 天内未登录的用户创建 KQL

azure-data-explorer - 尝试使用过去 30 天内未登录的用户创建 KQL

问题描述

让我说我不知道​​如何使用 Kusto，话虽如此，我们开始吧！我去了我的 AAD 工作簿并获得了一个查询以获取登录信息。然后，我添加了一些信息以获取已登录我的服务的用户列表。我如何让它在过去 30 天内拉出未登录的用户？

let data = SigninLogs
    | union ADFSSignInLogs;
data
| where TokenIssuerType in ('AzureAD', 'ADFederationServices') or '*' in ('AzureAD', 'ADFederationServices')
| project TimeGenerated,UserPrincipalName, Status_dynamic, UserDisplayName, Identity, AppDisplayName, AppId, ResourceDisplayName
| extend errorCode = Status_dynamic.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'
| make-series SignIns = count() default = 0
    on TimeGenerated
    from ago(30d) to now() step 1d
    by SigninStatus,UserDisplayName,UserPrincipalName,AppDisplayName,ResourceDisplayName

如何让我的查询仅显示过去 30 天内未登录我的服务的用户？

标签： azure-data-explorerkql

解决方案

您需要使用arg_max获取每个用户名的最新登录时间戳，然后您可以过滤上次登录时间超过 30 天 ( 30d) 之前的那些。

...
| summarize arg_max(LoginTime, *) by Username
| where LoginTime < ago(30d)

显然，您需要调整列名以匹配您的架构（实际列名）。

推荐阅读