Splunk Search

Help with JOIN in multi search

jmpaul012
New Member

I'm trying to do a JOIN with 2 search but I'm having issues. I tried to do a multi search join but I'm getting a streaming error.
I think one of the problems is the seaches both have an "id" column, but they reference two totally different things and I need to use it in the join. Here is the info below:

(I want to join these two searches)

SEARCH1 - "id" column is the ec2 instance name (I use this column for the join)

index=*_aws sourcetype=aws:description source="*ec2_instances"  |rename id AS ec2id | table account_id, ec2id, state, ip, publicIP, instance_type, platform vpc_id

SEARCH2- "id" column is the sg name" (I use instances{}.id to join "id" in SEARCH1)

index=*_aws sourcetype=aws:description source=*:ec2_security_groups
| rename id AS sgid, instances{}.id AS ec2id
| table account_id, sgid, ec2id, name, instances{}.id, rules{}.from_port, rules{}.grants{}.cidr_ip

I need to join on column ec2id.

0 Karma
1 Solution

woodcock
Esteemed Legend

Try this:

(index=*_aws AND sourcetype="aws:description") AND (source="*ec2_instances" OR source=*:ec2_security_groups)
| eval ec2id = if(match(source, "ec2_instances$"), id, 'instances{}.id')
| eval sgid = if(match(source, "ec2_instances$"), null, id)
| table account_id, sgid, ec2id, name, state, ip, publicIP, instance_type, platform vpc_id instances{}.id, rules{}.from_port, rules{}.grants{}.cidr_ip
| stats values(*) AS * BY ec2id

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

(index=*_aws AND sourcetype="aws:description") AND (source="*ec2_instances" OR source=*:ec2_security_groups)
| eval ec2id = if(match(source, "ec2_instances$"), id, 'instances{}.id')
| eval sgid = if(match(source, "ec2_instances$"), null, id)
| table account_id, sgid, ec2id, name, state, ip, publicIP, instance_type, platform vpc_id instances{}.id, rules{}.from_port, rules{}.grants{}.cidr_ip
| stats values(*) AS * BY ec2id
0 Karma

jmpaul012
New Member

Your query seems to be working a lot better! I do have a question. This part didn't work:

(index=*_AND aws sourcetype=aws:description)

I had to remove the _AND:
(index=* aws sourcetype=aws:description)

What is the logic behind, index=*_AND ?

0 Karma

woodcock
Esteemed Legend

That was a typo; I fixed it. I like to have explicit AND instead if implied.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Perhaps join is not needed.

index=*_aws sourcetype=aws:description (source="*ec2_instances" OR source=*:ec2_security_groups)
| eval id = if(like(source, "%:ec2_security_groups"), sgid)
| eval ec2id = if(like(source, "%:ec2_security_groups"), instances{}.id, id)
| stats values(account_id) as account_id, values(state) as state, values(publicIP) as publicIP, values(instance_type) as instance_type, values(platform) as platform, values(vpc_id) as vpc_id, values(name) as name, values(rules{}.from_port) as from_port, values(rules{}.grants{}.cidr_ip) as cidr_ip by ec2id
---
If this reply helps you, Karma would be appreciated.
0 Karma

jmpaul012
New Member

I got the following error, any suggestions?

Error in 'eval' command: The arguments to the 'if' function are invalid.

I tried to change some of the field names for how I understand your suggestion but still can't get it to work.

0 Karma

jaime_ramirez
Communicator

There is an error in the 2 line (if command missing a else case):

...
| eval id = if(like(source, "%:ec2_security_groups"), sgid)
...

This should be:

...
| eval id = if(like(source, "%:ec2_security_groups"), sgid, id)
...
0 Karma

jmpaul012
New Member

Thanks, the search runs but doesn't return results. If I run the query without "by ec2id" at the end it returns 700+ rows. Any idea why it isn't returning results with "by ec2id"?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

We may need to swap the order of evals. What are you seeing for the values of ec2id?

index=*_aws sourcetype=aws:description (source="*ec2_instances" OR source=*:ec2_security_groups)
| eval ec2id = if(like(source, "%:ec2_security_groups"), instances{}.id, id) 
| eval id = if(like(source, "%:ec2_security_groups"), sgid, id)
| stats values(account_id) as account_id, values(state) as state, values(publicIP) as publicIP, values(instance_type) as instance_type, values(platform) as platform, values(vpc_id) as vpc_id, values(name) as name, values(rules{}.from_port) as from_port, values(rules{}.grants{}.cidr_ip) as cidr_ip by ec2id
---
If this reply helps you, Karma would be appreciated.
0 Karma

jmpaul012
New Member

No values are being returned for ec2id

This part of the query doesn't work becuase "ec2id" is what I want to rename "instances{}.id to.

 | eval ec2id = if(like(source, "%:ec2_security_groups"), instances{}.id, id) 
0 Karma

richgalloway
SplunkTrust
SplunkTrust

How does it not work? The eval is functionally equivalent to rename in that you get a field called 'ec2id' with the value from field 'instances{}.id'.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...