Is there a way to compare the values in two multivalues fields irrepsective of the positions of the values that lie withing?
If not is there a way to sort the values within a multivalue field?
Currently I am using a simple field!=field expression however since I NOT am interested in the differences between the order of the values within the field this does not suffice.
eval Status = if(DynamicValues != StaticValues, "NOT OK", "OK")
The end result being, from the image above I would like to be able to resturn a Status of OK for the 1st and 5th rows. However since the actual values are the same but the ordering is not a simple != expression does not suffice any ideas?
It seems like stats' "values" function sorts the values alphabetically
... | streamstats count as id | stats values(DynamicalValues) as dyn values(StaticValues) as st by id | eval Status = if(dyn != st, "NOT OK", "OK")
Here I'm using streamstats just to assign each event a unique ID.
Great thanks just tried this out, have never used streamstats before worked a treat, since were not interested in the _raw values of the fields thats ok, also they always be reconstructed
There may be a better answer out there, but here's something to get you going. You should be able to do your search like this:
... | makemv DynamicValues | mvexpand DynamicValues | where match(StaticValues, DynamicValues)
This should yield a separate event for each value of DynamicValues for every event. The "match" function will search a field for a RegEx, but in this case, we're searching one multivalued field (StaticValues) for the the individual entities of DynamicValues. Be sure to check the docs on makemv, so you get your field splits correct.
If you want to add the "OK" and "NOT OK" text to the list and return the events to their original format, you could do the search like this:
... | makemv DynamicValues | mvexpand DynamicValues | eval Status = if(match(StaticValues, DynamicValues), "OK", "NOT OK") | mvcombine DynamicValues
I actually tried something simalar, attemped to break down the values using mvexpand, then sort, then use mvcombin to reconstruct and then compare. Didnt occur to me to use match once the values had been expanded 🙂
I ran across the same challenge when trying to compare a list of diagnosis codes on a claim to a static list of diagnosis codes in my search. I was looking for an mvintersect() or similar command... I didn't like the above idea of mvexpand creating more results given the fact that the result set was already large, so I came up with the following:
index=claims | eval diags=mvdedup(mvappend(diag_1_cd, diag_2_cd, diag_3_cd, diag_4_cd)) | eval search_diags=mvdedup(mvappend("a","b")) | eval separateDiagCount = mvcount(diags) + mvcount(search_diags) | eval joinedDiagCount = mvcount(mvdedup(mvappend(diags,search_diags))) | eval hasOverlap = if(separateDiagCount != joinedDiagCount, 1,0)
Yikes! Not sure if it performs, but it does what I want it to do. Feel free to consolidate to your liking... I just wanted to break down the solution to discrete steps.