The latest google provider for stackql is available now, and includes a new oracledatabase service, including resources for cloud_vm_clusters, db_nodes, db_servers, cloud_exadata_infrastructures, entitlements, and more.
Summary stats for the new google provider:
| Version | v24.09.00254 |
| Total services | 168 |
| Total resources | 1941 |
Let us know what you think! ⭐ us on GitHub.
Analyzing firewall rules is crucial for maintaining security in your cloud infrastructure. Using StackQL, you can efficiently query and analyze Google Cloud firewall configurations to ensure that your security policies are correctly implemented and that there are no unexpected open ports or protocols that might pose a security risk. Below is a simple query that retrieves important details about the ingress firewall rules for a specific network in a Google Cloud project.
SELECT
name,
source_range,
ip_protocol,
allowed_ports,
direction
FROM (
SELECT
name,
source_ranges.value as source_range,
JSON_EXTRACT(allowed.value, '$.IPProtocol') as ip_protocol,
JSON_EXTRACT(allowed.value, '$.ports') as allowed_ports,
direction
FROM google.compute.firewalls, json_each(sourceRanges) as source_ranges, json_each(allowed) as allowed
WHERE project = 'stackql-k8s-the-hard-way-demo'
AND network = 'https://www.googleapis.com/compute/v1/projects/stackql-k8s-the-hard-way-demo/global/networks/kubernetes-the-hard-way-dev-vpc'
) t
WHERE
source_range = '0.0.0.0/0'
and direction = 'INGRESS';
This query provides a comprehensive list of all ingress firewall rules that apply to any IP address (0.0.0.0/0) within the specified Google Cloud project and network. The results include the firewall rule name, the source IP range, the protocol, the allowed ports, and the direction of the traffic, an example is shown below:
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| name | source_range | ip_protocol | allowed_ports | direction |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| default-allow-icmp | 0.0.0.0/0 | icmp | null | INGRESS |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| default-allow-rdp | 0.0.0.0/0 | tcp | ["3389"] | INGRESS |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| default-allow-ssh | 0.0.0.0/0 | tcp | ["22"] | INGRESS |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0 | tcp | ["22"] | INGRESS |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0 | tcp | ["6443"] | INGRESS |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0 | icmp | null | INGRESS |
|-----------------------------------------------|--------------|-------------|---------------|-----------|
You can use this query to help quickly identify potential security vulnerabilities. Regularly auditing these rules ensures that your cloud environment remains secure and that only the necessary ports and protocols are open to the internet.
Give us your feedback! ⭐ us here!
We have released the latest StackQL provider for Google, which includes:
alloydb, apphub, biglake, bigquerydatapolicy, looker and more)More information is available here. Run the following to install or update the Google provider:
-- run from stackql shell
REGSITRY PULL google;
or
# from the command line
stackql registry pull google
Give us your feedback! ⭐ us here!
StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework. Use cases include CSPM, asset inventory and analysis, finops and more, as well as our IaC and ops (lifecycle management).
The three major cloud providers all offer a built-in Linux shell for executing commands using their respective CLIs; in some cases, they come with tools like terraform pre-installed. They are pre-authorized with your credentials in the cloud console for the user you authenticated with.
Now you can easily use stackql - a unified analytics and IaC dev tool - in all major cloud providers' built-in shells, using cloud shell scripts packaged with the stackql Linux binary (available from v0.5.587 onwards).
StackQL is particularly useful for asynchronously querying across regions in AWS, projects in Google, or resource groups in Azure, which is challenging to do via the CLIs. For example:
SELECT region, COUNT(*) as num_functions
FROM aws.lambda.functions
WHERE region IN (
'us-east-1','us-east-2','us-west-1','us-west-2',
'ap-south-1','ap-northeast-3','ap-northeast-2',
'ap-southeast-1','ap-southeast-2','ap-northeast-1',
'ca-central-1','eu-central-1','eu-west-1',
'eu-west-2','eu-west-3','eu-north-1','sa-east-1')
GROUP BY region;
Additionally, you could authenticate to another provider from one cloud shell simultaneously and run multi-cloud inventory commands. For example:
SELECT
name,
SPLIT_PART(machineType, '/', -1) as instance_type,
'google' as provider
FROM google.compute.instances
WHERE project IN ('myproject1','myproject2')
UNION
SELECT
instanceId as name,
instanceType as instance_type,
'aws' as provider
FROM aws.ec2.instances
WHERE region IN (
'us-east-1','us-east-2','us-west-1','us-west-2',
'ap-south-1','ap-northeast-3','ap-northeast-2',
'ap-southeast-1','ap-southeast-2','ap-northeast-1',
'ca-central-1','eu-central-1','eu-west-1',
'eu-west-2','eu-west-3','eu-north-1','sa-east-1');
To get started with StackQL in your preferred cloud shell environment, download the StackQL package using the following command:
curl -L https://bit.ly/stackql-zip -O \
&& unzip stackql-zip
This command downloads the StackQL package, unzips it, and sets the appropriate permissions. From there, you can use our tailored scripts for AWS, Google Cloud, or Azure to integrate StackQL seamlessly into your cloud shell environment.
Run the stackql-aws-cloud-shell.sh as follows to use the StackQL command shell within the AWS cloud shell:
sh stackql-aws-cloud-shell.sh
An example is shown here:
You can also run stackql exec commands using the stackql-aws-cloud-shell.sh script; for instance, this command will write a CSV file for the results of a query that could be downloaded from the Cloud Shell.
sh stackql-aws-cloud-shell.sh exec \
--output csv --outfile instances.csv \
"SELECT region, instanceType FROM aws.ec2.instances WHERE region IN ('us-east-1')"
Additionally, you can supply an IAM role using the --role-arn argument to assume another role for your query or mutation operation, an example is shown here:
sh stackql-aws-cloud-shell.sh \
--role-arn arn:aws:iam::824532806693:role/SecurityReviewerRole exec \
--infile query.iql \
--output csv --outfile output.csv
Run the stackql-azure-cloud-shell.sh as follows to open a StackQL command shell from the Azure Cloud Shell:
sh stackql-azure-cloud-shell.sh
An example is shown here:
Similar to the AWS script, you can also invoke stackql exec as well, an example is shown here:
sh stackql-azure-cloud-shell.sh exec \
--output csv --outfile instances_by_location.csv \
"SELECT location, COUNT(*) as num_instances FROM azure.compute.virtual_machines WHERE resourceGroupName = 'stackql-ops-cicd-dev-01' AND subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468' GROUP BY location"
Run the stackql-google-cloud-shell.sh as shown below to launch a StackQL command shell from within the google cloud shell:
sh stackql-google-cloud-shell.sh
An example is shown here:
As with the other two providers, you can run exec commands following the example below:
sh stackql-google-cloud-shell.sh exec \
--output csv --outfile instances.csv \
"SELECT name, status FROM google.compute.instances WHERE project = 'stackql-demo'"
Please give us your feedback! Star us at github.com/stackql.
stackql is a dev tool that allows you to query, manage, and perform analytics against cloud and SaaS resources in real time using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.
The googleadmin StackQL provider is now available, which allows you to query, provision, or manage Google Workspace users, groups, devices, and more using StackQL. The googleadmin provider can be used with the google provider or other cloud providers to generate entitlements reports (or user access reviews) where Google Workspace identites are used in identity federation or IAM bindings.
The full documentation on how to use a Google service account for authentication to the googleadmin provider is available here. Information about the directory resources available and their fields and methods, is available in the StackQL Provider Registry Docs.
A simple query using the googleadmin provider is shown here:
SELECT
primaryEmail,
lastLoginTime
FROM
googleadmin.directory.users
WHERE domain = 'stackql.io'
AND primaryEmail = 'javen@stackql.io';
which would return the following results...
|------------------|--------------------------|
| primaryEmail | lastLoginTime |
|------------------|--------------------------|
| javen@stackql.io | 2023-07-08T23:30:31.000Z |
|------------------|--------------------------|
Here is an example using built-in functions in StackQL (more information about built-in functions is available in the StackQL docs):
SELECT
primaryEmail,
json_extract(name, '$.fullName') as full_name,
lastLoginTime
FROM
googleadmin.directory.users
WHERE domain = 'stackql.io'
AND primaryEmail = 'javen@stackql.io';
which would return results like this...
|------------------|--------------|--------------------------|
| primaryEmail | full_name | lastLoginTime |
|------------------|--------------|--------------------------|
| javen@stackql.io | Jeffrey Aven | 2023-07-08T23:30:31.000Z |
|------------------|--------------|--------------------------|
Here is an example of a summary query that could be useful:
SELECT
isAdmin,
COUNT(*) as num_admins
FROM
googleadmin.directory.users
WHERE domain = 'stackql.io'
GROUP BY isAdmin
results in...
|---------|------------|
| isAdmin | num_admins |
|---------|------------|
| false | 9 |
|---------|------------|
| true | 2 |
|---------|------------|
LEFT JOIN with the google providerUsing the LEFT OUTER JOIN capability with StackQL, you can generate entitlements or user access management reports that span across Google Workspace as an Identity Provider (IdP) and a Google Cloud resource (including Organizations, Folders, Projects, and resources), such as:
SELECT
split_part(json_extract(iam.members,'$[0]'), ':', 2) as member,
iam.role as role,
users.lastLoginTime
FROM google.cloudresourcemanager.organizations_iam_bindings iam
LEFT OUTER JOIN googleadmin.directory.users users
ON split_part(json_extract(iam.members,'$[0]'), ':', 2) = users.primaryEmail
WHERE users.domain = 'stackql.io'
AND iam.organizationsId = 141318256085
AND users.primaryEmail = 'javen@stackql.io';
which would return...
|------------------|------------------------------|--------------------------|
| member | role | lastLoginTime |
|------------------|------------------------------|--------------------------|
| javen@stackql.io | roles/bigquery.resourceAdmin | 2023-07-08T23:30:31.000Z |
|------------------|------------------------------|--------------------------|
| javen@stackql.io | roles/logging.admin | 2023-07-08T23:30:31.000Z |
|------------------|------------------------------|--------------------------|
Let us know what you think!