# Extracting details from lookup DQL

**URL:** <https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287>\
**Category:** DQL\
**Tags:** dql, apps, latest-dynatrace\
**Created:** [April 13, 2023, 1:28pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287 "2023-04-13T13:28:01Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![kwangxi](https://avatars.discourse-cdn.com/v4/letter/k/ccd318/32.png) [@kwangxi](https://community.dynatrace.com/u/kwangxi)\
**Post date:** [April 13, 2023, 1:28pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/1 "2023-04-13T13:28:01Z")

</div>

Hi,

I wanted to extract the **type** , **edition** and **versions** out from the technologies to be different columns in a table as well as do a bar chart specifically for **type**.

```auto
"<strong>monitoringMode</strong>": "FULL_STACK",
"<strong>lookup.entityId</strong>": "PROCESS_GROUP_INSTANCE-61A60B5D26F3BB1B",
"<strong>lookup.entityName</strong>": "com.dynatrace.easytravel.weblauncher.jar easytravel-*-x*",
"<strong>lookup.softwareTechnologies</strong>": [
"type:QOS_LOGBACK,edition:null,version:1.2.3",
"type:APACHE_TOMCAT,edition:null,version:7.0.93.0",
"type:JAVA,edition:OpenJDK,version:11.0.5",
"type:JDK_HTTP_SERVER,edition:null,version:null",
"type:APACHE_TOMCAT,edition:null,version:null",
"type:JAVA,edition:null,version:null",
"type:APACHE_HTTP_CLIENT_SYNC,edition:null,version:4.5.10"
],

```

Secondly, is there any way for me to change the name **lookup.softwareTechnologies** to just **softwareTechnologies**. As I couldn’t access it with the “.”

```auto
monitoringMode: record.values?.monitoringMode as string,
<strong>softwareTechnologies: record.values?.softwareTechnologies,

Failed with :
<font color="#FF0000">softwareTechnologies: record.values?.lookup.softwareTechnologies,</font></strong>

```

DQL:

```auto
fetch dt.entity.host, timeframe:"2023-03-01T00:00:00Z/2023-04-05T12:00:00Z"
|fieldsAdd osVersion, cpuCores, memory = (memoryTotal/1000/1000000), ipAddress, monitoringMode
|lookup [fetch dt.entity.process_group_instance
|fieldsAdd softwareTechnologies, tags |filterOut contains (entityName, "OneAgent") OR contains (entityName, "Linux") OR contains (entityName, "sshd") OR contains (entityName, "Short-live") OR contains (entityName, "master") ], sourceField:tags, lookupField:tags

```

---

<div class="post-metadata">

**Author:** ![educampver](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/educampver/32/29917_2.png) [@educampver](https://community.dynatrace.com/u/educampver)\
**Post date:** [April 13, 2023, 2:29pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/2 "2023-04-13T14:29:11Z")

</div>

Hi&nbsp;[@kwangxi](https://community.dynatrace.com/user/58678), you can extract type, edition, and version using the parse command. Something like this should work:

```markup
| filterOut ...
| parse softwareTechnologies[0], "'type:' WORD:type ',edition:' WORD:edition ',version:' WORD:version"

```

As for the second question, there’s no way to remove the prefix completely, but you can change it with the prefix argument like this:

```markup
lookup [
  ...
], sourceField:tags, lookupField:tags, prefix:"_"

```

Now you can access the values like this:

```markup
record.values._softwareTechnologies
record.values._types
...

```

But I’m still curious why the access via lookup fails. Would you try this instead?

```markup
record.values["lookup.softwareTechnologies"]

```

---

<div class="post-metadata">

**Author:** ![kwangxi](https://avatars.discourse-cdn.com/v4/letter/k/ccd318/32.png) [@kwangxi](https://community.dynatrace.com/u/kwangxi)\
**Post date:** [April 13, 2023, 2:53pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/3 "2023-04-13T14:53:14Z")

</div>

Hi,

Thanks for the prefix, it would definitely be the solution for it.

Btw for this, it would only get the 1st array which is [0], is there any way to populate in for the rest of the arrays too?

```auto
| filterOut ...
| parse softwareTechnologies[0], "'type:' WORD:type ',edition:' WORD:edition ',version:' WORD:version"

```

 ![kwangxi_0-1681397460395.png](https://global.discourse-cdn.com/dynatrace/original/2X/8/8cbaec5396d53f615a8aa01dedcad183cdc4fadd.png)

---

<div class="post-metadata">

**Author:** ![educampver](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/educampver/32/29917_2.png) [@educampver](https://community.dynatrace.com/u/educampver)\
**Post date:** [April 13, 2023, 3:45pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/4 "2023-04-13T15:45:07Z")

</div>

You’re right! My bad. What you need is a bit more complex and I’m not sure the format you need it in, here’s what I came up with:

```markup
| filterOut ...
| fieldsAdd technologiesString = toString(softwareTechnologies)
| parse technologiesString, "'[' ARRAY{'type:' WORD*:type (',edition:' LD ',version:' LD (', ' | ']'))}{1,}:types"
| parse technologiesString, "'[' ARRAY{'type:' LD ',edition:' WORD*:edition (',version:' LD (', ' | ']'))}{1,}:editions"
| parse technologiesString, "'[' ARRAY{'type:' LD ',edition:' LD ',version:' LD*:version (', ' | ']')}{1,}:versions"

```

This way, you’ll have 3 arrays containing the types, editions, and versions. Additionally, if you need to remove the duplicates, you can use:

```markup
| fieldsAdd types = arrayDistinct(types), editions = arrayDistinct(editions), versions = arrayDistinct(versions)

```

Hope it works as expected this time.

---

<div class="post-metadata">

**Author:** ![kwangxi](https://avatars.discourse-cdn.com/v4/letter/k/ccd318/32.png) [@kwangxi](https://community.dynatrace.com/u/kwangxi)\
**Post date:** [April 13, 2023, 5:03pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/5 "2023-04-13T17:03:31Z")

</div>

Hi,

Previously, I was summarizing the monitoring mode to be in this way, so it can be captured and read by the DT chart.

I intend to achieve the software technologies in this way, may I ask is there any way to achieve this?  
Let’s say, for all 3 hosts, I found there are 3 Java so it would be:

```auto
{
"category": "JAVA",
"value": "3"
},
{
"category": "APACHE_TOMCAT",
"value": "1"
},
.....

```

 ![kwangxi_0-1681405285404.png](https://global.discourse-cdn.com/dynatrace/original/3X/f/3/f3a037a4614e3380bf2e48dce33573f8bd8ba8e3.png)

---

<div class="post-metadata">

**Author:** ![educampver](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/educampver/32/29917_2.png) [@educampver](https://community.dynatrace.com/u/educampver)\
**Post date:** [April 17, 2023, 12:13pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/6 "2023-04-17T12:13:16Z")

</div>

Unfortunately, I don’t see how to achieve what you want in DQL. There’s a big difference with the monitoring mode, and is that the monitoring mode is only one value per host, while the technologies are an array. I recommend doing a simple DQL query to get the raw data and parse and aggregate the technologies in code. Let me know if I can help with anything else.

---

<div class="post-metadata">

**Author:** ![peter\_zahrer](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/peter_zahrer/32/38745_2.png) [@peter\_zahrer](https://community.dynatrace.com/u/peter_zahrer)\
**Post date:** [October 30, 2023, 3:27pm UTC](https://community.dynatrace.com/t/extracting-details-from-lookup-dql/39287/7 "2023-10-30T15:27:19Z")

</div>

This is indeed tough since the data is so deeply nested. While stating that, on the product side we can represent the software technologies data in an easier to use way, at least we can extract the data with DQL.

```java
fetch dt.entity.process_group_instance 
| fieldsadd softwareTechnologies
| expand softwareTechnologies
| parse softwareTechnologies, "kvp{[a-z]*:key ':' LD:value (EOS|',')}:techs"
| fieldsflatten techs

```

What should also be stated that this is in many cases not necessary as we enrich that information e.g. on logs

So you can directly group your data by technology:

```java
fetch logs
| fields timestamp, process.technology
| expand process.technology

```
