In writing queries, it is not uncommon to get results where a column has missing values. This can cause concerns or questions from your users. "Why is this blank?", "There must be something wrong with your query its missing data!".
To avoid this, Kusto provides two functions to check for missing values:
isempty. You can combine this with the
iif function (covered in the Fun With KQL – IIF post) to provide clarifying text to the end user.
Before we begin, be aware 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.
isnull function is used with numeric data. Let’s look at this simple example.
We’ll use the
AppExceptions table, and use
where to limit the output to rows where the SeverityLevel column does not have a value, in other words it is null.
This is then piped into the
count operator, to get a count of the number of rows without an entry in the SeverityLevel column. As you can see, there are 2,753 rows without a SeverityLevel.
So what happens if we want to get a listing of rows in the
AppExceptions table, and for rows with a null SeverityLevel print a message letting the user know it isn’t there?
We use our old friend
iif to see if the SeverityLevel is null. Note that we don’t have to use
isnull( SeverityLevel ) == true, the
isnull function always evaluates to true or false. The
iif knows what to do with that true or false, and executes the following parameters accordingly.
If it came out true, the SeverityLevel was null, the text
No Severity Level is returned, to let the user know this row lacked any data in this column.
On the other hand, if there was data in the SeverityLevel column then that value is returned. Note something though, we had to wrap the column in the
Both the true and false sections off an
iif function must return the same datatype! Since the true branch returned a text string, the else must too. Since the SeverityLevel column is of datatype int, we need to convert it to a string.
We do so using the simple
tostring function, which simply takes the passed in numeric value and converts it to a string datatype.
isnull is meant for numeric data, the
isempty function is used with string data.
In this first example we’ll get a count of rows from the
AppRequests table where the column AppRoleName is missing any text.
Here we pipe the
AppRequests table into a
where operator, limiting the dataset to only rows with an empty AppRoleName column. We then use the
count operator and find there are 17,707 rows where this column has no data (as of the running of this query).
Lets emulate what we did with
isnull, and create an
iif statement to let users know that the data in the AppRoleName column wasn’t present.
Here we used
iif to check if AppRoleName is empty. If so, it returns the text
No App Role Name, otherwise it returns the AppRoleName column.
It wasn’t necessary to wrap the column name with
tostring. AppRoleName is already a string datatype, so it will match the hard coded string we have for the true part of the
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.
As you see,
isempty are very easy to use. They can be combined with counts, summaries, if functions, even the case function. With these, users can have some assurance that the column was left with no data, and that it wasn’t an issue with the query.
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.