Using Kusto and thresholds to color code values

In the first part of this blog series, I provided a step-by-step approach to color-code a field in a workbook based upon the value of a text field. In this blog post, we will take that concept a step further by using Kusto to provide the information required to visualize the state of a text field based upon several different potential values of the data.

The goal of this visualization was to provide a way to quickly identify the health of the backup state for a set of SQL databases. Anything critical should be red, anything in warning should be yellow and if neither of those applies it should be in a green state (healthy). The logic to provide these colors is straightforward on the workspace side once we get the query where it provides the values in a format that we can easily use them (IE: it identifies for the “BackupState” field we defined if there are any “Critical” or “Warning” conditions).

For simplicity, we have broken down the query into various sections to explain what’s occurring throughout the query.

First, we define the variables which we will use to determine the health state. These are thresholds for various backup configurations:

let fullBackupThresholdDays = 7;

let differentialBackupThresholdDays = 1;

let logBackupThresholdDays = 1;

Next, we query to identify the last batch of data that was written into the CL which we created.

let lastBatch = HC_SQL_LastDatabaseBackup_CL

| where TimeGenerated > ago(15d)      and IsSchemaUpdate_b != “true”

| summarize arg_max(TimeGenerated, *)

|project Batch_s;

Now we query the last set of data, configure the various fields into human-readable output, sort, and remove any irrelevant fields.

HC_SQL_LastDatabaseBackup_CL

| where TimeGenerated > ago(15d)      and IsSchemaUpdate_b != “true”      and Batch_s in (lastBatch)

| extend  IsCompressed_b = case(IsCompressed_b == “true”, “Yes”, “No”)

| extend LastFullBackup = iff(isempty(LastFullBackup_t), “N/A”, format_datetime(LastFullBackup_t, “MM/dd/yyyy HH:mm:ss”))

| extend LastDifferentialBackup = iff(isempty(LastDifferentialBackup_t), “N/A”, format_datetime(LastDifferentialBackup_t, “MM/dd/yyyy HH:mm:ss”))

| extend LastLogBackup = iff(isempty(LastLogBackup_t), “N/A”, format_datetime(LastLogBackup_t, “MM/dd/yyyy HH:mm:ss”))

| project TimeGenerated      , Server = MachineName_s      , FullInstanceName = FullInstanceName_s      , Database = Database_s      , RecoveryModel = RecoveryModel_s      , LastFullBackup      , LastDifferentialBackup      , LastLogBackup      , Compressed = IsCompressed_b      , Comments = HC_SQL_Comments_s

| summarize arg_max(TimeGenerated, *) by Server, FullInstanceName, Database

| sort by Server asc      , FullInstanceName asc      , Database asc

| project–away Server

Everything above this point in the query was just our default query to show the content of our _CL defined data. Below this we now start to prepare fields to eventually generate our “BackupState” text field. We start by identifying how many days it has been since each type of backup has occurred.

| extend DaysSinceLastFullBackup = datetime_diff(‘day’,TimeGenerated, todatetime(LastFullBackup))

| extend DaysSinceLastDifferentialBackup = datetime_diff(‘day’,TimeGenerated, todatetime(LastDifferentialBackup))

| extend DaysSinceLastLogBackup = datetime_diff(‘day’,TimeGenerated, todatetime(LastLogBackup))

Now we create a specific test condition for each set of data that could impact the state of the BackupState field.

NOTE: It would be very possible to streamline the code shown in the next two sections. I have chosen not to do so in order to keep the debugging of this code as easy as possible (IE: In this configuration I can see the state of any of the 10 conditions by just showing the value of the BackupStateTest# field).

| extend BackupStateTest1 = iff(Compressed == “No”, “Warning: Not Compressed”, “”)

| extend BackupStateTest2 = iff(DaysSinceLastFullBackup > fullBackupThresholdDays, “Critical: Full Backup out of date”, “”)

| extend BackupStateTest3 = iff(LastDifferentialBackup == “N/A” and RecoveryModel == “FULL”, “Warning: No recent differential backup”, “”)

| extend BackupStateTest4 = iff(LastDifferentialBackup == “N/A” and RecoveryModel == “BULK_LOGGED”, “Warning: No recent differential backup”, “”)

| extend BackupStateTest5 = iff(DaysSinceLastDifferentialBackup > fullBackupThresholdDays, “Critical: Differential Backup out of date”, “”)

| extend BackupStateTest6 = iff(DaysSinceLastDifferentialBackup > differentialBackupThresholdDays, “Warning: Differential Backup out of date”, “”)

| extend BackupStateTest7 = iff(LastLogBackup == “N/A” and RecoveryModel == “FULL”, “Critical: No last backup identified”, “”)

| extend BackupStateTest8 = iff(LastLogBackup == “N/A” and RecoveryModel == “BULK_LOGGED”, “Critical: No last backup identified”, “”)

| extend BackupStateTest9 = iff(LastLogBackup != “N/A” and DaysSinceLastLogBackup > fullBackupThresholdDays, “Critical: Log backup too old”, “”)

| extend BackupStateTest10 = iff(LastLogBackup != “N/A” and DaysSinceLastLogBackup > logBackupThresholdDays, “Warning: Log backup too old”, “”)

Now we populate the contents of the BackupState field by adding any values which are not blank into the new BackupState field.

| extend BackupState = iff(BackupStateTest1 != “”, BackupState = BackupStateTest1, ”)

| extend BackupState = iff(BackupStateTest2 != “” ,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest2), BackupStateTest2), BackupState)

| extend BackupState = iff(BackupStateTest3 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest3), BackupStateTest3), BackupState)

| extend BackupState = iff(BackupStateTest4 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest4), BackupStateTest4), BackupState)

| extend BackupState = iff(BackupStateTest5 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest5), BackupStateTest5), BackupState)

| extend BackupState = iff(BackupStateTest6 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest6), BackupStateTest6), BackupState)

| extend BackupState = iff(BackupStateTest7 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest7), BackupStateTest7), BackupState)

| extend BackupState = iff(BackupStateTest8 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest8), BackupStateTest8), BackupState)

| extend BackupState = iff(BackupStateTest9 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest9), BackupStateTest9), BackupState)

| extend BackupState = iff(BackupStateTest10 != “”,

                            iff(BackupState != “”, BackupState = strcat_delim(‘, ‘, BackupState, BackupStateTest10), BackupStateTest10), BackupState)

To complete this we project the specific fields we know that we want.

| project FullInstanceName, Database, TimeGenerated, RecoveryModel, LastFullBackup, LastDifferentialBackup, LastLogBackup, Compressed, BackupState, Comments

The result is we have a BackupState field which will either contain “Critical: ” or “Warning: ” if one of these two conditions occurs. This gives us what we need to visualize the value of the BackupState field using the column settings/thresholds configuration we discussed in the previous blog post. An example where the values have one or more warning conditions is shown below.

Summary: You can use the power of Kusto queries to create specific field values based on a variety of conditions within your data and then color code the output using the threshold capabilities of workbooks.