How can I aggregate fields based on the value of another field?

Question

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:

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.

Answer 1

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

Identifier	session_id	session_start	session_end	session_duration	session_events	session_successes	session_failures	session_last_name
3b169e06-52e5-45d8-b951-62d5e8ab385b	0	2022-06-26T20:22:22.4006737Z	2022-06-26T20:22:22.4006737Z	00:00:00	1	0	1	Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b	1	2022-06-26T22:47:01.8487347Z	2022-06-26T22:47:01.8487347Z	00:00:00	1	1	0	Successful login
3b169e06-52e5-45d8-b951-62d5e8ab385b	2	2022-06-27T04:57:15.6405722Z	2022-06-27T07:32:10.4409854Z	02:34:54.8004132	4	1	3	Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b	3	2022-06-27T10:44:19.8739205Z	2022-06-27T12:46:14.2586725Z	02:01:54.3847520	3	0	3	Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b	4	2022-06-27T14:50:35.3882433Z	2022-06-27T14:50:35.3882433Z	00:00:00	1	0	1	Unsuccessful login. Account wa
3b169e06-52e5-45d8-b951-62d5e8ab385b	5	2022-06-27T18:33:51.4464796Z	2022-06-27T18:47:06.0628481Z	00:13:14.6163685	2	0	2	Unsuccessful login. Invalid cred
63ce6481-818e-4f3b-913e-88a1b76ac423	0	2022-06-26T19:27:05.1220534Z	2022-06-26T20:24:53.5616443Z	00:57:48.4395909	2	0	2	Unsuccessful login. Account wa
63ce6481-818e-4f3b-913e-88a1b76ac423	1	2022-06-27T02:17:03.4123257Z	2022-06-27T02:36:50.1918116Z	00:19:46.7794859	3	1	2	Successful login
63ce6481-818e-4f3b-913e-88a1b76ac423	2	2022-06-27T13:27:27.2550722Z	2022-06-27T14:32:39.6361479Z	01:05:12.3810757	3	2	1	Successful login
63ce6481-818e-4f3b-913e-88a1b76ac423	3	2022-06-27T17:20:34.3725797Z	2022-06-27T17:20:34.3725797Z	00:00:00	1	0	1	Unsuccessful login. Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb	0	2022-06-26T22:38:39.3105749Z	2022-06-26T22:38:39.3105749Z	00:00:00	1	0	1	Unsuccessful login. Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb	1	2022-06-27T03:06:04.340965Z	2022-06-27T04:49:37.3314224Z	01:43:32.9904574	3	3	0	Successful login
6ed81ab3-447e-481d-8bb3-a5f4087234bb	2	2022-06-27T07:11:47.260913Z	2022-06-27T07:11:47.260913Z	00:00:00	1	0	1	Unsuccessful login. Account wa
6ed81ab3-447e-481d-8bb3-a5f4087234bb	3	2022-06-27T11:39:02.356791Z	2022-06-27T16:49:23.5818891Z	05:10:21.2250981	4	2	2	Unsuccessful login. Invalid cred

Fiddle