Kusto | Summarize count() multiple columns with where clauses

Question

I'm trying to get the count of multiple things in a Kusto query but having trouble getting it working. Let's say I have a sample table like this:

let SampleTable = datatable(Department:string, Status:string, DateStamp:datetime) 
[
   "Logistics", "Open", "05-01-2019",
   "Finance", "Closed", "05-01-2020",
   "Logistics", "Open", "05-01-2020"
];

And I query like this:

SampleTable
| summarize closedEntries = count() by (Status | where Status == "Closed"), 
    openEntries = (Status | where Status == "Open"),
    recentDates = (DateStamp | where DateStamp > "12-31-2019"),
    Department

Expected results:

But this gives an error "The name 'Status' does not refer to any known column, table, variable or function." and the same error for DateStamp. I've also tried using extend and join but it's a mess.

Answer 1

you could use the countif() aggregation function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/countif-aggfunction

datatable(Department:string, Status:string, DateStamp:datetime) 
[
   "Logistics", "Open", "05-01-2019",
   "Finance", "Closed", "05-01-2020",
   "Logistics", "Open", "05-01-2020"
]
| summarize closedEntries = countif(Status == "Closed"),
            openEntries = countif(Status == "Open"),
            recentDates = countif(DateStamp > datetime(12-31-2019))
         by Department