KQL Search

Query Details

HomeAI ToolsDevice Query

Parsing Palo Alto Prisma Cloud Alert Logs

Query

// This query can help you to parse Prisma Cloud CSPM API events received in PaloAltoPrismaCloudAlert_CL table.
PaloAltoPrismaCloudAlert_CL
| extend
    alertAdditionalInfo_scannerVersion_s=column_ifexists('alertAdditionalInfo_scannerVersion_s', ''),
    alertRules_s=column_ifexists('alertRules_s', ''),
    alertTime_d=column_ifexists('alertTime_d', real(null)),
    firstSeen_d=column_ifexists('firstSeen_d', real(null)),
    history_s=column_ifexists('history_s', ''),
    id_s=column_ifexists('id_s', ''),
    investigateOptions_alertId_s=column_ifexists('investigateOptions_alertId_s', ''),
    lastSeen_d=column_ifexists('lastSeen_d', real(null)),
    lastUpdated_d=column_ifexists('lastUpdated_d', real(null)),
    metadata_auditAttackTechniques_s=column_ifexists('metadata_auditAttackTechniques_s', ''),
    metadata_auditCount_d=column_ifexists('metadata_auditCount_d', real(null)),
    metadata_auditMessage_s=column_ifexists('metadata_auditMessage_s', ''),
    metadata_auditRuleName_s=column_ifexists('metadata_auditRuleName_s', ''),
    metadata_auditTime_d=column_ifexists('metadata_auditTime_d', real(null)),
    metadata_auditType_s=column_ifexists('metadata_auditType_s', ''),
    metadata_auditUser_s=column_ifexists('metadata_auditUser_s', ''),
    metadata_cveCritical_d=column_ifexists('metadata_cveCritical_d', real(null)),
    metadata_cveHigh_d=column_ifexists('metadata_cveHigh_d', real(null)),
    metadata_cveLow_d=column_ifexists('metadata_cveLow_d', real(null)),
    metadata_cveMedium_d=column_ifexists('metadata_cveMedium_d', real(null)),
    metadata_incidentCategory_s=column_ifexists('metadata_incidentCategory_s', ''),
    metadata_incidentCountUri_s=column_ifexists('metadata_incidentCountUri_s', ''),
    metadata_lastIncidentTime_d=column_ifexists('metadata_lastIncidentTime_d', real(null)),
    metadata_saveSearchId_g=column_ifexists('metadata_saveSearchId_g', ''),
    metadata_source_s=column_ifexists('metadata_source_s', ''),
    policy_complianceMetadata_s=column_ifexists('policy_complianceMetadata_s', ''),
    policy_deleted_b=column_ifexists('policy_deleted_b', bool(null)),
    policy_description_s=column_ifexists('policy_description_s', ''),
    policy_findingTypes_s=column_ifexists('policy_findingTypes_s', ''),
    policy_labels_s=column_ifexists('policy_labels_s', ''),
    policy_lastModifiedBy_s=column_ifexists('policy_lastModifiedBy_s', ''),
    policy_lastModifiedOn_d=column_ifexists('policy_lastModifiedOn_d', real(null)),
    policy_name_s=column_ifexists('policy_name_s', ''),
    policy_policyId_g=column_ifexists('policy_policyId_g', ''),
    policy_policyType_s=column_ifexists('policy_policyType_s', ''),
    policy_recommendation_s=column_ifexists('policy_recommendation_s', ''),
    policy_remediable_b=column_ifexists('policy_remediable_b', bool(null)),
    policy_remediation_cliScriptTemplate_s=column_ifexists('policy_remediation_cliScriptTemplate_s', ''),
    policy_remediation_description_s=column_ifexists('policy_remediation_description_s', ''),
    policy_remediation_impact_s=column_ifexists('policy_remediation_impact_s', ''),
    policy_severity_s=column_ifexists('policy_severity_s', ''),
    policy_systemDefault_b=column_ifexists('policy_systemDefault_b', bool(null)),
    reason_s=column_ifexists('reason_s', ''),
    resource_accountId_g=column_ifexists('resource_accountId_g', ''),
    resource_accountId_s=column_ifexists('resource_accountId_s', ''),
    resource_account_s=column_ifexists('resource_account_s', ''),
    resource_additionalInfo_accessKeyAge_s=column_ifexists('resource_additionalInfo_accessKeyAge_s', ''),
    resource_additionalInfo_clusters_s=column_ifexists('resource_additionalInfo_clusters_s', ''),
    resource_additionalInfo_containers_s=column_ifexists('resource_additionalInfo_containers_s', ''),
    resource_additionalInfo_hosts_s=column_ifexists('resource_additionalInfo_hosts_s', ''),
    resource_additionalInfo_images_s=column_ifexists('resource_additionalInfo_images_s', ''),
    resource_additionalInfo_inactiveSinceTs_s=column_ifexists('resource_additionalInfo_inactiveSinceTs_s', ''),
    resource_additionalInfo_labels_s=column_ifexists('resource_additionalInfo_labels_s', ''),
    resource_additionalInfo_namespaces_s=column_ifexists('resource_additionalInfo_namespaces_s', ''),
    resource_cloudAccountGroups_s=column_ifexists('resource_cloudAccountGroups_s', ''),
    resource_cloudAccountOwners_s=column_ifexists('resource_cloudAccountOwners_s', ''),
    resource_cloudServiceName_s=column_ifexists('resource_cloudServiceName_s', ''),
    resource_cloudType_s=column_ifexists('resource_cloudType_s', ''),
    resource_data_access_key_1_active_b=column_ifexists('resource_data_access_key_1_active_b', bool(null)),
    resource_data_access_key_1_last_rotated_t=column_ifexists('resource_data_access_key_1_last_rotated_t', datetime(null)),
    resource_data_access_key_1_last_used_date_t=column_ifexists('resource_data_access_key_1_last_used_date_t', datetime(null)),
    resource_data_access_key_1_last_used_region_s=column_ifexists('resource_data_access_key_1_last_used_region_s', ''),
    resource_data_access_key_1_last_used_service_s=column_ifexists('resource_data_access_key_1_last_used_service_s', ''),
    resource_data_access_key_2_active_b=column_ifexists('resource_data_access_key_2_active_b', bool(null)),
    resource_data_access_key_2_last_rotated_s=column_ifexists('resource_data_access_key_2_last_rotated_s', ''),
    resource_data_access_key_2_last_used_date_s=column_ifexists('resource_data_access_key_2_last_used_date_s', ''),
    resource_data_access_key_2_last_used_region_s=column_ifexists('resource_data_access_key_2_last_used_region_s', ''),
    resource_data_access_key_2_last_used_service_s=column_ifexists('resource_data_access_key_2_last_used_service_s', ''),
    resource_data_arn_s=column_ifexists('resource_data_arn_s', ''),
    resource_data_cert_1_active_b=column_ifexists('resource_data_cert_1_active_b', bool(null)),
    resource_data_cert_1_last_rotated_s=column_ifexists('resource_data_cert_1_last_rotated_s', ''),
    resource_data_cert_2_active_b=column_ifexists('resource_data_cert_2_active_b', bool(null)),
    resource_data_cert_2_last_rotated_s=column_ifexists('resource_data_cert_2_last_rotated_s', ''),
    resource_data_mfa_active_b=column_ifexists('resource_data_mfa_active_b', bool(null)),
    resource_data_password_enabled_s=column_ifexists('resource_data_password_enabled_s', ''),
    resource_data_password_last_changed_s=column_ifexists('resource_data_password_last_changed_s', ''),
    resource_data_password_last_used_s=column_ifexists('resource_data_password_last_used_s', ''),
    resource_data_password_next_rotation_s=column_ifexists('resource_data_password_next_rotation_s', ''),
    resource_data_user_creation_time_t=column_ifexists('resource_data_user_creation_time_t', datetime(null)),
    resource_data_user_s=column_ifexists('resource_data_user_s', ''),
    resource_id_g=column_ifexists('resource_id_g', ''),
    resource_id_s=column_ifexists('resource_id_s', ''),
    resource_name_s=column_ifexists('resource_name_s', ''),
    resource_regionId_s=column_ifexists('resource_regionId_s', ''),
    resource_region_s=column_ifexists('resource_region_s', ''),
    resource_resourceApiName_s=column_ifexists('resource_resourceApiName_s', ''),
    resource_resourceConfigJsonAvailable_b=column_ifexists('resource_resourceConfigJsonAvailable_b', bool(null)),
    resource_resourceDetailsAvailable_b=column_ifexists('resource_resourceDetailsAvailable_b', bool(null)),
    resource_resourceTs_d=column_ifexists('resource_resourceTs_d', real(null)),
    resource_resourceType_s=column_ifexists('resource_resourceType_s', ''),
    resource_rrn_s=column_ifexists('resource_rrn_s', ''),
    resource_unifiedAssetId_g=column_ifexists('resource_unifiedAssetId_g', ''),
    resource_url_s=column_ifexists('resource_url_s', ''),
    riskDetail_rating_s=column_ifexists('riskDetail_rating_s', ''),
    riskDetail_riskScore_maxScore_d=column_ifexists('riskDetail_riskScore_maxScore_d', real(null)),
    riskDetail_riskScore_score_d=column_ifexists('riskDetail_riskScore_score_d', real(null)),
    riskDetail_score_s=column_ifexists('riskDetail_score_s', ''),
    status_s=column_ifexists('status_s', '')
| project
    TimeGenerated,
    AlertId = id_s,
    InvestigateOptionsAlertId = investigateOptions_alertId_s,
    AlertStatus = status_s,
    AlertReason = reason_s,
    FirstSeen = unixtime_milliseconds_todatetime(firstSeen_d),
    LastSeen = unixtime_milliseconds_todatetime(lastSeen_d),
    LastUpdated = unixtime_milliseconds_todatetime(lastUpdated_d),
    AlertTime = unixtime_milliseconds_todatetime(alertTime_d),
    AlertHistory = history_s,
    AlertRules = alertRules_s,
    ScannerVersion = alertAdditionalInfo_scannerVersion_s,
    PolicyType = policy_policyType_s,
    PolicySeverity = policy_severity_s,
    PolicyName = policy_name_s,
    PolicyDescription = policy_description_s,
    PolicyRecommendation = policy_recommendation_s,
    PolicyFindingTypes = policy_findingTypes_s,
    PolicyCompliance = policy_complianceMetadata_s,
    PolicyRemediable = policy_remediable_b,
    PolicyRemediationScript = policy_remediation_cliScriptTemplate_s,
    PolicyRemediationDescription = policy_remediation_description_s,
    PolicyRemediationImpact = policy_remediation_impact_s,
    PolicyLabels = policy_labels_s,
    PolicyLastModifiedOn = unixtime_milliseconds_todatetime(policy_lastModifiedOn_d),
    PolicyLastModifiedBy = policy_lastModifiedBy_s,
    PolicyId = policy_policyId_g,
    PolicySystemDefault = policy_systemDefault_b,
    PolicyDeleted = policy_deleted_b,
    ResourceCloudType = resource_cloudType_s,
    ResourceCloudService = resource_cloudServiceName_s,
    ResourceType = resource_resourceType_s,
    ResourceApi = resource_resourceApiName_s,
    ResourceName = resource_name_s,
    ResourceId = coalesce(resource_id_s, resource_id_g),
    ResourceRrn = resource_rrn_s,
    ResourceUrl = resource_url_s,
    ResourceAccountName = resource_account_s,
    ResourceAccountId = coalesce(resource_accountId_s, resource_accountId_g),
    ResourceAccountOwner = resource_cloudAccountOwners_s,
    ResouceAccountGroup = resource_cloudAccountGroups_s,
    ResourceRegion = resource_region_s,
    ResourceRegionId = resource_regionId_s,
    ResourceLabels = resource_additionalInfo_labels_s,
    ResourceTimestamp = unixtime_milliseconds_todatetime(resource_resourceTs_d),
    ResourceConfigJsonAvailable = resource_resourceConfigJsonAvailable_b,
    ResourceDetailsAvailable = resource_resourceDetailsAvailable_b,
    ResourceUnifiedAssetId = resource_unifiedAssetId_g,
    ResourceClusters = resource_additionalInfo_clusters_s,
    ResourceContainers = resource_additionalInfo_containers_s,
    ResourceNamespaces = resource_additionalInfo_namespaces_s,
    ResourceHosts = resource_additionalInfo_hosts_s,
    ResourceImages = resource_additionalInfo_images_s,
    SaveSearchId = metadata_saveSearchId_g,
    Source = metadata_source_s,
    CveCritical = metadata_cveCritical_d,
    CveHigh = metadata_cveHigh_d,
    CveMedium = metadata_cveMedium_d,
    CveLow = metadata_cveLow_d,
    AuditTime = unixtime_milliseconds_todatetime(metadata_auditTime_d),
    AuditRuleName = metadata_auditRuleName_s,
    AuditType = metadata_auditType_s,
    AuditCount = metadata_auditCount_d,
    AuditMessage = metadata_auditMessage_s,
    AuditAttackTechniques = metadata_auditAttackTechniques_s,
    AuditUser = metadata_auditUser_s,
    LastIncidentTime = unixtime_milliseconds_todatetime(metadata_lastIncidentTime_d),
    IncidentCategory = metadata_incidentCategory_s,
    IncidentUri = metadata_incidentCountUri_s,
    // Unchecked columns
    ResourceDataMfaActive=resource_data_mfa_active_b,
    ResourceDataCert1Active=resource_data_cert_1_active_b,
    ResourceDataCert2Active=resource_data_cert_2_active_b,
    ResourceDataPasswordEnabled=resource_data_password_enabled_s,
    ResourceDataPasswordLastUsed=resource_data_password_last_used_s,
    ResourceDataUserCreationTime=resource_data_user_creation_time_t,
    ResourceDataAccessKey1Active=resource_data_access_key_1_active_b,
    ResourceDataAccessKey2Active=resource_data_access_key_2_active_b,
    ResourceDataCert1LastRotated=resource_data_cert_1_last_rotated_s,
    ResourceDataCert2LastRotated=resource_data_cert_2_last_rotated_s,
    ResourceDataPasswordLastChanged=resource_data_password_last_changed_s,
    ResourceDataPasswordNextRotation=resource_data_password_next_rotation_s,
    ResourceDataAccessKey1LastRotated=resource_data_access_key_1_last_rotated_t,
    ResourceDataAccessKey2LastRotated=resource_data_access_key_2_last_rotated_s,
    ResourceDataAccessKey1LastUsedDate=resource_data_access_key_1_last_used_date_t,
    ResourceDataAccessKey2LastUsedDate=resource_data_access_key_2_last_used_date_s,
    ResourceDataAccessKey1LastUsedRegion=resource_data_access_key_1_last_used_region_s,
    ResourceDataAccessKey2LastUsedRegion=resource_data_access_key_2_last_used_region_s,
    ResourceDataAccessKey1LastUsedService=resource_data_access_key_1_last_used_service_s,
    ResourceDataAccessKey2LastUsedService=resource_data_access_key_2_last_used_service_s,
    ResourceDataArn=resource_data_arn_s,
    ResourceDataUser=resource_data_user_s,
    ResourceAccessKeyAge=resource_additionalInfo_accessKeyAge_s,
    ResourceInactiveSince=resource_additionalInfo_inactiveSinceTs_s,
    RiskDetailRiskScoreScore=riskDetail_riskScore_score_d,
    RiskDetailRiskScoreMaxScore=riskDetail_riskScore_maxScore_d,
    RiskDetailRating=riskDetail_rating_s,
    RiskDetailScore=riskDetail_score_s

Explanation

This query helps to extract and organize information from Prisma Cloud CSPM API events stored in the PaloAltoPrismaCloudAlert_CL table. It retrieves details such as alert status, reason, time, policy information, resource details, and risk scores for analysis and monitoring purposes.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: February 19, 2024

Tables

PaloAltoPrismaCloudAlert_CL

Keywords

PrismaCloudAlert_CL,TimeGenerated,AlertId,InvestigateOptionsAlertId,AlertStatus,AlertReason,FirstSeen,LastSeen,LastUpdated,AlertTime,AlertHistory,AlertRules,ScannerVersion,PolicyType,PolicySeverity,PolicyName,PolicyDescription,PolicyRecommendation,PolicyFindingTypes,PolicyCompliance,PolicyRemediable,PolicyRemediationScript,PolicyRemediationDescription,PolicyRemediationImpact,PolicyLabels,PolicyLastModifiedOn,PolicyLastModifiedBy,PolicyId,PolicySystemDefault,PolicyDeleted,ResourceCloudType,ResourceCloudService,ResourceType,ResourceApi,ResourceName,ResourceId,ResourceRrn,ResourceUrl,ResourceAccountName,ResourceAccountId,ResourceAccountOwner,ResouceAccountGroup,ResourceRegion,ResourceRegionId,ResourceLabels,ResourceTimestamp,ResourceConfigJsonAvailable,ResourceDetailsAvailable,ResourceUnifiedAssetId,ResourceClusters,ResourceContainers,ResourceNamespaces,ResourceHosts,ResourceImages,SaveSearchId,Source,CveCritical,CveHigh,CveMedium,CveLow,AuditTime,AuditRuleName,AuditType,AuditCount,AuditMessage,AuditAttackTechniques,AuditUser,LastIncidentTime,IncidentCategory,IncidentUri,ResourceDataMfaActive,ResourceDataCert1Active,ResourceDataCert2Active,ResourceDataPasswordEnabled,ResourceDataPasswordLastUsed,ResourceDataUserCreationTime,ResourceDataAccessKey1Active,ResourceDataAccessKey2Active,ResourceDataCert1LastRotated,ResourceDataCert2LastRotated,ResourceDataPasswordLastChanged,ResourceDataPasswordNextRotation,ResourceDataAccessKey1LastRotated,ResourceDataAccessKey2LastRotated,ResourceDataAccessKey1LastUsedDate,ResourceDataAccessKey2LastUsedDate,ResourceDataAccessKey1LastUsedRegion,ResourceDataAccessKey2LastUsedRegion,ResourceDataAccessKey1LastUsedService,ResourceDataAccessKey2LastUsedService,ResourceDataArn,ResourceDataUser,ResourceAccessKeyAge,ResourceInactiveSince,RiskDetailRiskScoreScore,RiskDetailRiskScoreMaxScore,RiskDetailRating,RiskDetailScore.

Operators

extendcolumn_ifexistsprojectunixtime_milliseconds_todatetimecoalesce

Actions