SubSearch with different index and pass the fields to MainSearch and only use one filed from it for query purpose

[adeshreddy]

Hey Community, 

I am trying to get my head around this query 

My subsearch below, The query will look for the api path,src and Ip's and I am doing dns lookup to get hostname which is present in different index 

site = "friendly" index=traffic_log src="*"  uri="*" | eval date = date_month + "/" + date_mday + "/" + date_wday + "/" + date_year | mvexpand date | dedup src | dedup uri  | lookup dnslookup  clientip as src OUTPUT clienthost as ComputerName | where like (ComputerName,"p%") | 
dedup ComputerName |table ComputerName,src,uri,date

Main query. If see my main query Computername is the only filed which is present in main index search and want to use for searching with computername. which will give the owner details of the hostname but also I want the src,uri,date fileds from subsearch to be added in table 

index="wineventlog" source="WinEventLog:Application"  [ search site = "friendly.org" index=traffic_log src="*"  uri="*" | eval date = date_month + "/" + date_mday + "/" + date_wday + "/" + date_year | mvexpand date | dedup src | dedup uri  | lookup dnslookup  clientip as src OUTPUT clienthost as ComputerName | where like (ComputerName,"p%") | 
dedup ComputerName |fields ComputerName,src,uri,date] | dedup ComputerName| dedup ownerEmail | dedup ownerFull | dedup ownerName | dedup ownerDept | table ComputerName, ownerEmail,ownerFull,ownerName,ownerDept,src,uri,date

Can someone throw insights into the query

[adeshreddy]

Hey Mayur, 

According to your suggestion I tried the query 

Subsearch give me the output I need 

ComputerName	src	uri	date
pc44        	xx	/ui/n	apri/7/2022

But when combined with main search 

ComputerName |ownerEmail| ownerFull|ownerName|ownerDept| date| src | uri
4GV	        test@com   Jack	      nal        ghjsj    <N/A> <N/A> <N/A>	 	 	 

I want use computername from subsearch  to search in main search and also append the sub search filed data to main search 

[mayurr98]

Well thats what "type=left" will do, it will give you results from the main search as well as the matching results from the subsearch.

The above example is not matching your computerName is different, for subsearch it's PC44 and for main search it's 4GV that's why you see date,src and uri field blank in the result. Look for the one's where computerName is matching and there you should see all the fields.

[adeshreddy]

Thanks Mayur,

The query helped.

What happen was subsearch computername was returning the value with lower case (pc4555)and main search computername value was returning uppercase (PC455).

Converted the main search value to lower case was able to find match the values.

Do you have any idea to omit the the data if field values are empty 

[mayurr98 · Accepted solution]

Not sure if this is what you are looking for

below search will give you results from the main search as well as matching results from the subsearch:

if you are just interested in matching results then change type=inner

index="wineventlog" source="WinEventLog:Application" 
| dedup ComputerName, ownerEmail, ownerFull, ownerName, ownerDept 
| stats values(ownerEmail) as ownerEmail,values(ownerFull) as ownerFull, values(ownerName) ownerName, values(ownerDept) as ownerDept by ComputerName 
| join type=left ComputerName 
    [ search index=traffic_log src="*" uri="*" site="friendly.org" 
    | eval date = date_month + "/" + date_mday + "/" + date_wday + "/" + date_year 
    | mvexpand date 
    | dedup src, uri 
    | lookup dnslookup clientip as src OUTPUT clienthost as ComputerName 
    | where like (ComputerName,"p%") 
    | dedup ComputerName 
    | stats values(src) as src, values(uri) as uri, values(date) as date by ComputerName]