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

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

donm — Tue, 21 Jan 2025 21:22:25 GMT

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?

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

yuanliu — Wed, 22 Jan 2025 01:02:13 GMT

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.

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

donm — Wed, 22 Jan 2025 14:14:05 GMT

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.

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

PickleRick — Wed, 22 Jan 2025 14:53:28 GMT

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

<your_search>
| transpose 0 include_empty=f