How to get at two fields from a subsearch to the main search results

[Sayanta_Basak_I]

Hello,

I am stuck with a scenario and can not figure out the right way out. I want my Sub search to retrieve 2 fields out of which 1 exist in primary search the other one really needs to just hang out in the end result. I might not be able to use the join in the right manner

my search:
index = ids [search index=vm sourcetype="vuln" daysago=30 cvss>7 | dedup dest | table dest | rename dest as DstIP]|join dest[search index=vm sourcetype="vuln" daysago=30 cvss>7| dedup signature | table signature ] AND [search index=windows sourcetype="script" |stats first(SymantecDefsDate) as SymantecDefDate by host |eval Symdef_exp_date=relative_time(now(), "-3d@d" ) |eval SymDef_Date=strptime(SymantecDefDate,"%Y-%m-%d") |eval SymDefination_Date=if(SymDef_Date7 | dedup dest | table dest | rename dest as DstIP]|join
dest[search index=vm sourcetype="vuln" daysago=30 cvss>7| dedup signature | table signature ]
Subsearch 2: [search index=windows sourcetype="script" |stats first(SymantecDefsDate) as SymantecDefDate by host |eval
Symdef_exp_date=relative_time(now(), "-3d@d" ) |eval SymDef_Date=strptime(SymantecDefDate,"%Y-%m-%d") |eval
SymDefination_Date=if(SymDef_Date<Symdef_exp_date,"noncomplaint","complaint")| search
SymDefination_Date=noncomplaint | lookup dnslookup clienthost AS host | fields clientip | dedup clientip |rename clientip as DstIP]

But My join here does not give back the correct result for signature/sig in the final output. I have tried both join with 'dest' and 'DstIP' but no luck . Need your expert advise

[maciep]

that's quite a search. It might be helpful to explain what you're trying to do as well, because I have a feeling this could be simplified a bit.

But since you are renaming everything as DstIP, that's what you should be joining on. And I'm not sure how the signature subsearch is supposed get joined since it doesn't have a DstIP.

In general, I'd suggest building one search, the add on one join, then add on the next join, and so on. That way you can validate your logic/search as you go instead of trying to troubleshoot one super long complicated search from the start.

[Sayanta_Basak_I]

@maciep

I am posting a simpler version of the search:

index="ids" Message=remote*code*execution [search index=vm sourcetype="vuln" daysago=30 cvss>9 | dedup dest | table dest | rename dest as DstIP ]| join dest [search index=vm sourcetype="vuln" daysago=30 cvss>9 | dedup signature | table signature ] | stats values(Message) as Message values(signature) as sig by DstIP

the sub search with "index=vm: has 2 fields that i need. viz dest and signature. dest for correlation and signature to be displayed out in the final table. I have refereed below link

https://answers.splunk.com/answers/120641/subsearch-returning-2-fields-and-evaluation-needed-on-one....

[Sayanta_Basak_I]

got your explanation @maciep
So what do you suggest to be done here

If i carry signature from the 1st search and do not dedup then I will end up with duplicate data combination for DstIP and signature, which I would have extracted using values had the logic worked

If I flip the logic I end up with similar issue since I need 2 fields each from primary search and sub search, so which ever way i ll have the same issue

My requirement is very straightforward

I needs fields DstIP,Message from index="ids
I need fields DstIP,signature from index=vm
I want the final table to have DstIP,Message and signature

I think I am missing something simple here and causing all the confusion.
HELP!!

[maciep]

I'm not even sure you need a join here. If you have one base search that gets events from both the ids index as well as the vm index (using the same filters you already have), then you will have all of the data you need in your search results already.

Then you should be able to just do your stats - values() should handle the dedup for signature. And you can add an aggregate to get a distinct count of indexes and then remove any rows where that is 1. Meaning, if a DstIP is in only one index, you don't want to see it anyway, right?

Does that make sense? Something like this maybe? I'm still not sure which fields for the IP exist where, but will toss a coalesce function in here to "normalize" that if needed.

(index="ids" Message=remote*code*execution) OR (index=vm sourcetype="vuln" daysago=30 cvss>9)
| eval DstIP = coalesce(DstIP,dest)
| stats values(Message) as Message, values(signature) as sig, dc(index) as indexes by DstIP
| where indexes=2
| fields - indexes

[Sayanta_Basak_I]

Hello,

Thank you for your support but still it did not workout. Finally I wrote a lookup gen to populate the VM data separately and added lookup to call that.

Even though it servers my purpose but I would be very interested to know the right way to attain this via join.

[maciep]

let's break down what your search is doing and then go from there.

index="ids" Message=remote*code*execution 
    [ search index=vm sourcetype="vuln" daysago=30 cvss>9 
    | dedup dest 
    | table dest 
    | rename dest as DstIP ] 

So this is searching the ids index for any DstIP fields from your vm index subsearch. So at this point you will only have events from the ids index, presumably including a DstIP field. Then...

| join dest 
    [ search index=vm sourcetype="vuln" daysago=30 cvss>9 
    | dedup signature 
    | table signature ] 

Now you are trying to join that initial result set with another search of your vm index. This search has the same criteria as the first subsearch, but it only results in one field - a signature field. So it is impossible for the join to work, because you are essentially asking splunk to join the dest field from the original results (if that field even exists there?) to a dest field in this subsearch, but that field doesn't exist.

Does that make sense? This is like a sql join in that the join field has to exist in both sets of data for it to successfully join.

Then

| stats values(Message) as Message values(signature) as sig by DstIP

So this stats will effectively just be running on your original results, since the join failed. not sure if signature, Message and DstIP all exist in the ids index, but those are the only results you'll be seeing.

That's kinda why I was asking for the description of what you are trying to do because it's hard to infer the goal based on the search.

It may be as simple as including the dest/DstIP in the table of your join subsearch, so you would still have the signature, but then the join would have that field to join on as well. But you may not want to dedup the signature at that point, because you may lose some dest values? Depends on your data.

Or you could maybe even flip the logic, and start with your vm search and then left join to the ids on the appropriate dest field.

Hopefully that helps...

[damiensurat]

Exactly what went through my mind when I read the search. Join is used on a common field between the two searches. Then any and all of the additional fields from the subsearch are added to the data where the common join field matches. Here’s an example. Say I had two searches, and the first search returned host and ip address and the subsearch contained the same host names and had additional data I wanted to add wherever host names match from the initial search in the subsearch. I would do:
My search which output host and ip:
Search something | fields host, ip | join type=left host [| search something else | fields host, location, model] | table *.

What would happen is the subsearch would match all hosts returned from the initial search and give me the host, ip, location, and model based on the host in the resultant set of data. Be aware that join is case sensitive with sub searches. So I tend to use an eval to make the existing field to match on either upper or lower if it is a string. Eg;

Search something | fields host, ip | eval host = upper(host) | join type=left host [| search something else | fields host, location, model | eval host = upper(host) ] | table *

Hopefully that helps you in understand the join command. Happy splunking!

[Sayanta_Basak_I]

@damiensurat

Hello,

I have edited the query as per your guidelines still no luck. It just shows me data o/p of outer search

index="ids" Message=remote*code*execution| fields DstIP,Message | join type=left DstIP [|search index=vm sourcetype="vuln" daysago=30 cvss>9 | dedup dest| fields dest,signature | rename dest as DstIP ] | table *

[Sayanta_Basak_I]

yes DstIP and dest has same format and use same source. we have matching events all right respect to IP

[Elsurion]

Just to be sure, the DstIP and dest have the same format (IPv4 vs DNS vs IPv6) and they share both the same source?
Not that the vm index useses internal-IPs and the ids index the external-IPs.

What i saw is, that you doing the lookup on the dns only for the Windows Index and only in your second join, the first is without lookup.