当前位置： 文江博客话题详情

如何根据另一个字段的值汇总字段？

发布于 2025-02-11 11:49:42 字数 784 浏览 1 评论 0原文

我有一个Azure托管应用程序，其中一些用户一直抱怨登录困难。因此，我添加了一些在应用程序见解中显示的日志。数据示例如下所示：

“

我需要创建一个报告：显示的

  • 唯一用户数（dissidenifier字段），该报告成功地登录了无法登录的唯一用户。
  • 成功尝试之前（如果有的话）的失败登录尝试数 - 在KQL中是否可以？

我的尝试是：

customEvents
| order by timestamp asc
| summarize TotalUserCount=dcount(tostring(customDimensions["Identifier"])),
            SuccessCount=countif(name startswith "Success"),
            FailureCount=countif(name !startswith "Success")

但这是错误的，我需要countif（name ...）也可以通过标识符与众不同。

我是KQL的新手，因此感谢您的帮助。

谢谢。

原文

I have an Azure hosted application in which some of our users have been complaining of difficulty logging-in. So I added some logs which show up in Application Insights. A sample of the data is shown below:

Log results

I need to create a report that shows:

  • The number of unique users (the Identifier field) that successfully logged-in and the number of unique users that failed to login.
  • The number of failed login attempts that preceded a successful attempt (if any) - is this even possible in KQL?

One one my attempts was:

customEvents
| order by timestamp asc
| summarize TotalUserCount=dcount(tostring(customDimensions["Identifier"])),
            SuccessCount=countif(name startswith "Success"),
            FailureCount=countif(name !startswith "Success")

But this is wrong, I need countif(name...) to also be distinct by Identifier.

I'm new to KQL and so would appreciate some help.

Thanks.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

吻风 2025-02-18 11:49:42

我将从分析会话级别的数据开始。
It's very easy to take it from there and summarize it to the user level etc.

// Data sample generation. Not part of the solution
// Setup
let p_event_num                     = 30;
let p_identifiers_num               = 3;
let p_max_distance_between_events   = 2h;
let p_names                         = dynamic(["Unsuccessful login. Invalid cred", "Unsuccessful login. Account wa", "Successful login"]);
// Internal
let p_identifiers   =   toscalar(range i from 1 to p_identifiers_num step 1 | summarize make_list(new_guid()));
let p_names_num     =   array_length(p_names);
let customEvents    =   materialize
                        (
                            range i from 1 to p_event_num step 1 
                            |   extend  ['timestamp [UTC]'] = ago(24h*rand())
                            |   extend  Identifier          = tostring(p_identifiers[toint(rand(p_identifiers_num))])
                            |   extend  name                = p_names[toint(rand(p_names_num))]
                        );
// Solution starts here
customEvents
|   project-rename ts = ['timestamp [UTC]']
|   partition hint.strategy=native by Identifier
    (
            order by    ts asc
        |   extend      session_id          = row_cumsum(iff(ts - prev(ts) >= p_max_distance_between_events, 1, 0))
        |   summarize   session_start       = min(ts)
                       ,session_end         = max(ts)
                       ,session_duration    = 0s
                       ,session_events      = count()                        
                       ,session_successes   = countif(name  startswith "Successful")
                       ,session_failures    = countif(name !startswith "Successful")
                       ,arg_max(ts, name)
                        by Identifier, session_id
    )
|   project-away    ts
|   project-rename  session_last_name = name    
|   extend          session_duration = session_end - session_start
|   order by        Identifier asc, session_id asc   
|   as              user_sessions
Identifiersession_idsession_startsession_endsession_durationsession_eventssession_successessession_failuressession_last_name
3b169e06-52e5-45d8-b951-62d5e8ab385b02022-06-26T20:22:22.4006737Z2022-06- 26T20：22：22.4006737Z00：00：00101失败登录。 Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b12022-06-26T22:47:01.8487347Z2022-06-26T22:47:01.8487347Z00:00:00110Successful login
3b169e06-52e5-45d8-b951- 62D5E8AB385B22022-06-27T04：57：15.6405722Z2022-06-27T07：32：10.4409854Z02：34：34：34：54.800413241 13不成功登录。 Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b32022-06-27T10:44:19.8739205Z2022-06-27T12:46:14.2586725Z02:01:54.3847520303Unsuccessful login.帐户WA
3B169E06-52E5-45D8-B951-62D5E8AB385B42022-06-27T14：50：35.3882433Z2022-06-27T14：50：50：50：50：35.3882433z 00:00:00:00:00:00：00 0 0 10101Unsuccess- Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b52022-06-27T18:33:51.4464796Z2022-06-27T18:47:06.0628481Z00:13:14.6163685202Unsuccessful login. Invalid cred
63ce6481-818e-4f3b-913e-88a1b76ac42302022-06-26T19:27:05.1220534Z2022-06-26T20:24:53.5616443Z00:57:48.4395909202Unsuccessful login. WA
63CE6481-818E-4F3B-913E-88A1B76AC42312022-06-27T02：17：03.4123257Z2022-06-27T02：36：36：36：19：19：19：：50.1918116Z 00：1946.77777999881-348.348.32-244.48.48.32.248.244.248.248.248 。
13e- 88A1B76AC42322022-06-27T13：27：27.2550722Z：39.6361479Z01：05：12.3810757321成功
： 322022-06-27T1417:20： 34.3725797Z2022-06-27T17：20：34.3725797Z00：00：0010 11 0 1失败登录。帐户WA
6ED81AB3-447E-481D-8BB3-A5F4087234BB02022-06-26T22：38：39.3105749Z2022-06-26-26T22：38：38：39.3105749Z 00:00:00:00：00:00：00：00 0 0 10 1 01 1UNSUCCESS-UNCOSS-UNCOSFUL。 Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb12022-06-27T03:06:04.340965Z2022-06-27T04:49:37.3314224Z01:43:32.9904574330Successful login
6ed81ab3-447e-481d-8bb3- A5F4087234BB22022-06-27T07：11：47.260913Z2022-06-27T07：11：47.260913Z 00：00：00：00：001 00 1 01不成功登录。 Account WA
6ED81AB3-447E-481D-8BB3-A5F4087234BB32022-06-27T11：39：02.356791Z：49：23.58181891Z：21.2225509881422222.22255098142222205：10：10：10： 102022-06-27T16：49：49： 49. Invalid cred

Fiddle

I would start from analyzing the data in the session level.
It's very easy to take it from there and summarize it to the user level etc.

// Data sample generation. Not part of the solution
// Setup
let p_event_num                     = 30;
let p_identifiers_num               = 3;
let p_max_distance_between_events   = 2h;
let p_names                         = dynamic(["Unsuccessful login. Invalid cred", "Unsuccessful login. Account wa", "Successful login"]);
// Internal
let p_identifiers   =   toscalar(range i from 1 to p_identifiers_num step 1 | summarize make_list(new_guid()));
let p_names_num     =   array_length(p_names);
let customEvents    =   materialize
                        (
                            range i from 1 to p_event_num step 1 
                            |   extend  ['timestamp [UTC]'] = ago(24h*rand())
                            |   extend  Identifier          = tostring(p_identifiers[toint(rand(p_identifiers_num))])
                            |   extend  name                = p_names[toint(rand(p_names_num))]
                        );
// Solution starts here
customEvents
|   project-rename ts = ['timestamp [UTC]']
|   partition hint.strategy=native by Identifier
    (
            order by    ts asc
        |   extend      session_id          = row_cumsum(iff(ts - prev(ts) >= p_max_distance_between_events, 1, 0))
        |   summarize   session_start       = min(ts)
                       ,session_end         = max(ts)
                       ,session_duration    = 0s
                       ,session_events      = count()                        
                       ,session_successes   = countif(name  startswith "Successful")
                       ,session_failures    = countif(name !startswith "Successful")
                       ,arg_max(ts, name)
                        by Identifier, session_id
    )
|   project-away    ts
|   project-rename  session_last_name = name    
|   extend          session_duration = session_end - session_start
|   order by        Identifier asc, session_id asc   
|   as              user_sessions
Identifiersession_idsession_startsession_endsession_durationsession_eventssession_successessession_failuressession_last_name
3b169e06-52e5-45d8-b951-62d5e8ab385b02022-06-26T20:22:22.4006737Z2022-06-26T20:22:22.4006737Z00:00:00101Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b12022-06-26T22:47:01.8487347Z2022-06-26T22:47:01.8487347Z00:00:00110Successful login
3b169e06-52e5-45d8-b951-62d5e8ab385b22022-06-27T04:57:15.6405722Z2022-06-27T07:32:10.4409854Z02:34:54.8004132413Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b32022-06-27T10:44:19.8739205Z2022-06-27T12:46:14.2586725Z02:01:54.3847520303Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b42022-06-27T14:50:35.3882433Z2022-06-27T14:50:35.3882433Z00:00:00101Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b52022-06-27T18:33:51.4464796Z2022-06-27T18:47:06.0628481Z00:13:14.6163685202Unsuccessful login. Invalid cred
63ce6481-818e-4f3b-913e-88a1b76ac42302022-06-26T19:27:05.1220534Z2022-06-26T20:24:53.5616443Z00:57:48.4395909202Unsuccessful login. Account wa
63ce6481-818e-4f3b-913e-88a1b76ac42312022-06-27T02:17:03.4123257Z2022-06-27T02:36:50.1918116Z00:19:46.7794859312Successful login
63ce6481-818e-4f3b-913e-88a1b76ac42322022-06-27T13:27:27.2550722Z2022-06-27T14:32:39.6361479Z01:05:12.3810757321Successful login
63ce6481-818e-4f3b-913e-88a1b76ac42332022-06-27T17:20:34.3725797Z2022-06-27T17:20:34.3725797Z00:00:00101Unsuccessful login. Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb02022-06-26T22:38:39.3105749Z2022-06-26T22:38:39.3105749Z00:00:00101Unsuccessful login. Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb12022-06-27T03:06:04.340965Z2022-06-27T04:49:37.3314224Z01:43:32.9904574330Successful login
6ed81ab3-447e-481d-8bb3-a5f4087234bb22022-06-27T07:11:47.260913Z2022-06-27T07:11:47.260913Z00:00:00101Unsuccessful login. Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb32022-06-27T11:39:02.356791Z2022-06-27T16:49:23.5818891Z05:10:21.2250981422Unsuccessful login. Invalid cred

Fiddle

回复 原文
深府石板幽径 2025-02-18 11:49:42

我需要Countif（name ...）也可以通过标识符不同。

如果我正确理解您的意图，则可以使用dcountif（）。

例如：

customEvents
| where timestamp > ago(1d)
| extend Identifier = tostring(customDimensions["Identifier"])
| summarize TotalUserCount = dcount(Identifier),
            SuccessCount = dcountif(Identifier, name startswith "Success"),
            FailureCount = dcountif(Identifier, name !startswith "Success")

成功尝试之前的登录尝试次数（如果有的话） - 在kql？

中是否可能

中是否可以尝试使用scan> scan操作员： https://learlen.microsoft.com/en-us /azure/data-explorer/kusto/query/scan-operator

I need countif(name...) to also be distinct by Identifier.

If I understood your intention correctly, you could use dcountif().

For example:

customEvents
| where timestamp > ago(1d)
| extend Identifier = tostring(customDimensions["Identifier"])
| summarize TotalUserCount = dcount(Identifier),
            SuccessCount = dcountif(Identifier, name startswith "Success"),
            FailureCount = dcountif(Identifier, name !startswith "Success")

The number of failed login attempts that preceded a successful attempt (if any) - is this even possible in KQL?

You could try using the scan operator for this: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scan-operator

回复 原文
~没有更多了~

关于作者

佞臣

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文