Oct 09 2019 09:18 AM
I'm new to using Kusto and having some trouble getting the data from my query output in the right format.
The following query produces the table below, however I'm trying to get success count and the failure count for each item in the first column to be grouped together (IE see #1) or on the same line (IE see #2). Any suggestions?
#1
User started security info registration for self-service password reset Success xxx
User started security info registration for self-service password reset Failure xxx
#2
User started security info registration for self-service password reset Success xxx Failure xxx
AuditLogs
| where LoggedByService == "Self-service Password Management"
| summarize count () by ActivityDisplayName, Result
User completed security info registration for self-service password reset | success | 961 |
User started security info registration for self-service password reset | success | 1,112 |
Security info saved for self-service password reset | success | 969 |
User started security info registration for self-service password reset | unknownFutureValue | 403 |
Self-service password reset flow activity progress | success | 211 |
Unlock user account (self-service) | success | 13 |
Self-service password reset flow activity progress | failure | 51 |
User completed security info registration for self-service password reset | unknownFutureValue | 47 |
Reset password (self-service) | success | 2 |
Reset password (self-service) | failure | 14 |
Oct 09 2019 09:26 AM - edited Oct 09 2019 09:34 AM
for having both in the same output row, you could potentially use `countif()`: https://docs.microsoft.com/en-us/azure/kusto/query/countif-aggfunction
let AuditLogs = datatable(LoggedByService:string, ActivityDisplayName:string, Result:string) [ "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "failure", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", ] ; AuditLogs | where LoggedByService == "Self-service Password Management" | summarize SuccessCount = countif(Result == "success"), FailureCount = countif(Result == "failure") by ActivityDisplayName
alternatively, if you want the output in the form of a property bag, you could use `make_bag()` on top of your original aggregation: https://docs.microsoft.com/en-us/azure/kusto/query/make-bag-aggfunction
let AuditLogs = datatable(LoggedByService:string, ActivityDisplayName:string, Result:string) [ "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "failure", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", ] ; AuditLogs | where LoggedByService == "Self-service Password Management" | summarize count() by ActivityDisplayName, Result | summarize Results = make_bag(pack(Result, count_)) by ActivityDisplayName
Oct 09 2019 09:36 AM
@Yoni This is perfect, thank you very much for the guidance and the links!