How to Compare two mv fields and audit individual results?

[dural_yyz]

I'm looking specifically at the index for _configtracker to audit changes to serverclass.conf file.  Because the nature of the <filtertype>.n = <value> the behavior is one action to remove all values, then a second action to rewrite all the values in lexi order.  This is making auditing add/removals/static very difficult.

I have managed to transact the events so I can compare old values to new values.  I struggle with how to compare the results to identify changes when values list is very long.

Current Table Output

Unique Ident	OldValues	NewValues
<transact-x>	A B C D	A C D E

What I'm looking for

Unique Ident	OldValues	NewValue	Audit
<transact-x>	A	A	NoChange
<transact-x>	B		Removed
<transact-x>	C	C	NoChange
<transact-x>	D	D	NoChange
<transact-x>		E	Added

Assumptions

1) stats values(field): I don't believe any of my samples cross over 10,000 which I believe is default limits for values field

2) values function will lexi order all values regardless of original order in raw data feed

[ITWhisperer · Accepted solution]

It is a bit convoluted because you need to create additional events to cover all comparisons.

| mvexpand OldValues
| eval Audit=if(isnotnull(mvfind(NewValue,OldValues)),"NoChange","Removed")
| eventstats values(OldValues) as allOldValues by Unique_Ident
| mvexpand NewValue
| eval NewAudit=if(isnotnull(mvfind(allOldValues,NewValue)),null(),"Added")
| eval OldValues=if(NewAudit="Added",null(),OldValues)
| eval NewValue=if(Audit="NoChange" AND isnotnull(OldValues),OldValues,if(Audit="Removed" AND isnotnull(OldValues),null(),NewValue))
| eval Audit=coalesce(NewAudit,Audit)
| fields Unique_Ident OldValues NewValue Audit
| fillnull value="" OldValues NewValue
| dedup Unique_Ident OldValues NewValue Audit

[dural_yyz]

This is exactly what I needed.

My unique_ident was actually a collection of fields.  I just eval'd them together as a big string with a delim character that I can easily separate out post processing.

One issue that I will not worry too much about but if you have a hint would be good.  The values fields are text and can contain an "*" character.  Even if the value exists on both columns it is registering as "Added" and "Removed".  Because of the nuance I can live with it.  However, if I wanted to move this into a dashboard not all users might understand the mechanics.

Example

Unique	Old	New
transact-x	*_ABCD_*	*_ABCD_*

Results

Unique	Old	New	Audit
transact-x		*_ABCD_*	Added
transact-x	*_ABCD_*		Removed

[ITWhisperer]

The * is being treated as a special character by the mvfind which is confusing the mvfind function and giving you false positives / false negatives. You could try replace() to change them to something else, then change them back afterwards?