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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...