Need help for KQL query
Sonika Sahu
20
Reputation points
I have 2 KQL :
1st is:
AppEvents
| where Name contains "MANAGEPASSKEYS_ADDPROCESS_INITIATED"
| project
Event = Name,
obj = tostring(Properties.ObjectId),
session = tostring(Properties.CorrelationId)
| distinct Event, obj, session
| join kind=leftouter (AppEvents
| where Name contains "MANAGEPASSKEYS_ADDPROCESS_INITIATED"
or Name contains "PasskeyAdd-Success"
or Name contains "PasskeyAdd-Failure"
or Name contains "MFAOnboardingBehaviourEnroll-SubJourneyEnd"
or Name contains "ShowStrongMFAOptions_Methods-SubJourneyEnd"
or Name contains "PasskeyAdd-AlreadyExists-Failure"
or Name contains "PasskeyAdd-DeviceNotSupported"
or Name contains "PasskeyAdd-Failure-OS"
| project
Event = Name,
obj = tostring(Properties.ObjectId),
session = tostring(Properties.CorrelationId))
on session
| distinct Event1, obj
| extend Event1 = replace_string(Event1, 'MANAGEPASSKEYS_ADDPROCESS_INITIATED', '1--Click Add Passkey')
| extend Event1 = replace_string(Event1, 'MFAOnboardingBehaviourEnroll-SubJourneyEnd', '2-MFA onboaded')
| extend Event1 = replace_string(Event1, 'ShowStrongMFAOptions_Methods-SubJourneyEnd', '3-MFA Verification')
| extend Event1 = replace_string(Event1, 'PasskeyAdd-Success', '4-Passkey successfully added')
| extend Event1 = replace_string(Event1, 'PasskeyAdd-Failure', '5-Passkey Add failed-Generic')
//| extend Event1 = replace_string(Event1, 'MSAGEPASSKEYS_CANCELPROCESS_INITIATED', '6-cancel journey initated')
| extend Event1 = replace_string(Event1, 'PasskeyAdd-AlreadyExists-Failure', '6-Passkey Add failed-AlreadyExists-Failure')
| extend Event1 = replace_string(Event1, 'PasskeyAdd-DeviceNotSupported', '7-Passkey Add failed-DeviceNotSupported')
| extend Event1 = replace_string(Event1, 'PasskeyAdd-Failure-OS', '8-Passkey Add failed-Failure-OS')
| summarize count() by Event1
| sort by Event1 asc
And 2nd is :
//Unique User
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 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")
)
)
)
)
// Only include sessions where the cancel event was present
| where HasCancel == true
| 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
)
| where FinalEventLabel == "cancel journey initiated"
| summarize count() by FinalEventLabel
| sort by FinalEventLabel asc
I want both KQL output as below format so please merge KQL without changing functinality:
1--Click Add Passkey
2-MFA onboaded
3-MFA Verification
4-Passkey successfully added
5-Passkey Add failed-Generic-OS
6-Passkey Add failed-AlreadyExists-Failure
7-cancel journey initiated
Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
3,645 questions
1 answer
Sort by: Most helpful
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more