multiple joins and subsearch question

[mmdacutanan]

I have got 3 queries that I need to join together.

First query has a subsearch. I used a subsearch because I need to find the records that has a fractionLost > 128 for eh_event=RTCP_MESSAGE. From that subsearch I collected the callId. I then use the callId to display the SIP records with the same callId AND has a method of BYE. This query works but not the fastest. Don't know of any other way to do this other than a subsearch.

index=ehop sourcetype=VOIP eh_event=SIP_RESPONSE method=BYE callId=* [ search index=ehop sourcetype=VOIP eh_event=RTCP_MESSAGE fractionLost > 128
| eval {eh_event}-Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| rex field=senderAddr "xx\.(?<range>\d{1,3})\.\d{1,3}\.\d{1,3}"
| where range >=xx and range <=xxx
| table callId]
| eval SIPTime=strftime(_time, "%Y-%m-%d %H:%M:%S") 
| table SIPTime clientAddr serverAddr callId

Now the query above only gives me the columns for the main search; I want to join the columns of the subsearch as well. So I did a join which basically looks like the subsearch above but with all the columns. The query seems to work, albeit slow:

index=ehop sourcetype=VOIP eh_event=SIP_RESPONSE method=BYE callId=* latest=-3h@h [ search index=ehop sourcetype=VOIP eh_event=RTCP_MESSAGE fractionLost > 128
    | eval {eh_event}-Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
    | rex field=senderAddr "xx\.(?<range>\d{1,3})\.\d{1,3}\.\d{1,3}"
    | where range >=xxx and range <=xxx
    | table callId]
| eval SIPTime=strftime(_time, "%Y-%m-%d %H:%M:%S") 
| table SIPTime clientAddr serverAddr callId
| join type=left callId  [ search index=ehop sourcetype=VOIP eh_event=RTCP_MESSAGE fractionLost > 128 
    | eval {eh_event}-Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
    | rex field=senderAddr "10\.(?<range>\d{1,3})\.\d{1,3}\.\d{1,3}"
    | where range >=xxx and range <=xxx
    | table *
    | fields - burstDensity burstDuration change_type clientName date* _raw host index punct source sourcetype splunk* eh_* time* tag* eventtype unix_* -callId]

   So now the query above gives me all the columns for both eh_event=SIP_REQUEST and eh_event=RTCP_MESSAGE. BUT I have one more requirement which is to join eh_event=RTP_Tick. Again, I can use the callId to join RTCP_Tick to the rest. This is where I run into issues. I can join RTP_Tick with RTCP_Message just fine (query below) by employing the main search with subsearch technique. 

BUT how do i join the query below to the query above so that I displaying all SIP_REQUEST, RTP_Tick and RTCP_Message that has the same callId???

index=ehop sourcetype=VOIP eh_event=RTP_Tick callId=* [ search index=ehop sourcetype=VOIP eh_event=RTCP_MESSAGE fractionLost > 128 callId=* 
| eval {eh_event}-Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| rex field=senderAddr "xx\.(?<range>\d{1,3})\.\d{1,3}\.\d{1,3}"
| where range >=xxx and range <=xxxx
| table callId]
| eval {eh_event}-Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| table *
| fields - clientName date* _raw host index punct source sourcetype splunk* eh_* time* tag* eventtype unix_* -callId _time version

I tried to do a second join but that didn't work. I only get SIP_Request and RTP_Message events, nothing shows up for RTP_Tick. ANy suggestion on how I can optimize my queries, please let me know as well. I know that join/append/subsearches should be last resort but I couldn't see any other way.

PLease help and thanks in advance!!

[MuS]

Hi mmdacutanan,

My advice: forget join for reasons!

Take all you searches and combine to one single base search like :

 index=ehop sourcetype=VOIP eh_event=RTP_Tick callId=* ( sourcetype=VOIP eh_event=RTCP_MESSAGE fractionLost > 128 callId=* ) ( eh_event=SIP_RESPONSE method=BYE callId=*)

and do all needed eval or other SPL processing after that. Finally use a stats to get the correlation you need.

See some examples to use stats instead join here https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

Hope this helps ...

cheers, MuS

[mmdacutanan]

Hi MuS,

Thanks for the tip. I have seen that link about that subsearches and I actually referred to it before several times before=) But I am still somewhat stuck on how to convert my query using subsearch/join (which is actually useless now because I am hitting some splunk limit so I am not even getting full data for one day).

Below is my query so far. I am able to see the 4 columns that I need from eh_event=SIP_RESPONSE and all the RTCP_MESSAGE columns (minus the ones I explicitly dropped). But my data is now just one row with multiple columns instead of being broken down into multiple rows. Do I need to do an mvexpand on each field? ProbIem is I have at least 40 columns from RTCP_MESSAGE alone.

index=ehop_voip sourcetype=VOIP (eh_event=RTCP_MESSAGE fractionLost>128 callId=*) OR (eh_event=SIP_RESPONSE method=BYE callId=*)
| eval {eh_event}-Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| foreach * [eval "{eh_event}_<<MATCHSTR>>"=<<MATCHSTR>>]
| rex field=senderAddr "xx\.(?<range>\d{1,3})\.\d{1,3}\.\d{1,3}" 
| eval isCC=if(cidrmatch("xx.xx.0.0/16" ,senderAddr),1,0)
| eval isCC2=if(cidrmatch("xx.xx.0.0/16" ,senderAddr),1,0)
| fields RTCP* isCC isCC2 SIP_RESPONSE-Time SIP_RESPONSE_clientAddr  SIP_RESPONSE_serverAddr SIP_RESPONSE_callId
| fields - RTCP_MESSAGE_date* RTCP_MESSAGE_clientName RTCP_MESSAGE_punct RTCP_MESSAGE_source* RTCP_MESSAGE_eh_* RTCP_MESSAGE_eventtype* RTCP_MESSAGE_unix* RTCP_MESSAGE_cName RTCP_MESSAGE_host RTCP_MESSAGE_index _raw _time RTCP_MESSAGE_splunk*
| stats values(*) as *

I modified my "stats values() as *" to "stats values() as * by RTCP_MESSAGE_callId SIP_RESPONSE_callId" but that didn't work. So I tried renaming both RTCP_MESSAGE_callId and SIP_RESPONSE_callId to just "callId" then do the "stats values(*) as * by callId" but I got nothing either.

What am I doing wrong here? Please help!

[MuS]

Sounds like you are missing some unique identifier that is common to all events. If you don't have one you can create one like this:

 | eval joiner=case(isnotnull(field1), field1, isnotnull(field2), field2, 1=1, "unknown")

once you have done this you can use a final stats values(*) AS * by joiner for example.

cheers, MuS