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)
- T | extend duration = endTime – startTime (https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/extend-operator)
- split(source, delimiter [, requestedIndex]) (https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/split())
- toupper(“hello”) == “HELLO” (https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/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:
- print str = strcat_array(dynamic([1, 2, 3]), “->”) (https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/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:
- For the new query languae, always work with the documentation team’s great reference information available at: https://docs.loganalytics.io/docs/Language-Reference
- And for Log Analytics in general: https://docs.microsoft.com/en-us/azure/log-analytics/
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!
