Azure Log Analytics: A little more on Parse and Print

**** this post was restored, as the original was deleted by mistake ****

In my last post I wrote about Print and Let - which reminded me of some work I did to manipulate some text recently (it also used a Print operator, which was the thing that reminded me).

The raw data was this (it was from an event log but I don't recall which one):

"Device=\\.\\PHYSICALDRIVE; FirmwareRevision=AAA3; Interface=IDE; MediaType=\\HDD\\; Model=123456; SerialNumber=987654321"

So using Print I was able to manipulate the text, each part was needed in a column and some of the data was also needed to be formatted to removed unwanted text or characters.  Ultimately this isn't a finished query rather an example of a few techniques that you may find useful to manipulate text.

Step1 – I used Print as per the last post, then on the next line create an array using the ";" as a character to split the line at:

print txt = "Device=\\.\\PHYSICALDRIVE; FirmwareRevision=AAA3; Interface=IDE; MediaType=\\HDD\\; Model=123456; SerialNumber=987654321"

| extend txtArray = split(txt, ";")                           

As you can see by the above output we now have 6 rows of data returned in the array.

I wanted the data in a column so I used an Extend, which does that, adding these next two lines as an example

| extend DeviceID = txtArray [0]

, Firmware = txtArray [1]

Again this gets us the information, but now we need to tidy it up a little.

I first added an extra line

Interface = split(txtArray [2], "=",1)

This line splits the 2nd item [2] in the array, at the “=” character and then keeps only the txt after that character; change the 1 to 0 if you want the opposite.  You can see below, the column is now named Interface and the result is “IDE”.

The next line of syntax I added is the most complex

MediaType = trim(@"[^\w]+", tostring(split(txtArray [3],"=",1)))

We are doing a few things to the data here.

1. Using Trim and some regex to remove any non word characters from the beginning or end of the array item.  See Trim for help on that.

2. Then I used Split again to get just the text after the “=”, and this time I added tostring as well.  So txtArray[3] went from \\HDD\\ to HDD in the MediaType column:

So the full script now looks like:

print txt = "Device=\\.\\PHYSICALDRIVE; FirmwareRevision=AAA3; Interface=IDE; MediaType=\\HDD\\; Model=123456; SerialNumber=987654321"

| extend txtArray = split(txt, ";") // split on ";" char

| extend DeviceID = txtArray [0]

, Firmware = txtArray [1]

, Interface = split(txtArray [2], "=",1) // like the line above but use split to show just the result

, MediaType = trim(@"[^\w]+", tostring(split(txtArray [3],"=",1))) // again, split on "=" and then trim all non word chars

, Model = txtArray [4]

// etc...

| project DeviceID , Firmware , Interface , MediaType, Model

So in the above example Column 1 and 4 are essentially unformatted, but columns 2 and 3 have two different formats applied to make them more readable.