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 ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...