Help with JOIN in multi search

[jmpaul012]

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.

[woodcock · Accepted solution]

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

[jmpaul012]

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 ?

[woodcock]

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

[richgalloway]

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

[jmpaul012]

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.

[jaime_ramirez]

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

[jmpaul012]

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

[richgalloway]

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

[jmpaul012]

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) 

[richgalloway]

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