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")]
or
'<<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
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.
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.