In my previous post, Fun With KQL – DCountIf, we saw how you could apply a filter to the data directly within the
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:
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.
Now let’s see how to do the same thing with the
First, we omitted the
where since the filterning will be done in the
Next we use
summarize, and this time set a column name of
RowCount to hold the value returned by
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
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
No Guesswork Here
Just to be clear,
dcountif, returns an exact value, not an estimate.
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.
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.