Apply variable values in filter with entitySelector

[elenaperez]

I have the following query on a dashboard:

timeseries cpu=avg(dt.host.cpu.usage), filter:in(dt.entity.host,entitySelector("type(host),tag(Environment:DEV)")),by:dt.entity.host
| sort arrayAvg(cpu), direction:"descending"
| limit 20

And I have a variable called Environment with the values DEV, SANDBOX and PROD. I wold like to filter the tile based on the variable but substituting DEV by $Environment doesn't work.

Any idea on how could I create the query?

[sinisa_zubic · Accepted solution]

Hi @elenaperez 

As you might have noticed from the info message in your environment, the entitySelector will be soon depreciated for usage in DQL. Instead you can use just plain DQL. I guess this example should work for you

timeseries cpu=avg(dt.host.cpu.usage), by:{dt.entity.host}
| lookup [fetch dt.entity.host], sourceField:dt.entity.host, lookupField:id, fields:{tags}
| expand tags
| parse tags, """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)"""
| fieldsAdd tag = replaceString(tag,"""\:""", ":"), value = replaceString(value, """\:""", ":")
| filter toString(tag) == "Environment" and toString(value) == $environment
| sort arrayAvg(cpu), direction:"descending"
| limit 20

Best,
Sini

[elenaperez]

What if I also have another tag called Business_Application that works the same way as Environment and I want to filter on the 2 values? I tried this but it doesn't work (doesn't show any data but it should)

| filter (toString(tag) == "Business Application" and in(toString(value), array($Business_Application))) and (toString(tag) == "Environment" and in(toString(value), array($Environment)))

[sinisa_zubic · Accepted solution]

because for every tag you have now one record. This is caused by the expand command.

So you need to summarize again the data end extract the tags on which you want to filter. Something like this

timeseries cpu=avg(dt.host.cpu.usage), by:{dt.entity.host}
| lookup [fetch dt.entity.host], sourceField:dt.entity.host, 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 businessApp = if(tag == "Business Application", value)
| fieldsAdd environment = if(tag == "Environment", value)
| summarize {cpu = takeAny(cpu), 
  businessApp=arrayMin(collectArray(businessApp)),
  environment = arrayMin(collectArray(environment))}
  , by:{dt.entity.host}
| filter in(businessApp, array($Business_Application)) and 
  in(environment, arary($Environment))

Best,
Sini