How to compare fields "list" and "standard"?

[bosseres]

Hello everyone

In the result of my search I got such results (last command was stats values(list) as list, values(standard) as standard by host 
fields list and standard are multivalues

host	list	standard
	5	1
	1	2
	2	3
	3	4

I need to compare fields "list" and "standard"

make field "result" where will be: lacking records, redundant records and passing records
Lacking is record that present in standard but not in list, redundant is present in list but not in standard, and passing is which is in list and standard is equal.

so for this example must be:

result

Passing: 1 2 3 Lacking: 4 Redundant: 5

[yuanliu]

To address @ITWhisperer's efficiency considerations, here is a literal implementation of your requirements.

``` uses side effect of SPL's liberal equality operator ```
| eval lacking = mvmap(standard, if(standard == list, null(), standard))
| eval redundant = mvmap(list, if(list == standard, null(), list))
| eval passing = mvmap(list, if(list == standard, list, null()))
| eval result = json_object("lacking", lacking, "redundant", redundant, "passing", passing)

Note: Your description of a field named result requires an associative array, or hash representation, that doesn't come native in SPL.  So, you can either use three separate fields as implemented in the first three lines or use a JSON representation which SPL added in 8.0, as created in line 4.

Using your sample data in this emulation,

| makeresults
| fields - _time
| eval list = mvappend("5", "1", "2", "3"), standard = mvappend("1", "2", "3", "4"), host = "hostA"
``` data emulation above ```

the result is

host	lacking	list	passing	redundant	result	standard
hostA	4	5 1 2 3	1 2 3	5	{"lacking":4,"redundant":5,"passing":["1","2","3"]}	1 2 3 4

Again, the use of "result" field is optional in my opinion.

[ITWhisperer]

Assuming host is not null, this is one way of doing it although possibly not the most efficient

| stats values(list) as list values(standard) as standard by host
| mvexpand list
| mvexpand standard
| eval list_in_standard = if(list==standard,list,null())
| stats values(list_in_standard) as list_in_standard values(standard) as standard by host list
| eval list_not_in_standard=if(isnull(list_in_standard),list,null())
| mvexpand standard
| eval standard_in_list = if(list==standard,standard,null())
| stats values(list_in_standard) as list_in_standard values(standard_in_list) as standard_in_list values(list) as list values(list_not_in_standard) as list_not_in_standard by host standard
| eval standard_not_in_list=if(isnull(standard_in_list),standard,null())
| stats values(list) as list values(standard) as standard values(list_in_standard) as list_iin_standard values(standard_not_in_list) as standard_not_in_list values(list_not_in_standard) as list_not_in_standard by host

[bosseres]

thank you!