Introduction
In my previous post, Fun With KQL – DCountIf, we saw how you could apply a filter to the data directly within the dcountif function.
You may have been thinking gosh, it sure would be nice if we could do that within the count function! (Note, you can read more about count in my Fun With KQL – Count blog post.)
Well fear not intrepid Kusto coder, there is just such a function to fit your needs: countif.
The samples in this post will be run inside the LogAnalytics demo site found at https://aka.ms/LADemo. This demo site has been provided by Microsoft and can be used to learn the Kusto Query Language at no cost to you.
If you’ve not read my introductory post in this series, I’d advise you to do so now. It describes the user interface in detail. You’ll find it at https://arcanecode.com/2022/04/11/fun-with-kql-the-kusto-query-language/.
Note that my output may not look exactly like yours when you run the sample queries for several reasons. First, Microsoft only keeps a few days of demo data, which are constantly updated, so the dates and sample data won’t match the screen shots.
Second, I’ll be using the column tool (discussed in the introductory post) to limit the output to just the columns needed to demonstrate the query. Finally, Microsoft may make changes to both the user interface and the data structures between the time I write this and when you read it.
Count – A Refresher
Before we tackle countif, let’s refresh ourselves on how count works. Here’s a simple example.
We take our Perf table and pipe it into a where operator to only get back rows where our CounterName column has the word Bytes in it. Note for this example the contains doesn’t care about case, it’ll match Bytes, bytes, BYTES, and so on.
Next we flow into a summarize, which uses the count function to sum up by the CounterName column. Finally we do a sort to make the output easy to read.
CountIf Basics
Now let’s see how to do the same thing with the countif function.
First, we omitted the where since the filterning will be done in the countif.
Next we use summarize, and this time set a column name of RowCount to hold the value returned by countif.
In the countif function we pass a parameter, the expression we want to use for filtering. Here we use the same CounterName contains "Bytes" we used with the where statement in the previous example. We could have used any expression though; looked for an exact match using ==, used in with a list, or any other valid Kusto expression.
Finally, we used sort to sort the output.
Notice something different about these results as compared to the previous example. Here, countif included row counts with 0 values. In contrast, the count operator removes all rows with zeros before it returns the data.
No Zeros Here!
If we want the output of countif to match what count produces, all we need to do is add a where to suppress rows with zero values.
Here all we needed was to use where RowCount > 0 to remove those rows, making the output of countif match count.
No Guesswork Here
Just to be clear, countif, unlike dcountif, returns an exact value, not an estimate.
See Also
The following operators, functions, and/or plugins were used or mentioned in this article’s demos. You can learn more about them in some of my previous posts, linked below.
Conclusion
The countif function can provide a streamlined way to filter our data when we need accurate row counts. We just need to keep in mind it will return data with zero counts, which may be a benefit if your goal is to discover rows where the count is zero. Otherwise you just need to remember to filter those zero value rows out.
The demos in this series of blog posts were inspired by my Pluralsight courses Kusto Query Language (KQL) from Scratch and Introduction to the Azure Data Migration Service, two of the many courses I have on Pluralsight. All of my courses are linked on my About Me page.
If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.
Arcane Code 