Forum Discussion
Mcsood
Oct 09, 2019Brass Contributor
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 t...
Yoni
Microsoft
Oct 09, 2019for 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