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.
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
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
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 ?
That was a typo; I fixed it. I like to have explicit AND
instead if implied.
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
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.
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)
...
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"?
We may need to swap the order of eval
s. 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
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)
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'.