topic DQL nice to know: Using a filter, but when not in filter then show record with empty attribute in DQL

DQL nice to know: Using a filter, but when not in filter then show record with empty attribute

henk_stobbe — Thu, 02 Jan 2025 14:52:16 GMT

Hello,

Maybe this is handy for someone. Suppose you want to use a filter, but you also want to show records not within the filter with an empty attribute

You need to fetch the data twice (-; The "data" example:

data record(rec=1,data =1),
record(rec=2,data =2) ,
record(rec=3,data =1),
record(rec=4,data =3)
| filter (data == 1)
| fieldsadd newdata = data
| append [
data record(rec=1,data =1),
record(rec=2,data =2) ,
record(rec=3,data =1),
record(rec=4,data =3)]
| dedup rec, sort:(newdata) // Use the "null" values last!
| fieldsAdd data = if(isNull(newdata), "", else:data) // Replace ugly null by ""
|fieldsRemove newdata // Remove the temp column

Result:

Any comments, issue's or has a better way?

KR Henk

Re: DQL nice to know: Using a filter, but when not in filter then show record with empty attribute

Tiit_Hallas — Thu, 02 Jan 2025 15:40:04 GMT

I might be missing something here, but why not just use the IF clause without the append?

data record(rec=1,data=1), record(rec=2,data=2), record(rec=3,data=1), record(rec=4,data=3) | fieldsAdd data = if(data == 1, data, else: "")

An example of the result can be seen in the Security Investigator in Dynatrace Playground: https://wkf10640.apps.dynatrace.com/ui/apps/dynatrace.security.investigator/share/2e02f2c7-8781-4e95-bd73-d5c792fc9281

Re: DQL nice to know: Using a filter, but when not in filter then show record with empty attribute

henk_stobbe — Thu, 02 Jan 2025 15:55:37 GMT

Hello,

Thanks for your reply!

You are not missing anything, from where I am sitting you are brilliant!

KR Henk