topic Re: Help with JOIN in multi search in Splunk Search

Help with JOIN in multi search

jmpaul012 — Tue, 23 Jul 2019 13:35:38 GMT

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.

Re: Help with JOIN in multi search

richgalloway — Tue, 23 Jul 2019 18:43:23 GMT

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

Re: Help with JOIN in multi search

jmpaul012 — Tue, 23 Jul 2019 18:54:42 GMT

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.

Re: Help with JOIN in multi search

jaime_ramirez — Tue, 23 Jul 2019 23:09:41 GMT

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

Re: Help with JOIN in multi search

jmpaul012 — Wed, 24 Jul 2019 12:30:50 GMT

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"?

Re: Help with JOIN in multi search

richgalloway — Wed, 24 Jul 2019 14:13:04 GMT

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

Re: Help with JOIN in multi search

jmpaul012 — Thu, 25 Jul 2019 12:39:21 GMT

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)

Re: Help with JOIN in multi search

woodcock — Thu, 25 Jul 2019 23:57:32 GMT

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

Re: Help with JOIN in multi search

jmpaul012 — Mon, 29 Jul 2019 11:50:38 GMT

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 ?

Re: Help with JOIN in multi search

richgalloway — Tue, 30 Jul 2019 12:16:43 GMT

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

Re: Help with JOIN in multi search

woodcock — Tue, 30 Jul 2019 14:50:10 GMT

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