Join Result of Subsearch With Main Search if it Exists in String

[mcgi906]

I have been beating my head against a wall trying to figure this out and have not been having much luck, Ive tried everything from using appendcols, append, map, and cant get it to work.

My subsearch below returns a string
index=a | eval SPLITid=substr(SPLITLOTID,2,8) | where match(SPLITid,".")

that I need to use in my main search.
search index=b| eval REASON=split(REASON,"/") | eval filteredVal=mvfilter(match(REASON, SPLITid)) | table filteredVal

Please help.

[sundareshr]

How about this

index=b| eval REASON=split(REASON,"/") | mvexpand REASON | search  [search index=a | eval SPLITid=substr(SPLITLOTID,2,8) | where match(SPLITid,".") | eval REASON="*".SPLITid."*" | table REASON]

[maciep]

A couple ideas in general for getting that one string into all of your events for comparison.

Use the join command to put the two searches together. You can eval some field in both searches to the same value and then join on that field. Something like this:

index=b
| eval tmp="match" 
| join tmp 
[
    search index=a 
    | eval SPLITid=substr(SPLITLOTID,2,8) 
    | where match(SPLITid,".") 
    | eval tmp="match" 
    | table tmp SPLITid
] 
|search eval REASON=split(REASON,"/") 
| eval filteredVal=mvfilter(match(REASON, SPLITid)) 
| table filteredVal

Another option might be to use append and eventstats

index=b
| append
[
    search index=a 
    | eval SPLITid=substr(SPLITLOTID,2,8) 
    | where match(SPLITid,".") 
    | table SPLITid
] 
| eventtats values(SPLITid) as SPLITid
|search eval REASON=split(REASON,"/") 
| eval filteredVal=mvfilter(match(REASON, SPLITid)) 
| table filteredVal

Not sure if those are terribly efficient, but I think they get the job done.

[somesoni2 · Accepted solution]

Give this a try

index=b [search index=a | eval SPLITid=substr(SPLITLOTID,2,8) | where match(SPLITid,".") | eval REASON="*".SPLITid."*" | table REASON]
| eval REASON=split(REASON,"/")  | mvexpand REASON | search [search index=a | eval SPLITid=substr(SPLITLOTID,2,8) | where match(SPLITid,".") | eval REASON="*".SPLITid."*" | table REASON] | table REASON

[mcgi906]

This works for the most part, but Im getting a bunch of duplicates for the SPLItid's. I know I need to use dedup, but where should I place it? Thanks

[somesoni2]

Just after the last table (| dedup REASON)

[mcgi906]

The problem Im running into is that it removes the duplicates of REASON, but I am trying to remove all duplicates of SPLITid.

[somesoni2]

Ok.. in both subsearches replace search index=a | eval SPLITid=substr(SPLITLOTID,2,8) with search index=a | eval SPLITid=substr(SPLITLOTID,2,8) | dedup SPLITid

[mcgi906]

It worked, awesome thanks so much