Need help in KQL query to get the count from Azure logs.

Question

Need help in KQL query to get the count from Azure logs.

Sonika Sahu 20

I have requirement where Count MSAGEPASSKEYS_CANCELPROCESS_INITIATED event only if it appears alone in a session (i.e., not with PasskeyAdd-Success).

If both MSAGEPASSKEYS_CANCELPROCESS_INITIATED and PasskeyAdd-Success are present in the same session, only count PasskeyAdd-Success Event.

I've created below KQL but it's not working as expected.

AppEvents | where Name contains "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" | project Event1 = Name, obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId) | distinct Event1, obj, session | join kind=leftouter (AppEvents | where Name = "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" and Name != "PasskeyAdd-Success" | project Event1 = Name, obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)) on session | distinct Event1, obj | extend Event1 = replace_string(Event1, 'MSAGEPASSKEYS_CANCELPROCESS_INITIATED', '6-cancel journey initated') | summarize count() by Event1 | sort by Event1 asc

Marcin Policht 49,640 MVP Volunteer Moderator

Try the following:

// Step 1: Get relevant events
AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize Events = make_set(Name) by session, obj

// Step 2: Classify sessions
| extend 
    HasCancel = "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" in Events,
    HasSuccess = "PasskeyAdd-Success" in Events
| extend
    FinalEvent = case(
        HasSuccess, "PasskeyAdd-Success",
        HasCancel and not HasSuccess, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
        "Other"
    )
| where FinalEvent != "Other"
| summarize count() by FinalEvent
| sort by FinalEvent asc

make_set(Name) gathers all events per session.
- If session has PasskeyAdd-Success, count it and ignore CANCELPROCESS_INITIATED.
- If session has only CANCELPROCESS_INITIATED, count it.
case() ensures only one event is counted per session based on your rule.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Sonika Sahu 20 Reputation points

2025-06-14T15:39:28.67+00:00

Getting error
"Query could not be parsed at 'Events' on line [8,60] Token: Events Line: 8"
Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T15:58:58.49+00:00

Try the following AppEvents | where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success") | extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId) | summarize EventList = make_set(Name) by session, obj | extend HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" | extend HasSuccess = EventList has "PasskeyAdd-Success" | extend FinalEvent = iff(HasSuccess, "PasskeyAdd-Success", iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")) | where FinalEvent != "Other" | summarize count() by FinalEvent | sort by FinalEvent asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Sonika Sahu 20 Reputation points

2025-06-14T16:20:44.0866667+00:00

Thanks above query works.

I need help with 3rd Event to be included in above query where Count MSAGEPASSKEYS_CANCELPROCESS_INITIATED event only if it appears alone in a session (i.e., not with PasskeyAdd-Success and PasskeyRemove-Success).

If both MSAGEPASSKEYS_CANCELPROCESS_INITIATED and PasskeyAdd-Success are present in the same session, only count PasskeyAdd-Success Event.
And
If both MSAGEPASSKEYS_CANCELPROCESS_INITIATED and PasskeyRemove-Success are present in the same session, only count PasskeyRemove-Success Event.

Marcin Policht 49,640 MVP Volunteer Moderator

Try the following

AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize EventList = make_set(Name) by session, obj
| extend 
    HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
    HasAdd = EventList has "PasskeyAdd-Success",
    HasRemove = EventList has "PasskeyRemove-Success"
| extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success",
                      iff(HasRemove, "PasskeyRemove-Success",
                      iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")))
| where FinalEvent != "Other"
| summarize count() by FinalEvent
| sort by FinalEvent asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Marcin Policht 49,640 MVP Volunteer Moderator

You can use a extend with case() or iff() to rename the event labels for the final output.

AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize EventList = make_set(Name) by session, obj
| extend 
    HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
    HasAdd = EventList has "PasskeyAdd-Success",
    HasRemove = EventList has "PasskeyRemove-Success"
| extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success",
                      iff(HasRemove, "PasskeyRemove-Success",
                      iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")))
| where FinalEvent != "Other"
| extend FinalEventLabel = case(
    FinalEvent == "PasskeyAdd-Success", "Passkey successfully added",
    FinalEvent == "PasskeyRemove-Success", "Passkey Removed",
    FinalEvent == "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "cancel journey initiated",
    FinalEvent
)
| summarize count() by FinalEventLabel
| sort by FinalEventLabel asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Sonika Sahu 20 Reputation points

2025-06-14T18:36:48.2333333+00:00

Thankyou above works!

Could please help me with below scenario and create one KQL:

to monitor how many customers are created this way and how many activate their account by setting up their password.

example of reporting:

ID123: autoprovision/autoprovision = someone who didn't set their password

AC1:

Ability to know the total number of customers created via auto-provision BUT have not yet setup the password

In the App Event logs the last Event name will be "TAPI_AUTOPROVISION_JWT_CREATE_SUCCESS". And Policy name is "B2C_1A_RPBT_AccountActivation" distinct Event1, obj and session also should be unique. Unique session and unique user Id.

I need KQL which will check in different Application and give count with Application name. Apllication client Id I'll add in Query once you will share the Query.

Accepted answer

0 additional answers

Your answer

Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T15:23:49.7166667+00:00

Try the following:

// Step 1: Get relevant events AppEvents | where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success") | extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId) | summarize Events = make_set(Name) by session, obj // Step 2: Classify sessions | extend HasCancel = "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" in Events, HasSuccess = "PasskeyAdd-Success" in Events | extend FinalEvent = case( HasSuccess, "PasskeyAdd-Success", HasCancel and not HasSuccess, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other" ) | where FinalEvent != "Other" | summarize count() by FinalEvent | sort by FinalEvent asc

make_set(Name) gathers all events per session.

If session has PasskeyAdd-Success, count it and ignore CANCELPROCESS_INITIATED.

If session has only CANCELPROCESS_INITIATED, count it.

case() ensures only one event is counted per session based on your rule.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Sonika Sahu 20 Reputation points

2025-06-14T15:39:28.67+00:00

Getting error
"Query could not be parsed at 'Events' on line [8,60] Token: Events Line: 8"
Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T15:58:58.49+00:00

Try the following AppEvents | where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success") | extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId) | summarize EventList = make_set(Name) by session, obj | extend HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" | extend HasSuccess = EventList has "PasskeyAdd-Success" | extend FinalEvent = iff(HasSuccess, "PasskeyAdd-Success", iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")) | where FinalEvent != "Other" | summarize count() by FinalEvent | sort by FinalEvent asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Sonika Sahu 20 Reputation points

2025-06-14T16:20:44.0866667+00:00

Thanks above query works.

I need help with 3rd Event to be included in above query where Count MSAGEPASSKEYS_CANCELPROCESS_INITIATED event only if it appears alone in a session (i.e., not with PasskeyAdd-Success and PasskeyRemove-Success).

If both MSAGEPASSKEYS_CANCELPROCESS_INITIATED and PasskeyAdd-Success are present in the same session, only count PasskeyAdd-Success Event.
And
If both MSAGEPASSKEYS_CANCELPROCESS_INITIATED and PasskeyRemove-Success are present in the same session, only count PasskeyRemove-Success Event.
Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T17:22:23.8433333+00:00

Try the following

AppEvents | where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success") | extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId) | summarize EventList = make_set(Name) by session, obj | extend HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", HasAdd = EventList has "PasskeyAdd-Success", HasRemove = EventList has "PasskeyRemove-Success" | extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success", iff(HasRemove, "PasskeyRemove-Success", iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other"))) | where FinalEvent != "Other" | summarize count() by FinalEvent | sort by FinalEvent asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T17:46:30.12+00:00

You can use a extend with case() or iff() to rename the event labels for the final output.

AppEvents | where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success") | extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId) | summarize EventList = make_set(Name) by session, obj | extend HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", HasAdd = EventList has "PasskeyAdd-Success", HasRemove = EventList has "PasskeyRemove-Success" | extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success", iff(HasRemove, "PasskeyRemove-Success", iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other"))) | where FinalEvent != "Other" | extend FinalEventLabel = case( FinalEvent == "PasskeyAdd-Success", "Passkey successfully added", FinalEvent == "PasskeyRemove-Success", "Passkey Removed", FinalEvent == "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "cancel journey initiated", FinalEvent ) | summarize count() by FinalEventLabel | sort by FinalEventLabel asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Sonika Sahu 20 Reputation points

2025-06-14T18:36:48.2333333+00:00

Thankyou above works!

Could please help me with below scenario and create one KQL:

to monitor how many customers are created this way and how many activate their account by setting up their password.

example of reporting:

ID123: autoprovision/autoprovision = someone who didn't set their password

AC1:

Ability to know the total number of customers created via auto-provision BUT have not yet setup the password

In the App Event logs the last Event name will be "TAPI_AUTOPROVISION_JWT_CREATE_SUCCESS". And Policy name is "B2C_1A_RPBT_AccountActivation" distinct Event1, obj and session also should be unique. Unique session and unique user Id.

I need KQL which will check in different Application and give count with Application name. Apllication client Id I'll add in Query once you will share the Query.

Answer 1

Marcin Policht 49,640 MVP Volunteer Moderator

Try the following

AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize EventList = make_set(Name) by session, obj
| extend 
    HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
    HasAdd = EventList has "PasskeyAdd-Success",
    HasRemove = EventList has "PasskeyRemove-Success"
| extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success",
                      iff(HasRemove, "PasskeyRemove-Success",
                      iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")))
| where FinalEvent != "Other"
| summarize count() by FinalEvent
| sort by FinalEvent asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Sonika Sahu 20 Reputation points

2025-06-14T17:24:12.6333333+00:00

Thankyou above query is working but I want in result Event name replace with below:
MSAGEPASSKEYS_CANCELPROCESS_INITIATED', 'cancel journey initiated'
PasskeyAdd-Success - 'Passkey successfully added'
PasskeyRemove-Success - Passkey Removed

Marcin Policht 49,640 MVP Volunteer Moderator

Try the following

AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize EventList = make_set(Name) by session, obj
| extend 
    HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
    HasAdd = EventList has "PasskeyAdd-Success",
    HasRemove = EventList has "PasskeyRemove-Success"
| extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success",
                      iff(HasRemove, "PasskeyRemove-Success",
                      iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")))
| where FinalEvent != "Other"
| summarize count() by FinalEvent
| sort by FinalEvent asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T19:25:44.72+00:00

Pls make sure to respond by adding comments to the current thread - rather than posting your responses as answers

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Sonika Sahu 20 Reputation points

2025-06-14T19:25:46.8433333+00:00

Above query works .
Marcin Policht 49,640 Reputation points MVP Volunteer Moderator

2025-06-14T19:26:50.51+00:00

Pls make sure to respond by adding comments to the current thread - rather than posting your responses as answers

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Marcin Policht 49,640 MVP Volunteer Moderator

Try the following

AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize EventList = make_set(Name) by session, obj
| extend 
    HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
    HasAdd = EventList has "PasskeyAdd-Success",
    HasRemove = EventList has "PasskeyRemove-Success"
| extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success",
                      iff(HasRemove, "PasskeyRemove-Success",
                      iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")))
| where FinalEvent != "Other"
| extend FinalEventLabel = case(
    FinalEvent == "PasskeyAdd-Success", "Passkey successfully added",
    FinalEvent == "PasskeyRemove-Success", "Passkey Removed",
    FinalEvent == "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "cancel journey initiated",
    FinalEvent
)
| summarize count() by FinalEventLabel
| sort by FinalEventLabel asc

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Sonika Sahu 20 Reputation points

2025-06-15T12:06:01.33+00:00

For above KQL for "PasskeyRemove-Success" Event , 3 Event triggered so above Query need to be edit in such way where for Passkey Removed it should include below event if any event triggered it should count under Passkey Removed. "FIDORemovePasskey-EndSubJourney" "PasskeyRemove-Success" "FIDORemovePasskey-StartSubJourney"

Sonika Sahu 20

I have created below query but I want below to be tweak in such way that if in session only "MSAGEPASSKEYS_CANCELPROCESS_INITIATED" Event triggered then it should count under "cancel journey initiated" if it is triggered with below given other Event then count should be added to those events rather than cancel journey initiated.

AppEvents
| where Name in ("MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "PasskeyAdd-Success", "PasskeyRemove-Success", "FIDORemovePasskey-StartSubJourney", "FIDORemovePasskey-EndSubJourney")
| extend obj = tostring(Properties.ObjectId), session = tostring(Properties.CorrelationId)
| summarize EventList = make_set(Name) by session, obj
| extend 
    HasCancel = EventList has "MSAGEPASSKEYS_CANCELPROCESS_INITIATED",
    HasAdd = EventList has "PasskeyAdd-Success",
    HasRemove = EventList has "PasskeyRemove-Success",
	HasFIDORemoveStart = EventList has "FIDORemovePasskey-StartSubJourney",
	HasFIDORemoveEnd = EventList has "FIDORemovePasskey-EndSubJourney"
| extend FinalEvent = iff(HasAdd, "PasskeyAdd-Success",
                      iff(HasRemove, "PasskeyRemove-Success",
					  iff(HasFIDORemoveStart, "FIDORemovePasskey-StartSubJourney",
					  iff(HasFIDORemoveEnd, "FIDORemovePasskey-EndSubJourney",
                      iff(HasCancel, "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "Other")))))
| where FinalEvent != "Other"
| extend FinalEventLabel = case(
    FinalEvent == "PasskeyAdd-Success", "Passkey successfully added",
    FinalEvent == "PasskeyRemove-Success", "Passkey successfully Removed",
    FinalEvent == "FIDORemovePasskey-StartSubJourney", "Passkey remove Journey Started",
    FinalEvent == "FIDORemovePasskey-EndSubJourney", "Passkey Remove Journey End",
    FinalEvent == "MSAGEPASSKEYS_CANCELPROCESS_INITIATED", "cancel journey initiated",
    FinalEvent
)
| summarize count() by FinalEventLabel
| sort by FinalEventLabel asc

Share via

Need help in KQL query to get the count from Azure logs.

0 additional answers

Your answer