Passing required fields from a subsearch to main search as well non-matched values

[amendon]

I have two different sources source 1 and source 2.

Source2 has the field called uri and source1 has the field called url. This field is used as join statement for subsearch.

index=source1 [search index=source2 | eval url=uri | fields + url] | table url,filename

Now performing the above query results only for the matched events retrieved from source2 and lookup back in source1.

But I want the below two query to be resulted too as an output :

1. I want to know for the list of uri in source2 which did not match the results in the source1.
2. Source2 has the field called filename which I also want to populate in the outer query using stats or table command.

[somesoni2 · Accepted solution]

Try something like this

index=source1 OR index=source2 | table url uri filename index| eval filename2=if(index="source2",filename,null()) |eval url =coalesce(url,uri) | fields - uri | stats values(*) as  * by url | eval OnlyinSource1=if(mvcount(index)=1 AND index="source1",1,0) | eval OnlyinSource2=if(mvcount(index)=1 AND index="source2",1,0) | eval MatchinginBoth=if(mvcount(index)=2,1,0)

Last 3 fields created are the one that you should use as filter as per your requirement.

[amendon]

QUick query in the above query, eval url =coalesce(url,uri), this might take all the url from the source1 and source 2, which will add up more time. Instead can we just look for the uri hits from the source2 and then use those to match against the source1 ?

The scenario is like source1 is an web traffic and source2 is like Intrusion detection. so I may not want to coalesce, instead just look up the one which is detected from Intrusion detection and check for the actual web traffic for further details like the web connection and reputation.

[amendon]

Also i tried above query,but all of the fields are 0 OnlyinSource2,MatchinginBoth and OnlyinSource1.

Sample ;

if there is a url www.xyz.com

lets say webtraffic index has the field called url.

intrusiondetection has field called uri.

index=webtraffic OR index=intrusiondetection | table ur uri filename index| eval filename2=if(index="intrusiondetection",filename,null()) |eval url =coalesce(url,uri) | fields - uri | stats values(webreputation) by url | eval OnlyinSource1=if(mvcount(index)=1 AND index="source1",1,0) | eval OnlyinSource2=if(mvcount(index)=1 AND index="source2",1,0) | eval MatchinginBoth=if(mvcount(index)=2,1,0)

When running this query and searching for the www.xyz.com for timeframe of 4 hours.. I should have got matchinginboth value to be 1. as I could see this domain in both the index. But i get this as 0.

[somesoni2]

You've changed my query during the stats (instead of values(*) you're using some different field ).

[amendon]

ok now i changed it to match your query and it works. Thanks for your help.

So now I have additional query , if I want to extend this search further say if this field OnlyinSource2 matches or holds values greater than 0, then I need to output the webtraffic logs for the matched uri.

[amendon]

there are lot of challenges on the above asked query, the reason is webtraffic logs will have http format appended, while the intrusiondetection might not have but it will just have the snippet of the path for the matched signature.

example: webtraffic logs can have www.xyz.com/abcde/1234

whereas intrusion detection might just have this portion in its uri /abcde/1234

so if above is the case, we never get this value MatchinginBoth to be toggled. it will always be 0.

And if I see this value MatchinginBoth, then i would like to extend the search to only show me the output of the webtraffic.

[woodcock]

This is your base search which is a full join:

source=source1 OR source=source2 | stats dc(source) AS numSources values(*) AS * by uri ...

To see a join, add this

... | where numSources=2

To see the opposite (your #1: list of uri in source2 which do not match results in source1), add this:

... | where numSources=1 AND source=source2

As far as your #2, the field exists for all of the searches given.

[amendon]

performing this query source=source1 OR source=source2 in general will take all the URL pointed from source1 and source2.

The scenario is like source1 is an web traffic logs and source2 is like Intrusion detection logs. so instead I just want to initally look up the URI as detected from the intrusion detection logs and check for the actual web traffic logs for further details like the web connection and reputation as well as the URI detected from Intrusion detection logs to be populated in the output if we did not find the match in the web logs.

ideally the subsearch helps doing that so the it initally when splunk runs it takes the inner query first and gets the list of uri from source2 and then uses that to match against the source1. But to match my above scenario not sure how could i proceed further

[woodcock]

My search does exactly what you are describing, based on the | where parts. Did you even run it? My answer is a complete solution. When people take the time to give you answers, you definitely should try them before you assume they don't work.