Skip to main content

18 posts tagged with "analytics"

View All Tags

· 2 min read

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!

· One min read

We have released the latest StackQL provider for Google, which includes:

  • 14 new services (including alloydb, apphub, biglake, bigquerydatapolicy, looker and more)
  • 231 new resources
  • 1,185 new methods

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!

· One min read
info

stackql is a dev tool that allows you to query and manage cloud and SaaS resources using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

We're excited to announce the release of two new StackQL providers: datadog and pagerduty. The daatdog provider includes 41 services and 405 methods at your disposal, you can query and manage everything from APM retention filters, audit logs, to cloud workload security and more. More information on the dataog provider can be found here.

The pagerduty provider includes an array of services like events, metrics, monitors, and users to fully leverage the operational prowess of these platforms. Whether it's maintaining the security posture with cloud_workload_security and security_monitoring or managing resources with containers and incidents, StackQL gives you the visibility and control over pagerduty, datadog or numerous other XaaS platforms. More information on the pagerduty provider can be found here.

Let us know your thoughts! Visit us and give us a ⭐ on GitHub

· 2 min read
info

stackql is a dev tool that allows you to query and manage cloud and SaaS resources using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

An exciting new feature to cap off 2023! Parallel query execution in StackQL. With the latest release of StackQL, parameters in WHERE IN clauses are fetched asyncronously.

This query for example, queries lambda functions across 17 AWS regions in under 1.5 sec, technically these are 17 parallel queries to 17 different endpoints. 

SELECT region, function_name
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'
)

You could do something similar for other hyperscalars, for example querying resources across projects in GCP asynchronously, or querying across resource groups in Azure asynchronously.

This capability was previously available using the pystackql package, as discussed in the Query Resources Across AWS Regions Asynchronously blog post, but is now available natively in the StackQL query optimizer.

You just need to add --execution.concurrency.limit=-1 to your stackql exec or stackql shell commands or when starting a StackQL Server using stackql srv. More query optimizations coming! Happy New Year! 🎉 🎉 🎉

Let us know your thoughts! Visit us and give us a ⭐ on GitHub

· 4 min read

Give us a ⭐ on GitHub

With the GoDaddy provider, users can leverage StackQL to interact with their GoDaddy resources directly through SQL queries. The addition of godaddy to the StackQL provider catalog further enabled a unified SQL-based experience for cloud services management.

Key Features​

  • Domain Management: List, update, and monitor domains registered with GoDaddy domains, including registration, renewal, and transfer.
  • DNS Configuration: Manage DNS settings for your domains using SQL commands, including querying and updating DNS records.
  • Security Certificates: Query and manage SSL certificates.
  • Order Management: Report on orders related to GoDaddy services.

Getting Started​

To begin using the GoDaddy provider, with stackql installed (see here), create a GoDaddy API token, populate an environment variable named GODADDY_API_KEY with this value, using stackql exec or stackql shell pull the latest provider for GoDaddy using:

REGISTRY PULL godaddy;

start querying!

Example Queries​

Here are some sample queries to get you started with the godaddy provider.

List Domains​

Heres a simple extract of domains with status, expiry date, privacy, and auto-renewal status:

SELECT 
 domain, 
 status, 
 expires, 
 privacy, 
 renewAuto 
FROM godaddy.domains.domains;

Domain Summary by Status​

Heres a quick summary by status:

SELECT status, count(*) as num_domains 
FROM godaddy.domains.domains
GROUP BY status;

Listing Nameservers for a Domain​

Heres a query expanding nameservers for a given domains:

SELECT 
 domain, 
 ns.value as nameserver 
FROM godaddy.domains.domains, json_each(nameServers) as ns
WHERE domain = 'chessenthusiastclubvictoria.org.au';

Get DNS Records for a Domain​

Heres an example query to get the CNAME records for a domain, you could use this to get any other type of DNS records (A, AAAA, MX, TXT, etc.):

select data, name, ttl, type  from godaddy.domains.records
where domain = 'zetadata.com.au' and type = 'CNAME';

You can visit the GoDaddy StackQL provider docs for a detailed view of all the features and services.

Join the Conversation​

We want your feedback to improve the StackQL experience continually. Visit our forum to discuss the new GoDaddy provider and share your thoughts.