The solution becomes more obvious if I restate the problem like this: In addition to colors, you must have another field with four distinct values. Let's call the additional field group, and give th...
See more...
The solution becomes more obvious if I restate the problem like this: In addition to colors, you must have another field with four distinct values. Let's call the additional field group, and give them values "a", "b", "c", and "d". colors group blue a blue a red a yellow b red b blue c red c blue c red d red d green d green d When data structure is clear, what you are asking is to Find values of colors that appear more than once with each group value. Count how many distinct values of group for each of duplicated values of colors. Hence, | stats count by colors group
| where count > 1
| stats dc(group) as duplicate_count by colors Here is a data emulation you can play with and compare with real data | makeresults format=csv data="colors,group
blue,a
blue,a
red,a
yellow,b
red,b
blue,c
red,c
blue,c
red,d
red,d
green,d
green,d"
``` data emulation above ``` String the two together, you get colors duplicate_count blue 2 green 1 red 1