Forum Discussion
How to format output
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 |
2 Replies
- Yoni
Microsoft
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
