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!! ... View more

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. ... View more

I have the query that gives me the results I need. I just wanted to ask the gurus out here to look at my SPL and if there is a more efficient way to do it then I'd love to hear it. 😃 Thanks in advance! Here's the SPL: index=abc (sourcetype=abc_MainReportLog "Entered Phone Number" Phone!=1234567890) OR ( sourcetype=abc_core_MainReportLog "\|RemoteApplicationData\|" VH_ICMUUI_CALLVARIABLE3!=1234567890 CV7=*) | rename CALLVARIABLE3 as Phone | bucket _time span=1m | stats dc(CallID) as Count by _time Phone CV7 | where Count >=2 | timechart span=1h sum(Count) as Total | fillnull value=0 ... View more

I need to run a report that gives me phone numbers that appeared >=2 within the same minute and with the corresponding CallID and then I need to do an inner join to include another data called CV7. I was able to accomplish that with this query: index=ABC sourcetype=ABC_MainReportLog "Entered Phone Number" Phone!=1234567890 | dedup CallID | table CallID Phone _time | join type=inner CallID [ search index=ABC sourcetype=ABC_core_MainReportLog "\|RemoteApplicationData\|" CV7=* | dedup CallID] | fields Phone, State, CallID | bucket _time span=1m | stats count(CallID) as Count by _time Phone State | where Count >=2 The query above gives me _time Phone State and Count. But I also need to display the CallID. How do I do that? Thanks in advance! ... View more

I have a query that counts events from 30 days ago to current day but I filter the results so that I am only getting the count of events for the days in those 30 days that equals the current week day. So that if today is Wednesday, I would only see the count of events for all the Wednesdays in the last 30 days. Query is below: index=abc sourcetype=abc_proxy(Action=InteractionQueued OR Action=InteractionDequeued) earliest=-30d@d latest=now()| timechart span=1d count| eval day_of_week = lower(strftime(_time, "%A")), now = lower(strftime(now(), "%A"))| where day_of_week = now|fields - day_of_week - now Table is kind of big because I am doing hourly breakdowns but a portion of the output looks like this: _time count 2018-07-11T00:00 7872 2018-07-11T01:00 5741 2018-07-11T02:00 6480 2018-07-11T03:00 10198 2018-07-11T04:00 11394 2018-07-11T05:00 17033 2018-07-11T06:00 17464 2018-07-11T07:00 21961 2018-07-11T08:00 28636 2018-07-11T09:00 27801 2018-07-11T10:00 28537 2018-07-11T11:00 27996 2018-07-11T12:00 24798 2018-07-11T13:00 27681 2018-07-11T14:00 25653 2018-07-11T15:00 32204 2018-07-11T16:00 32450 2018-07-11T17:00 23217 2018-07-11T18:00 23988 2018-07-11T19:00 22152 2018-07-11T20:00 19021 2018-07-11T21:00 19446 My problem now is with the visualization. Right now I get one continuous line for all the week days that match current weekday. I would like to have a line graph where each day is a separate line in the graph and where each line has an hourly granularity. I wish I could attach images but I don't have enough points. BUt basically whatever the output is of the query, switch to visualization tab and select line graph. Thanks in advance ... View more