topic Show “null” or replace with a value in a field in Dashboarding

Show “null” or replace with a value in a field

Red — Wed, 05 Jun 2024 06:20:13 GMT

Hello community,

I am in the process of discovering DQL but I am encountering a rather serious problem: I have several filters for a table in my dashboard which are populated with the values of the table. This part works fine but one of my columns may have "null" values. And in my filter, this is not seen, and biases my results.

In the screen below, you can see that I use several filters, with success, but the "Management_Zones" filter does not show all the possibilities because of the "nulls" that may be found there. I did the calculation with and without filter and the difference is significant :

My question is the following: how can we force "nulls" to be named in a specific column? I looked for several solutions but all the ones I tested didn't work.

Sincerely,

Red

Re: Show “null” or replace with a value in a field

joeistyping — Tue, 04 Jun 2024 21:18:14 GMT

Hey Red! (I'm team Poke Yellow way back in the day)

My recommendation to force a value is by using expressions to replace null with "some value", in the below example, this DQL expressions updates the field "managementZones" with the value "No_MZ", if the current value is null.

This will need to be updated within your variable definitions within your dashboard and within your tiles.

| fieldsAdd managementZones = if(isNull(managementZones), "No_MZ", else:managementZones)

Full example:

Variable Definitions:

fetch dt.entity.host | fieldsAdd monitoringMode, osType, osVersion, hostGroupName, managementZones | summarize takeLast(id), by: {managementZones} | fields values = managementZones | fieldsAdd values = if(isNull(values), "No_MZ", else:values)

Tiles:

fetch dt.entity.host | fieldsAdd monitoringMode, osType, osVersion, hostGroupName, managementZones | fieldsAdd managementZones = if(isNull(managementZones), "No_MZ", else:managementZones) | filter in(managementZones,$ManagementZone) | summarize count = count()

Result when we select all MZ's and also select "No_MZs":

Cheers,

Re: Show “null” or replace with a value in a field

Red — Fri, 07 Jun 2024 12:26:13 GMT

Hi joeistyping (good choice on the Yellow team ^^)

I just came back from leave and I tested your method, it works perfectly!

Thank you very much for your help !

Red