How do I Exclude null/empty fields from a lookup result where I should get a single row back

[donm]

We have a lookup that has all kinds of domain (DNS) information in it with about  60 fields like create date, ASN, name server IP,  MX IP, many of which are usually populated. But there are several fields which have no data - 10 to 20 on any given search (assuming that they are 'null'). The empty fields are likely to vary on each search. In other words some domains will have an MX record, some will not, but if they are in this lookup, they will always have a create-date. 

I am presenting this data on a domain lookup dashboard, using "|transpose" so that you have a table with the field name and value on a dashboard. I would like to just show a field and a value where this is returned data and filter out or not show a field which is null. Is there a way to do this?

[yuanliu]

Not sure if I fully understand the requirement.  But in general, you can assign a non-null string to those fields.  For example,

| eval MX = coalesce(MX, "MX is null")

The issue, I suspect, is when you transpose, all those values representing null will collapse and skew format.  Is this the problem?  If so,  you can force these values to be different, e.g.,

| eval MX = coalesce(MX, "MX is null for " . FQDN)

Hope. this helps.

[donm]

Hi - thanks for the idea, Sure, I could build that into the search, true.

On the output dashboard what you end up with is "1 2 3 next..." on the bottom right, so you need to click through to see all possible values from the lookup that we have on hand. Often enugh there are 4-6 rows of empty fields in the result set, because the data is transpose'd. I'm looking to make the returned data more compact, if you will. 

[PickleRick · Accepted solution]

If I understand you correctly you want to remove all-empty columns from your original data, right?

<your_search>
| transpose 0 include_empty=f