Splunk Search

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

mcgi906
Explorer

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

sundareshr
Legend

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]
0 Karma

maciep
Champion

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.

0 Karma

somesoni2
Revered Legend

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
Explorer

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

0 Karma

somesoni2
Revered Legend

Just after the last table (| dedup REASON)

0 Karma

mcgi906
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

mcgi906
Explorer

It worked, awesome thanks so much

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...