Filtering entity based on two tags variable in dashboard

[susmita_k]

I am trying to get a list of Oracle db instances based on two tags ( Environment and Department, those are also variable). The list will be also a variable in the dashboard. I am trying like this, but its not working for me.

Any help is really appreciated.

fetch `dt.entity.sql:com_dynatrace_extension_sql-oracle_instance`
| lookup [fetch `dt.entity.sql:com_dynatrace_extension_sql-oracle_instance`], sourceField:`dt.entity.sql:com_dynatrace_extension_sql-oracle_instance`, lookupField:id, fields:{tags}
| filter isNotNull(tags)
| expand tags
| parse tags, """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)"""
| fieldsAdd tag = replaceString(tag,"""\:""", ":"), value = replaceString(value, """\:""", ":")
| fieldsAdd Dept = if(tag == "Department", value)
| fieldsAdd Env = if(tag == "Environment", value)
| summarize {entity.name = takeAny(entity.name),
Dept=arrayMin(collectArray(Dept)),
Env = arrayMin(collectArray(Env))}
, by:{`dt.entity.sql:com_dynatrace_extension_sql-oracle_instance`}
| filter in(Dept, array($Department)) and
in(Env, array($Environment))

[Radu]

I would do it like this:

fetch `dt.entity.sql:com_dynatrace_extension_sql-oracle_instance`
// Add the tags as a concatenated string
| fieldsAdd tags = toString(tags)
// Keep only entities where both tags exist
| filter contains(tags, "Department:") and contains(tags, "Environment:")
// Parse the values into fields
| parse tags, "LD '\"Department:' LD:DepartmentTag '\"' LD"
| parse tags, "LD '\"Environment:' LD:EnvironmentTag '\"' LD"
// Filter based on dashboard's variables
| filter in(DepartmentTag, array($Department)) and in(EnvironmentTag, array($Environment))

[krzysztof_hoja · Accepted solution]

With iterative expressions is not necessary to go through expand and summarize sequence as well as converting whole tags array to string for parsing.

I would try this:

fetch `dt.entity.sql:com_dynatrace_extension_sql-oracle_instance`
| fieldsAdd ptags = parse(tags[], """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)""")
| filter in(arrayRemoveNulls(iCollectArray(if(ptags[][tag]=="Department",ptags[][value]))), array($Department))
  and in(arrayRemoveNulls(iCollectArray(if(ptags[][tag]=="Environment",ptags[][value]))), array($Environment))