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

[dmezynski]

I'm doing this same thing and am having issues with displaying as a time series on a dashboard.

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 client = if(tag == "client", value)
| fieldsAdd environment_type = if(tag == "environment_type", value)
| fieldsAdd function = if(tag == "function", value)
| summarize {cpu = takeAny(cpu), 
  client=arrayMin(collectArray(client)),
  environment_type = arrayMin(collectArray(environment_type)),
  function = arrayMin(collectArray(function))}
  , by:{dt.entity.host}
| filter in(client, array($Client)) and 
  in(environment_type, array($Environment))

The data set comes out like this (with the tag values not shown):

The dashboard is telling my the data is not suitable for a graph:

Any help would be appreciated.

[elenaperez]

Try adding timeframe and interval in the by. You are missing that information when you do the summarize:

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 client = if(tag == "client", value)
| fieldsAdd environment_type = if(tag == "environment_type", value)
| fieldsAdd function = if(tag == "function", value)
| summarize {cpu = takeAny(cpu), 
  client=arrayMin(collectArray(client)),
  environment_type = arrayMin(collectArray(environment_type)),
  function = arrayMin(collectArray(function))}
  , by:{dt.entity.host, timeframe, interval}
| filter in(client, array($Client)) and 
  in(environment_type, array($Environment))

[dmezynski]

Thank you very much!!!! I am just getting into gen3 and DQL. Do you know what modification would be needed to report by entity name instead of the id?

[dmezynski]

Got it.

timeseries cpu=avg(dt.host.cpu.usage), by:{dt.entity.host}
| lookup [fetch dt.entity.host], sourceField:dt.entity.host, lookupField:id, fields:{tags, entity.name}
| filter isNotNull(tags)| expand tags
| parse tags, """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)"""
| fieldsAdd tag = replaceString(tag,"""\:""", ":"), value = replaceString(value, """\:""", ":")
| fieldsAdd client = if(tag == "client", value)
| fieldsAdd environment_type = if(tag == "environment_type", value)
| fieldsAdd function = if(tag == "server_function", value)
| summarize {cpu = takeAny(cpu), 
  client=arrayMin(collectArray(client)),
  environment_type = arrayMin(collectArray(environment_type)),
  function = arrayMin(collectArray(function))}
  , by:{entity.name, timeframe, interval}
| filter in(client, array($Client)) and 
  in(environment_type, array($Environment))
| fieldsRemove client
|fieldsREmove environment_type
|fieldsRemove function