How to find events with duplicate field

[barrysvee]

Our application had a defect in a logging interceptor that led to a field being duplicated in an event but where both values didn't match. I am trying to verify the fix but am having difficulty in coming up with a query.

How can I find all events where a field is listed twice in the same event but where both values are not equal?

[woodcock]

Like this:

... | eventstats values(BrokenFIeld) dc(BrokenField) AS numDistinctValues count(BrokenField) AS numValues by _serial | where numValues>1 AND numDistinctValues>1

[barrysvee]

Thank you, that does produce a list of events that shows both BrokenField fields but does not filter to just the ones where both are unequal. If I add a known BrokenField value to the query I do see just the ones with that known value plus the unequal values. I'm losing the events with unequal values in the sea of events where they are correctly equal.

[woodcock]

Are you sure? The search is pretty sold. The numValues>1 should drop out any events that do not have a multi-valued "BrokenField". The numDistinctValues>1 should drop out any events that do not have more than one value (e.g. that have the same value) for "BrokenField".

In any case, this simpler approach should also work for a 2-value-only case:

... | eval BV1=mvindex(BrokenValue,0) | eval BV2=mvindex(BrokenValue,1) | where isnotnull(BV1) AND isnotnull(BV2) AND BV1!=BV2

[woodcock]

Did this work for you?

[barrysvee]

In theory it should. I finally got a response back from our infrastructure team stating that either they need to make BrokenValue a multivalue field or I need to use the rex command as part of my query. I'll attempt the latter some time today. Thank you again for your suggestions!