DQL How to Flatten an Array

[36Krazyfists]

So you can flatten records to individual columns using the fieldsFlatten command, but there's no way to flatten arrays.  You can expand arrays, but that creates separate rows for each element in an array.  I want columns made for each element in the array.

Here's the query I'm running:

fetch dt.entity.host
| fieldsAdd state, osVersion, managementZones
| filter state == "OFFLINE"
| sort entity.name

The managementZones column has arrays of management zones in it.  I want to flatten that into columns of management zones (so mzone1, mzone2, mzone3, mzone4, etc...).  Or, if it's easier, the management zone names could become columns with something like TRUE/FALSE in the cell as well.

Anybody know if this is possible?

[krzysztof_hoja · Accepted solution]

No, unfortunately it is not possible to do in clean DQL, however it is possible to workaround it using DPL and string representation of the array.

You may try this:

fetch dt.entity.host
| fieldsAdd managementZones=array(managementZones[],true)
| fieldsAdd managementZones= toString(managementZones)
| parse managementZones, "'[' KVP{ '[' DQS:key ', ' BOOLEAN:value ']' ', '?}:mz ']'"
| fieldsFlatten mz
| fieldsRemove managementZones, mz

The result looks for me like this:

true is value of the fields names as management zone. When host is not in management zone the filed is simply missing

Kris

[36Krazyfists]

Wow, you replied to one of my other DQL questions with a similarly advanced query.  Not sure how you've learned all this advanced stuff given what's available in the documentation that Dynatrace has provided...

Do you have some secret source of documentation, tips, and examples that the rest of us mere mortals don't have access to, or did you figure all this stuff out on your own?

[HeadScratcher]

@36Krazyfists   you have articulated very well what I am thinking...    There need to be more examples in the docs.