topic Re: Apply variable values in filter with entitySelector in DQL

Apply variable values in filter with entitySelector

elenaperez — Fri, 14 Jul 2023 15:26:48 GMT

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?

Re: Apply variable values in filter with entitySelector

sinisa_zubic — Mon, 17 Jul 2023 07:39:52 GMT

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

Re: Apply variable values in filter with entitySelector

elenaperez — Wed, 19 Jul 2023 10:36:33 GMT

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)))

Re: Apply variable values in filter with entitySelector

sinisa_zubic — Wed, 19 Jul 2023 11:12:48 GMT

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

Re: Apply variable values in filter with entitySelector

dmezynski — Wed, 12 Mar 2025 15:21:53 GMT

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.

Re: Apply variable values in filter with entitySelector

elenaperez — Wed, 12 Mar 2025 15:51:21 GMT

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))

Re: Apply variable values in filter with entitySelector

dmezynski — Wed, 12 Mar 2025 20:09:26 GMT

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?

Re: Apply variable values in filter with entitySelector

dmezynski — Wed, 12 Mar 2025 20:28:56 GMT

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

Re: Apply variable values in filter with entitySelector

mgome — Thu, 27 Mar 2025 18:40:30 GMT

I want to revisit the original question because filtering in the timeseries query is faster than filtering after the query. In order to use a variable as part of an entity selector filter in a timeseries query, you need to use classicEntitySelector, concat and toString.

timeseries cpu=avg(dt.host.cpu.usage), filter:in(dt.entity.host, classicEntitySelector(concat("type(host),tag(Environment:", toString($Environment), ")") ) ), by:{dt.entity.host} | sort arrayAvg(cpu) desc | limit 20

Breaking down the filter, there's an embedded concat in the classicEntitySelector call with three arguments. The first argument is the beginning of the entity selector in quotes. The second argument is the variable converted to a string. The last argument is a quoted close parentheses to end the entity selector.

Anything you can to do reduce the amount of data returned in the timeseries query will make tiles in dashboards load faster.

Re: Apply variable values in filter with entitySelector

elenaperez — Fri, 28 Mar 2025 08:12:53 GMT

That makes sense! Thank you as well

Re: Apply variable values in filter with entitySelector

dmezynski — Fri, 28 Mar 2025 13:27:39 GMT

We flipped our methodology on this recently.
We created a hidden variable ($app_servers) to create a list of hosts:
Note: that hidden variable needs to be multiselect and default to *

fetch dt.entity.host |fieldsAdd tags |filter in(concat("client:",$Client),tags) and in(concat("environment_type:",$Environment),tags) |fields id

Then our tiles became much simpler by providing a host list.

timeseries cpu=avg(dt.host.cpu.usage), by:{dt.entity.host} |filter in({$app_servers}, dt.entity.host) |fieldsadd hostname=entityName(dt.entity.host)