Prisma Cloud RQL join operator

[SPAUL2008]

Hello,

I would like to gain a clearer understanding of how the join operator functions within RQL.

Below is an example query I am currently working with:

Full Query: 

config from cloud.resource
where api.name = 'aws-ec2-describe-instances'
AND json.rule = tags[*].value does not start with "prismacloud"
AND tags[*].value does not start with "aws-cloud9" as X; config from cloud.resource where api.name = 'aws-ec2-describe-images'
and json.rule = image.name does not start with "company" as Y; filter '$.X.imageId equals $.Y.image.imageId'; show X;

1st part:

config from cloud.resource
where api.name = 'aws-ec2-describe-instances'
AND json.rule = tags[*].value does not start with "prismacloud"
AND tags[*].value does not start with "aws-cloud9" as X;

2nd part:
config from cloud.resource
where api.name = 'aws-ec2-describe-images'
and json.rule = image.name does not start with "company" as Y;

3rd part:

filter '$.X.imageId equals $.Y.image.imageId'; show X;

I have the following questions:

• In this scenario, if the first query returns 10 EC2 instances and the second query returns 100 AMI images, what would be the resulting values or structures of X and Y?

• When the filter condition is applied, how are these datasets compared? Specifically, does the evaluation involve comparing the imageId of each EC2 instance with the imageId of each AMI image (i.e., performing a 10 × 100 comparison), or is a different mechanism used?

Thanks,
Suranjit

[JCalloway1]

Hey Paul!
As for your first question, more than likely you would get the number of responses in which the image named matched the AMI image name.  From what I've seen in the past, it would just repeat, so you'd get 100 results. 

As to your second question, remember that we are not working with datasets in the strictest sense of the term.  Instead, we are working with string values obtained from JSON output.  So the matches need to be very literal. 

Hope this helps!

[SPAUL2008]

Thank you.

So, after the first RQL is executed, what would that 100 results (for example) include?

What I am trying to understand is that would the result include the entire JSON output for evey EC2 and every AMI image or just a specific key/value?

[JCalloway1]

Hey Paul!

By default, the behaviour is that the output that is displayed in the console will include the JSON output for each resource that you can explore and search ( </> ).  However, if you download the results or if you're creating a csv programmatically, you will just get the data that is in the table and not the JSON data.  Of course, you can allways use the "addcolumn" function to add additional columns to the output from within JSON data.   Hope this helps!

[SPAUL2008]

Thank you.

Would you have a few examples on how to use the addcolumn function?

I am also looking into the documentation.