AMA: Summarize data using J1QL


J1_Ambassador Hunter @2x

"Is there a way to summarize data using J1QL similar to Splunk's stats function? For example, I'd like to get a count of the percentage of buckets that have versioning enabled... something like this:

find google_storage_bucket 
| stats count(id) by versioningEnabled


Ideally, that would return two rows:
true  - # of buckets with versioningEnabled=true
false - # of buckets with versioningEnabled=false
I  know I can print a list of all buckets and export it to Excel or Sheets and figure it out there, but I was hoping I could do it directly in J1QL."

--Question from Greg on the J1 Community Slack


Answer from Crystal, Solutions Engineer

Hi Greg that is a great question. We don't have a full stats feature but we have something that can help. We can return a count of which buckets have versioningEnabled that is true, false, and undefined with the following query:

FIND google_storage_bucket as b 
COUNT(b), b.versioningEnabled
When you return count with other variables the count is automatically broken out by the variable with the highest number of different values. Does this work for your use case?

Response from Greg

Hey Crystal - That worked perfectly.  Thanks!

AMA is an ongoing series published each Thursday, highlighting questions the community has asked in our support and how-to channels on Slack. If you haven't already done so, please join us on Slack 


More AMAs

2022-12-09 AMA - SentinelOne not installed


  I’m trying to find which of my Mac hosts don’t have SentinelOne installed.
December 14, 2021
2021-12-02 AMA Date Comparions


  Are 'WHERE' and 'WITH' clauses always supposed to be of the syntactic form (PROPERTY) (OP) (EXPRESSION)? 
December 02, 2021
2021-11-11 AMA Query to find all critical findings


Is there an API or JupiterOne query that will pull all 'Critical' findings, all 'High' findings etc?
November 11, 2021



Posted By JupiterOne Team

The JupiterOne Team is a diverse set of engineers and developers who are working on the next generation of cyber asset visibility and monitoring.

To hear more from the JupiterOne Team, get our newsletter. No spam, just the good stuff once or twice a month. Sign up below.


cyber-security 1

Ad Title Placeholder

Lorem ipsum dolor sit amet, consectetur adipiscing elit.