How to format output

Brass Contributor

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 resetsuccess 961
User started security info registration for self-service password resetsuccess1,112
Security info saved for self-service password resetsuccess969
User started security info registration for self-service password resetunknownFutureValue403
Self-service password reset flow activity progresssuccess211
Unlock user account (self-service)success13
Self-service password reset flow activity progressfailure51
User completed security info registration for self-service password resetunknownFutureValue47
Reset password (self-service)success2
Reset password (self-service)failure14

 

2 Replies

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

 

@Yoni This is perfect, thank you very much for the guidance and the links!