The Curious Capabilities of Case Scalar Function

Have you checked out the Case scalar function? If not this post is all about what the capabilities of Case scalar function are. However, as I mentioned previously, there is a lot of good documentation around Kusto, and then there are others that could use more documentation, this is one of those examples.

From the documentation linked above:

Evaluates a list of predicates and returns the first result expression whose predicate is satisfied.

If neither of the predicates return true, the result of the last expression (the else) is returned. All odd arguments (count starts at 1) must be expressions that evaluate to a boolean value. All even arguments (the thens) and the last argument (the else) must be of the same type.

And one only example provided. One is left wondering what the Case Scalar Function can actually do.

 

Case Scalar Function Example One

Back in January Anders Bengtsson put out a post about building a report with Workbooks. In his workbook he had this example code about reading security events, specifically Event 4625 for failed user logins.


SecurityEvent
| where AccountType == ‘User’ and EventID == 4625
| where TimeGenerated ago(30d)
| extend Reason = case(
SubStatus == ‘0xc000005e’, ‘No logon servers available to service the logon request’,
SubStatus == ‘0xc0000062’, ‘Account name is not properly formatted’,
SubStatus == ‘0xc0000064’, ‘Account name does not exist’,
SubStatus == ‘0xc000006a’, ‘Incorrect password’,
SubStatus == ‘0xc000006d’, ‘Bad user name or password’,
SubStatus == ‘0xc000006f’, ‘User logon blocked by account restriction’,
SubStatus == ‘0xc000006f’, ‘User logon outside of restricted logon hours’,
SubStatus == ‘0xc0000070’, ‘User logon blocked by workstation restriction’,
SubStatus == ‘0xc0000071’, ‘Password has expired’,
SubStatus == ‘0xc0000072’, ‘Account is disabled’,
SubStatus == ‘0xc0000133’, ‘Clocks between DC and other computer too far out of sync’,
SubStatus == ‘0xc000015b’, ‘The user has not been granted the requested logon right at this machine’,
SubStatus == ‘0xc0000193’, ‘Account has expirated’,
SubStatus == ‘0xc0000224’, ‘User is required to change password at next logon’,
SubStatus == ‘0xc0000234’, ‘Account is currently locked out’,
strcat(‘Unknown reason substatus: ‘, SubStatus))
| project TimeGenerated, Account, Reason, Computer

First, notice how he is using Case with what seems to be more than 3 predicates. Second, instead of classifying a number he’s translating error codes to strings with the Reason field. This one works because SubStatus is treated as one predicate and the strcat portion acts as the last predicate if you remove it you’ll get an error stating “case operator requires an odd number of arguments” this is definitely interesting and is essentially using it similarly as a Switch in PowerShell.

This is a great sample code you should be using anyway, but its also a great example of using the Case scalar function.

 

Case Scalar Function Example 2

Last year, Bluemedora reached out to me and asked if I wanted to demo their Bindplane product with Log Analytics. Naturally I agreed and we got setup. One of the dashboards they had contained a health score, curious how they were coming up with that I took at look at it. In this code they used case and then multiplied it by 100 to get a health score. This is a recreation of the code using native Perf data, they had their own custom logs they were using. However, I used something similar to this in the WVD examples to create a health score of WVD servers.

 


let avgRAM = Perf | where ObjectName == "Memory" and CounterName == "% Committed Bytes In Use" and TimeGenerated >= now(-10m) 
| summarize avg(toint(CounterValue)) by Computer, TimeGenerated 
| project Computer, Memory=avg_CounterValue; 
let avgCPU = Perf | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total" and TimeGenerated >= now(-10m) 
| summarize avg(toint(CounterValue)) by Computer, TimeGenerated 
| project Computer, CPU=avg_CounterValue; 
let avgDisk = Perf | where ObjectName == "LogicalDisk" and CounterName == "% Free Space" and TimeGenerated >= now(-10m)
| summarize avg(toint(CounterValue)) by Computer, TimeGenerated 
| project Computer, Disk=avg_CounterValue;
avgRAM 
| join (avgCPU) on Computer 
| join (avgDisk) on Computer 
| extend Healthy = case(['CPU'] < 85,['Memory'] < 85, ['Disk'] > 10) 
| summarize HealthScore = avg(Healthy) * 100 by Computer 
| summarize count() by case(HealthScore < 50, 'Critical', HealthScore < 70, 'Warning', isnull(HealthScore) or isnan(HealthScore), 'Unknown', 'Healthy')
You can see from the code above there are actually two usages of Case, the second is almost exactly like the example code provided in the docs, classifying text from a few numbers. The first, however, takes CPU, Memory and Disk, multiplies them by 100 to generate the score. When I first saw this I was like how is this even working. But testing and testing some more, sure enough it worked. I could make a server use up CPU or Memory and the score would drop once it breached the threshold.
You could take this second example and replace Disk Space with IOPs or really use any 3 counters you want, to create a score for them.

Conclusion

Case can clearly be used a number of interesting ways.
Do you have any other examples usages of Case? I would love to see them and share them if you wouldn’t mind.