how to remove dynamic column

Question

I have two search using xyseries,so field name of these two search are dynamic and some of these field names are different. now I want to combine these tow search ,and remove the different fields

I use |join -type outer to combine these two search table , but I don't know how to remove the different fields or keep same fields with search 1 in search 2

search 1:

fields aaaa aaa aaAa sjdk

---

count1 30 20 10 63

search 2:

fields aaaa aaa aaAa sjdk jdiw dwdd

---

count2 60 10 10 63 43 343

and now my table is like: (using |join type outer)

fields aaaa aaa aaAa sjdk jdiw dwdd

---

count1 30 20 10 63 count2 60 10 10 63

Is there any method to remove empty column? expect:

fields aaaa aaa aaAa sjdk

---

count1 30 20 10 63 count2 60 10 10 63

I'm trying use |foreach and |fields - , but it didn't work because I can't get the field name correctly

Thanks

and I also want to know is there any way to change color by value with it's full dashboard , because fields name are dynamic and I don't want to manual

Answer 1

If I understand correctly, you have some NULL fields after your join

If that is correct, the following should get you at least close:

<search that gets through the `join`>
| stats values(*) as * by <unique fields you know exist>
| fillnull value="-"

stats values(...) will only include fields for which there is at least one value in the previous events

stats values(...) will populate a NULL value for events that did not have the field in question - iff it existed with a value in ay event in the set

So follow it up with fillnull so you know where the fields existed (vs did not exist)