Using the new query language for log analytics (NQLFLA) to create consistent computer names

I received an interesting question on my Server Performance Solution for Log Analytics– where they were seeing computers appearing as different due to differences in case and whether or not the computer name was the FQDN. A subset of the question is below:

Question: I’m seeing a lot of duplicates. For example there would be three different computers for:

  • COMPUTER
  • computer
  • computer.domain.com

Answer: The power of the new query language makes this straightforward to resolve. Let’s take an existing query from this solution:

search Computer !in (“-“)

| summarize AggregatedValue = count() by Computer

| where Computer != “”

If we have situations like the one above we can leverage three functions in the new query language: (extend, split and toupper)

If we combine the original query with the new one we get something like this (change in bold below):

search Computer !in (“-“)

| summarize AggregatedValue = count() by Computer

| where Computer != “”

| extend ComputerName = toupper(split (Computer, “.”,0))

This gives us a new “ComputerName” field which is the short name of the computer consistently in upper case.

We can also add a project to the end of this to only show the new name and ignore the original one (change in bold below):

search Computer !in (“-“)

| summarize AggregatedValue = count() by Computer

| where Computer != “”

| extend ComputerName = toupper(split (Computer, “.”,0))

| project ComputerName, AggregatedValue

But wait, something looks strange here. Why does my DC2 list as [“DC2”] instead of just DC2? That because it’s sending back an array and we need a string. To do this we’ll use strcat_array:

So, with another tweak to this query (highlighted below) we can make that change.

search Computer !in (“-“)

| summarize AggregatedValue = count() by Computer

| where Computer != “”

| extend ComputerName = toupper(strcat_array(split (Computer, “.”,0),“”))

| project ComputerName, AggregatedValue

| sort
by AggregatedValue desc

And the output is now good to go!

Additional reference:

Summary: By using the extend, split, toupper and strcat_array functions in the new query language we can take what was an inconsistent set of output (COMPUTER, computer, and computer.domain.com) and move these into a consistent naming.

Thank you Brian “There is no OMS” for putting it in my brain that this is a Log Analytics solution not an OMS solution (hence the rename which occurred in this blog post. I also owe you additional thanks for your help with the process of converting an array to a string!

One Response

  1. Max Serg April 4, 2018

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.