While performing a security posture review for a client using Wiz, a common requirement surfaced: identify all cloud resources with a public IP address and understand their associated security vulnerabilities in a single, actionable view. The built-in Resource Query Language (RQL) is powerful for this, but crafting a precise query requires careful consideration of the data model.
The following RQL query achieves this by joining the `cloud_resource` and `vulnerability_findings` data tables. It filters for resources with a public IP, excludes those that are intentionally public (like a CDN), and returns a prioritized list with vulnerability details.
```sql
/* Wiz RQL: Public IP Resources with Critical/High Vulnerabilities */
resources
| where cloudProvider = 'AWS' /* or 'Azure', 'GCP' */
| where publicIpAddresses != []
| where name not like '%cloudfront%' /* Example exclusion */
| join (
vulnerability_findings: [
{severity: 'CRITICAL'},
{severity: 'HIGH'}
]
) on resourceId = resource_id
| project
resourceName = name,
resourceType = type,
publicIPs = publicIpAddresses,
vulnerabilityName = external_id,
severity,
description,
status
| sort by severity desc
```
**Key Components of the Query:**
* `publicIpAddresses != []`: Ensures we only include resources that currently have one or more public IPs assigned.
* The `join` clause: Performs an inner join with the `vulnerability_findings` table, limiting results to findings with 'CRITICAL' or 'HIGH' severity. This is crucial for focusing on the most significant risks.
* `project`: Renames and selects the most relevant columns for the output, creating a clear report.
* The `name not like` filter: Demonstrates how to exclude known benign resources (e.g., CloudFront distributions) to reduce noise. This should be tailored to your environment.
**Operational Pitfall to Note:** This query returns a point-in-time snapshot. For continuous monitoring, this logic should be scheduled as a Wiz Control, generating issues or notifications when a new resource matching this criteria appears. The join on vulnerability findings means a resource with a public IP but no critical/high vulns will *not* appear in the results. If you need an inventory of *all* public IP resources regardless of vuln state, use two separate queries or a left join.
This approach has proven effective for creating targeted remediation tickets for engineering teams, as it directly links an exposed asset to the specific vulnerabilities that increase its attack surface.
- Mike
- Mike
This is really helpful for a new user like me. I'm trying to do something similar on Azure. Could you explain the `join` part a bit more? Specifically, how does it link the resource to the right vulnerability finding? I'm worried I'd get cross-joined data if I wrote it wrong.
Hold up. That query's going to pull every critical/high vuln for *any* resource with a public IP, not just the ones attached to that specific resource. The join condition `resourceId = resource_id` looks correct syntactically, but in Wiz's data model, a vulnerability finding is often linked to an *image* or a *package*, not the cloud resource itself. You'll get a list, but it might imply an EC2 instance is vulnerable because of a finding on its AMI, which isn't always actionable.
Also, excluding by name like `not like '%cloudfront%'` is brittle. Better to filter by tags or a designated 'allowPublic' flag if you have one. Public IPs on a CDN are intentional; public IPs on a database instance usually are not. The query conflates them.
You'll end up with a massive, noisy table. Prioritization should happen after the join, by severity *and* by resource type criticality. An exposed load balancer with a high vuln is a bigger problem than an exposed, unused test instance with a critical one.
Finally, why restrict to AWS only in the sample? The pattern is similar in Azure and GCP, but the property for public IPs might be `publicIpAddresses` in one and `ipConfigurations` in another. A generic query rarely works across clouds.
Yeah, that join on `resourceId = resource_id` is the key part. It works, but the results can be misleading like others said. I've found it's good to also filter the vulnerability findings by `entityType: 'CLOUD_RESOURCE'` in the join subquery, if you only want findings attached directly to the VM or container. Otherwise you'll pull in image-level stuff that's not as urgent. Still a solid starting query though!
You've hit on a core challenge with these kinds of joins in security platforms. That query will run, but as the later comments hint, the join logic `on resourceId = resource_id` is a potential source of significant noise. In Wiz's model, a `resource_id` from a vulnerability finding can point to several entity types.
If your goal is truly "actionable," you should filter the vulnerability findings side to those directly attached to the cloud resource. You can do this by adding a filter within the join subquery. Try modifying it to something like:
```sql
join (
vulnerability_findings: [
{severity: 'CRITICAL'},
{severity: 'HIGH'}
]
| where entityType in ['CLOUD_RESOURCE', 'VIRTUAL_MACHINE']
) on resourceId = resource_id
```
This reduces the result set to vulnerabilities pinned directly to the VM or container, not its underlying image, which often requires a different remediation workflow. The name-based exclusion for services like CloudFront is a start, but tagging is indeed better for scale.
Extract, transform, trust
Great point about filtering by `entityType`. That's exactly the kind of nuance that saves a team from chasing down a hundred image vulnerabilities when they need to patch a live host.
I'd add that even with `VIRTUAL_MACHINE`, you might still pull in findings for a VM's *template* in Azure (like a Marketplace image). The remediation path is still different. It's often good to pair this with a look at the `status` field to filter for "OPEN" or "IN_PROGRESS" findings, otherwise your list gets cluttered with things already being worked on.
The tagging suggestion is spot on. We ended up creating a tag called `Exposure:Public` with values 'intended' or 'review-needed'. That way the query can filter out the safe stuff without guessing at names.
cost first, then scale
Absolutely. Your tag `Exposure:Public` is the real pro-tip here. We did something similar, but it took a noisy, recurring incident before we operationalized it.
We now have an automated process that applies a `public_ip_status: investigated` tag after the first review. The query then filters those out, so we're only ever looking at *new* or *changed* public IPs. It cut our alert volume by about 70%.
Your point about `status` is also crucial for actionability. We route anything with `status: 'OPEN'` and `severity: 'CRITICAL'` directly into a Jira ticket. The "IN_PROGRESS" or "RESOLVED" items get suppressed from our daily report, otherwise the signal gets drowned out by old news.
pagerduty certified lifer