Parsing Wiz Issues Old

Query

// https://aka.ms/sentinel-wiz-website-run-from-package
// https://wizio-public.s3.us-east-2.amazonaws.com/deployment-v2/azure/integrations/sentinel/packages/wiz_sentinel_latest.zip
let expected_keys = dynamic([
    "SourceSystem",
    "TenantId",
    "TimeGenerated",
    "Type",
    "createdAt_t",
    "description_s",
    "dueAt_t",
    "entitySnapshot_cloudPlatform_s",
    "entitySnapshot_cloudProviderURL_s",
    "entitySnapshot_externalId_g",
    "entitySnapshot_externalId_s",
    "entitySnapshot_id_g",
    "entitySnapshot_name_s",
    "entitySnapshot_nativeType_s",
    "entitySnapshot_providerId_g",
    "entitySnapshot_providerId_s",
    "entitySnapshot_region_s",
    "entitySnapshot_resourceGroupExternalId_s",
    "entitySnapshot_status_s",
    "entitySnapshot_subscriptionExternalId_g",
    "entitySnapshot_subscriptionExternalId_s",
    "entitySnapshot_subscriptionName_s",
    "entitySnapshot_type_s",
    "id_g",
    "notes_s",
    "openReason_s",
    "projects_s",
    "resolutionReason_s",
    "resolvedAt_t",
    "serviceTickets_s",
    "severity_s",
    "sourceRule___typename_s",
    "sourceRule_id_g",
    "sourceRule_id_s",
    "sourceRule_name_s",
    "sourceRule_resolutionRecommendation_s",
    "sourceRule_sourceType_s",
    "sourceRule_type_s",
    "sourceURL_s",
    "status_s",
    "statusChangedAt_t",
    "updatedAt_t"
]);
WizIssues_CL
//| project-away entitySnapshot_tags_*, entitySnapshot_subscriptionTags_*
| project
    TimeGenerated,
    CreatedAt = column_ifexists("createdAt_t", datetime(null)),
    UpdatedAt = column_ifexists("updatedAt_t", datetime(null)),
    ResolvedAt = column_ifexists("resolvedAt_t", datetime(null)),
    DueAt = column_ifexists("dueAt_t", datetime(null)),
    StatusChangedAt = column_ifexists("statusChangedAt_t", datetime(null)),
    Status = column_ifexists("status_s", ''),
    Severity = column_ifexists("severity_s", ''),
    OpenReason = column_ifexists("openReason_s", ''),
    ResolutionReason = column_ifexists("resolutionReason_s", ''),
    IssueType = column_ifexists("sourceRule___typename_s", ''),
    RuleSourceType = column_ifexists("sourceRule_sourceType_s", ''),
    RuleType = column_ifexists("sourceRule_type_s", ''),
    RuleId = coalesce(tostring(column_ifexists("sourceRule_id_g", guid(null))), column_ifexists("sourceRule_id_s", '')),
    IssueId = column_ifexists("id_g", guid(null)),
    IssueName = column_ifexists("sourceRule_name_s", ''),
    Description = column_ifexists("description_s", ''),
    ResolutionRecommendation = column_ifexists("sourceRule_resolutionRecommendation_s", ''),
    Projects = column_ifexists("projects_s", ''),
    ServiceTickets = column_ifexists("serviceTickets_s", ''),
    Notes = column_ifexists("notes_s", ''),
    IssueUrl = column_ifexists("sourceURL_s", ''),
    EntityType = column_ifexists("entitySnapshot_type_s", ''),
    EntityCloudPlatform = column_ifexists("entitySnapshot_cloudPlatform_s", ''),
    EntityRegion = column_ifexists("entitySnapshot_region_s", ''),
    EntitySubscriptionId = coalesce(tostring(column_ifexists("entitySnapshot_subscriptionExternalId_g", guid(null))), column_ifexists("entitySnapshot_subscriptionExternalId_s", '')),
    EntitySubscriptionName = column_ifexists("entitySnapshot_subscriptionName_s", ''),
    EntityResourceGroup = column_ifexists("entitySnapshot_resourceGroupExternalId_s", ''),
    EntityNativeType = column_ifexists("entitySnapshot_nativeType_s", ''),
    EntityName = column_ifexists("entitySnapshot_name_s", ''),
    EntityStatus = column_ifexists("entitySnapshot_status_s", ''),
    EntityCloudProviderUrl = column_ifexists("entitySnapshot_cloudProviderURL_s", ''),
    EntityExternalId = coalesce(tostring(column_ifexists("entitySnapshot_externalId_g", guid(null))), column_ifexists("entitySnapshot_externalId_s", '')),
    EntityProviderId = coalesce(tostring(column_ifexists("entitySnapshot_providerId_g", guid(null))), column_ifexists("entitySnapshot_providerId_s", '')),
    EntityId = tostring(column_ifexists("entitySnapshot_id_g", guid(null))),
    EntityTags = bag_remove_keys(pack_all(true), expected_keys)

Explanation

This KQL (Kusto Query Language) query is designed to extract and organize data from the WizIssues_CL table, which likely contains information about issues or alerts in a cloud environment. Here's a simplified explanation of what the query does:

Define Expected Keys: A list of expected keys (expected_keys) is defined. These keys represent the fields that are expected to be present in the data.
Select Data Source: The query operates on the WizIssues_CL table, which contains logs or records of issues.
Project Specific Fields: The query selects specific fields from the table to include in the output. It uses the project operator to rename and format these fields:
- Timestamps: Fields like TimeGenerated, CreatedAt, UpdatedAt, ResolvedAt, DueAt, and StatusChangedAt are extracted, with default values set to null if they don't exist.
- Status and Severity: Fields like Status, Severity, OpenReason, and ResolutionReason are included.
- Issue Details: Fields such as IssueType, RuleSourceType, RuleType, RuleId, IssueId, IssueName, Description, ResolutionRecommendation, Projects, ServiceTickets, Notes, and IssueUrl are selected.
- Entity Details: Information about the entity related to the issue is extracted, including EntityType, EntityCloudPlatform, EntityRegion, EntitySubscriptionId, EntitySubscriptionName, EntityResourceGroup, EntityNativeType, EntityName, EntityStatus, EntityCloudProviderUrl, EntityExternalId, EntityProviderId, and EntityId.
Handle Missing Data: The column_ifexists function is used to handle cases where a field might not exist in the data, providing a default value if necessary.
Extract Additional Tags: The EntityTags field is created by removing the expected keys from the complete set of fields, capturing any additional tags that were not explicitly projected.

Overall, this query is designed to cleanly extract and organize relevant information about issues from the WizIssues_CL table, making it easier to analyze and understand the data.

Details

Jose Sebastián Canós

Released: May 6, 2025

Tables

WizIssues_CL

Keywords

WizIssuesDevicesEntityCloudPlatformRegionSubscriptionResourceGroupNativeTypeNameStatusProviderURLExternalIdTags

Operators

letdynamicprojectcolumn_ifexistsdatetimenullcoalescetostringguidbag_remove_keyspack_all

Actions

GitHub

KQL Search