count events where the same value exists in two different fields

[mmdacutanan]

I need to write a query that counts events when 3 criteria are met. First two are easy, they events have to have the same "Timestamp" and "clientdAddr". The 3rd part is where I am having issue: the "to" and "from" fields need to have the same value.

Here is my query so far:

index=voip method=BYE eh_event=SIP_REQUEST  method=BYE NOT ( to="<sip:844*" OR to="<sip:855*" OR to="<sip:866*" OR to="<sip:877*" OR to="<sip:888*" OR to="<sip:800*" OR to="<sip:+1844*" OR to="<sip:+1855*" OR to="<sip:+1866*" OR to="<sip:+1877*" OR to="<sip:+1888*" OR to="<sip:+1800*" ) OR (index=extrahop_voip method=BYE eh_event=SIP_REQUEST NOT ( from="<sip:+1844*" OR from="<sip:+1855*" OR from="<sip:+1866*" OR from="<sip:+1877*" OR from="<sip:+1888*" OR from="<sip:+1800*" OR from="<sip:844*" OR from="<sip:855*" OR from="<sip:866*" OR from="<sip:877*" OR from="<sip:888*" OR from="<sip:800*" ) )
| rex field=clientAddr "99\.888\.7\.(?<range>\d{1,3})"
| where range >=60 and range <=80
| rex field=to  "<sip:(?:\+\d)?(?<to>\d+)@.+>"
| rex field=from  "<sip:(?:\+\d)?(?<from>\d+)@.+>"
| fillnull value=NULL
| search NOT (from=NULL) 
| dedup callId
| table Timestamp clientAddr from to

And the sample output would look something like this:
Timestamp clientAddr from to
1551493234 99.888.7.66 5556661234 8004446789
1551493509 99.888.7.66 888234567 5556661234

1551493509 99.888.7.66 5556661234 8004446789
1551486810 99.888.7.80 5556661234 8004446789

As you can see rows 2 & 3 meet the 3 criteria: same Timestamp, clientAddr and the same value in the from field (5556661234 ) in one row exists in the to field (5556661234) of the other row. Doesn't matter that from value "888234567 " doesn't match to value "8004446789" as long as there is at least one pair that matches.

Please help! I am thinking I might need to use multi-valued field like stats values() for this but not sure how to implement exactly.

[niketn]

@mmdacutanan try the following run anywhere example, based on sample data provided in the question, the following query generates the same using commands till from | makeresults and | table Timestamp clientAddr from to. The command after that are used to generate results as per your requirement:

| makeresults
| eval data="1551493234 99.888.7.66 5556661234 8004446789;1551493509 99.888.7.66 888234567 5556661234;1551493509 99.888.7.66 5556661234 8004446789;1551486810 99.888.7.80 5556661234 8004446789"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval Timestamp=mvindex(data,0),clientAddr=mvindex(data,1),from=mvindex(data,2),to=mvindex(data,3)
| table Timestamp clientAddr from to
| eval pipe=mvappend(from,to)
| stats count list(from) as from  list(to) as to  by Timestamp clientAddr pipe
| search count>1
| eval pipe=mvzip(from,to)
| fields - from to count
| mvexpand pipe
| makemv pipe delim=","
| eval from=mvindex(pipe,0),to=mvindex(pipe,1)
| fields - pipe