Index substring match from inputlookup?

[sarge338]

Good afternoon.

I have been working on this issue for a couple of days, and I just cannot seem to get this SPL correct.

I am running a report on SIP traffic for a specific scenario, and the results of that report are written to an outputlookup (CSV) file.  I then am trying to use that CSV file in a new report to try to find any history of the phone numbers found in the first report.

I have successfully extracted the user-part of the SIP URI (the phone number), and when I look at the results of just that SPL, everything looks good.  My problem comes from extracting that data as part of a subsearch, so that I can use the user-part as a means of finding all SIP URIs for that phone number for a period of time. 

I know that phonenumber will never equal SIPURI, so I am using "| eval SIPURI = user-part."%" in the subsearch to make the subsearch a LIKE rather than an EQUALS comparison.  I am still not getting any matching events, though.

Can someone tell me the proper way to, effectively, do a substring search with results of a subsearch, to find events in the index which contain said substring?

Thank you in advance!

[sarge338]

Thanks for the quick reply, @somesoni2 .

Here is my sanitized search.  I think I am doing what you suggested, but it is not working.

index="call_data" event_type="sip*" ("STARTED" OR "ENDED") 
[|inputlookup short_duration_results.csv 
| fields sourcenumber
| rex field=sourcenumber "(?<user_part>[^@]+)" 
| dedup user_part
| eval SIPURI=user_part."%" 
| fields - sourcenumber user_part
| format ]
| table
meta
SIPURI
state.state
| rename
meta as direction
SIPURI as sourcenumber
state.state as state
| eval duration_seconds=offsetInMs/1000
| table guid _time timestampStr sourcenumber duration_seconds state
| eval sourcenumber=mvdedup(sourcenumber)
| eval startTime=CASE(state="STARTED",timestampStr)
| eval endTime=CASE(state="ENDED",timestampStr)
| eval endDuration=CASE(state="ENDED",duration_seconds)
| stats values(*) as * by guid
| where endDuration > 120
| table guid sourcenumber startTime endDuration
| sort -startTime

Thank you, again!

[yuanliu]

You can make it so much easier for volunteers to help if you post sample/mock data (including content/format of lookup) instead of dropping hint fragments about the logic you are following. (Very often SPL is not the most direct way to describe logic, especially if the SPL does not get the desired result.)  It would also help greatly if you show results from posted data and explain why it is not as desired unless the difference is painfully obvious.  In this case, a phrase like "the search returns no result" is far better than "not working."

Here, I will assume that the search returns no results even though the same index data are used to produce the lookup.  Is that what you mean by "not working"?  If my speculation is correct, this is a simple matter of wildcard to use.  Change the first command to

index="call_data" event_type="sip*" ("STARTED" OR "ENDED") 
[|inputlookup short_duration_results.csv 
| fields sourcenumber
| rex field=sourcenumber "(?<user_part>[^@]+)" 
| dedup user_part
| eval SIPURI=user_part."*" 
| fields - sourcenumber user_part
| format ]

"%" is used as wildcard only with LIKE operator or like function in evaluation context such as in a where command.  Wildcard character in search command is "*".

While changing wildcard character can make the search return results, using subsearch from the lookup may not be the best strategy for such a use case, depending on how large the lookup is.

[somesoni2]

Try something like this.

your base search | search [| inputlookup yourlookup.csv | eval SIPURI='user-part'."*" | table SIPURI]

 This is assuming that your lookup table has the field  "user-part" which contains partial value which your base search has for field "SIPURI". It would be beneficial if you can share you obfuscated search to get better answer.