Splunk Search

count events where the same value exists in two different fields

mmdacutanan
Explorer

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.

Tags (1)
0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...