Help with RQL 'group by'

[CLimachi1]

Hi,

I want to do a search that groups every assset name of a result from an api by account and return the project where in can't find an specific string.

Wanted to know if 'group by' could be used for that?

Wasn't able to find examples of how to user this operator in the docs.

[bpachauli]

Hello CLimachi1
Can you please share an example of the objective you are trying to accomplish? Is this through API or RQL. An example would be great.

[CLimachi1]

Hi, 

It is through RQL. For example the return of: config from cloud.resource where cloud.type = 'gcp' AND cloud.service = 'Google Stackdriver Logging' AND api.name = 'gcloud-logging-sinks-list' 

Is something like this:

Resource Name	Account
default	dev
sink1	dev
sink2	dev
sinkpub	dev
default	qa
sink1	qa
sink3	qa
default	prod
sink4	prod
sink2	prod
sinkpub	prod

I would like to group the results by accoutn and only get a result for QA that doesn't have a resource with name "sinkpub".

dev	qa	prod
default	default	default
sink1	sink1	sink4
sink2	sink3	sink2
sinkpub		sinkpub

[bpachauli]

Hello, you can leverage the attribute - "cloud.account" in this use case. You can rewrite your query to 

config from cloud.resource where cloud.type = 'gcp' AND cloud.account = 'QA' AND cloud.service = 'Google Stackdriver Logging' AND api.name = 'gcloud-logging-sinks-list' 

If you want results from two or more accounts, use the operator IN

config from cloud.resource where cloud.type = 'gcp' AND cloud.account IN ( 'DEV', 'QA' ) AND cloud.service = 'Google Stackdriver Logging' AND api.name = 'gcloud-logging-sinks-list'

[bpachauli]

Hi CLimachi1. Please let me know if you were able to use the query to resolve your issue.

[CLimachi1]

Hello,

No, those queries are not what I need. 

I want to get as result an Account that doesn't have a sink with the specific string in their logging sink list.

[bpachauli]

Hello, for that you need to use the JSON rule feature in the RQL. For example, the below RQL will only show you results for account QA that doesn't have a resource with the name "sinkpub".

config from cloud.resource where cloud.type = 'gcp' AND cloud.account = 'QA' AND cloud.service = 'Google Stackdriver Logging' AND api.name = 'gcloud-logging-sinks-list'  AND json.rule = name does not equal "sinkpub" 

[PBurega · Accepted solution]

Can you clarify your requirements here?  You said:
'I would like to group the results by account and only get a result for QA that doesn't have a resource with name "sinkpub"'

In your example "qa" has the results {default, sink1, sink3}.  
"dev" has {default, sink1, sink2, sinkpub} - "sink2" is not in "qa", why is it not also a result you want? 
"prod" has {default, sink2, sink4, sinkpub} - "sink2" and "sink4" are not in "qa", why is this not also a result you want?

This type of report can be created by downloading the results from the Investigate page as a csv file, and using your favorite csv/spreadsheet tool to do the analysis and reporting.  Many customers use a BI tool to customize this type of reporting. 

Or you can use the Prisma Cloud API to get the results just as you get in the UI, and you can write the additional logic to perform what you are trying to create.  Prisma Cloud does provide Python libraries to get RQL queries and you just need to provide the additional custom logic to manipulate the data. 

Let me know if I can provide additional information or clarification.

[CLimachi1 · Accepted solution]

Hi, was able to resolve my requirement with another tool. Thanks.