topic Re: Compare values from same field in different events to all other events in Getting Data In

Compare values from same field in different events to all other events?

sg2 — Thu, 15 Sep 2022 14:44:33 GMT

So I'm trying to get all events where val1+val2 are also in another event from the table. In the example below, I would need as output row 0 and row 1, because both val1 and val2 match. Row 3 and 4 match on val1 but not on val2, and row 1 and 2 match on val2 but not on val1, so those events should get excluded. (Also I need time column to stay as I need to do some other operations with it)

row#	time	val1	val2
0	YYYY-MM-DD	A	X
1	YYYY-MM-DD	A	X
2	YYYY-MM-DD	B	X
3	YYYY-MM-DD	C	Y
4	YYYY-MM-DD	C	X
5	YYYY-MM-DD	A	Z

To solve this I've been trying:

| foreach val1 [eval test=if(val1+val2=val1+val2, "same", "not")]

'<<FIELD>>' = '<<FIELD>>'

But I end up getting with either "not" in all cases, or "same" in others even tho both values are not actually the same

Re: Compare values from same field in different events to all other events

richgalloway — Wed, 14 Sep 2022 21:06:32 GMT

See if this helps

| eventstats count by val1 val2 | where count > 1 | fields - count

The eventstats command counts the number of rows with the same values for val1 and val2. Then we eliminate the rows with a count of 1 and discard the count field.

Re: Compare values from same field in different events to all other events

bowesmana — Wed, 14 Sep 2022 22:44:49 GMT

FYI: foreach command only looks at fields in the SAME event, not across events. As @richgalloway says, use eventstats to calculate count as needed - that command will leave the original events untouched, so you have access to all fields still.