topic DQL: How can I convert row values to column headings in DQL

DQL: How can I convert row values to column headings

HeadScratcher — Wed, 04 Sep 2024 06:04:35 GMT

I have some DQL that looks at a web servers log files and counts the number of hits on services gives me data in a table like this:

service_name	status_code	hit_count
checkOut	200	1234
checkOut	404	1

I would prefer to present it like this so that I can apply percentage calculations and categorise the record as being OK or needing action.

service_name	200	404
checkOut	1234	1

Is there a way to do this kind of thing?

Re: DQL: How can I convert row values to column headings

krzysztof_hoja — Wed, 04 Sep 2024 12:52:16 GMT

Unfortunately the only technique I know to get it is via same method you probably saw already:
https://community.dynatrace.com/t5/DQL/DQL-How-to-Flatten-an-Array/m-p/251125/highlight/true#M1073

For your data it would look like:

data record(serviceName="checkOut", status_code=200, hit_count=1234), record(serviceName="checkOut", status_code=404, hit_count=1), record(serviceName="login", status_code=200, hit_count=4), record(serviceName="login", status_code=403, hit_count=1) | summarize { d=collectArray(array(toString(status_code),hit_count)) }, by: {serviceName} | fieldsAdd d=toString(d) | parse d, "'[' KVP{ '[' DQS:key ', ' LONG:value ']' ', '?}:code ']'" | fieldsFlatten code | fieldsRemove d, code

Kris

Re: DQL: How can I convert row values to column headings

HeadScratcher — Wed, 04 Sep 2024 15:10:54 GMT

Thanks Kris, you are right I did read the other thread. This approach does work fine and is actually quite similar to how I would have approached in in SPL which is where my skillset is coming from.