DROP VIEW IF EXISTS mv_refresh_issue_license_expire; DROP VIEW IF EXISTS mv_tb_node_status_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_av_status_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_patch_status_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_aprm_node_privacy_stat_latest_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_esa_node_weak_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_edr_status_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_edc_status_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_malware_timeline_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_sw_status_to_tb_issue_agent_status; DROP VIEW IF EXISTS mv_tb_node_status_to_tb_issue_agent_status_refresh; CREATE MATERIALIZED VIEW IF NOT EXISTS mv_tb_node_status_to_tb_issue_agent_status_refresh REFRESH EVERY 10 MINUTE TO tb_issue_agent_status AS WITH wt_license_data AS ( SELECT p.product_group as product_group, max(pl.expire_date > now()) as is_license_valid FROM tb_license_compatible as pl JOIN ( SELECT product_group, arrayJoin(license_group) as license_group FROM tb_product ) as p ON pl.license_group = p.license_group GROUP BY p.product_group ), ( SELECT is_license_valid FROM wt_license_data WHERE product_group = 'AV' ) AS is_av_license_exists, ( SELECT is_license_valid FROM wt_license_data WHERE product_group = 'EDR' ) AS is_edr_license_exists, ( SELECT is_license_valid FROM wt_license_data WHERE product_group = 'APM' ) AS is_epm_license_exists, ( SELECT is_license_valid FROM wt_license_data WHERE product_group = 'APRM' ) AS is_eprm_license_exists, ( SELECT is_license_valid FROM wt_license_data WHERE product_group = 'ESA' ) AS is_esa_license_exists, ( SELECT is_license_valid FROM wt_license_data WHERE product_group = 'EDC' ) AS is_edc_license_exists, (SELECT toUInt32(value) as engine_update_threshold FROM tb_config WHERE config_group = 'AGENT_THRESHOLD' AND key = 'engine_update_threshold' LIMIT 1) AS update_threshold, (SELECT subtractDays(toDate(parseDateTime32BestEffortOrZero(substring(value, 1, 10))), update_threshold) as oes_engine_date FROM tb_config WHERE config_group = 'UPDATE_SERVICE' AND key = 'oes_engine_date' LIMIT 1 ) AS oes_engine_date, (SELECT subtractDays(toDate(parseDateTime32BestEffortOrZero(substring(value, 1, 10))), update_threshold) as ses_engine_date FROM tb_config WHERE config_group = 'UPDATE_SERVICE' AND key = 'ses_engine_date' LIMIT 1) AS ses_engine_date, (SELECT subtractDays(now(), toUInt32(value)) as manual_scan_date FROM tb_config WHERE config_group = 'AGENT_THRESHOLD' AND key = 'manual_scan_threshold' LIMIT 1) AS manual_scan_threshold SELECT toUnixTimestamp64Milli(now64()) as server_time, ns.node_id AS node_id, n.group_id AS group_id, pi.is_av_install_target AS is_av_install_target, if(p.is_server AND is_av_install_target AND NOT pi.is_av_installed, true, false) AS av_not_install_server, if(NOT is_server AND is_av_install_target AND NOT pi.is_av_installed, true, false) AS av_not_install_desktop, if((is_av_license_exists AND pi.is_av_installed AND NOT av.node_id = 0) AND ((av.manual_scan_time IS NULL) OR (av.manual_scan_time < manual_scan_threshold)), true, false) AS av_not_manual_scan, toDate(addHours(av.manual_scan_time, av.tz_offset)) AS last_scan_time, if(pi.is_av_installed AND NOT(p.platform_id = 'UNIX' AND av.sysmon_mode = 'UNUSED') , av.is_realtime_scan_off, false) AS av_realtime_scan_off, multiIf(NOT is_av_license_exists OR NOT pi.is_av_installed OR av.node_id = 0, false, (av.engine_type = 'UNKNOWN') OR (parseDateTime32BestEffortOrNull(substring(av.engine_version, 1, 10)) IS NULL), true, (av.engine_type = 'OES') AND parseDateTime32BestEffortOrNull(substring(av.engine_version, 1, 10)) < oes_engine_date, true, (av.engine_type = 'SES') AND parseDateTime32BestEffortOrNull(substring(av.engine_version, 1, 10)) < ses_engine_date, true, false) AS av_not_new_engine, av.engine_version AS engine_version, if((is_av_license_exists AND pi.is_av_installed) AND pd.is_av_policy_not_applied, true, false) AS av_policy_not_applied, if(pi.is_av_installed AND (mt.malware_clean_count > 0 OR mt.malware_clean_fail_count > 0), true, false) AS av_malware_scan_30_days, if(pi.is_av_installed, mt.malware_clean_count + mt.malware_clean_fail_count, 0) AS malware_scan_30_days, if((pi.is_av_installed AND NOT is_av_license_exists) OR ((is_av_license_exists AND pi.is_av_installed) AND (av.license_expire_date IS NOT NULL) AND (av.license_expire_date < now())), true, false) AS av_license_expired, if(pi.is_av_installed, av.is_abnormal_integrity, false) AS av_abnormal_integrity, pi.is_edr_install_target AS is_edr_install_target, if((is_edr_license_exists AND is_edr_install_target) AND NOT pi.is_edr_installed, true, false) AS edr_not_install, if((pi.is_edr_installed AND NOT is_edr_license_exists) OR ((is_edr_license_exists AND pi.is_edr_installed) AND (edr.license_expire_date IS NOT NULL) AND (edr.license_expire_date < now())), true, false) AS edr_license_expired, if(pi.is_edr_installed, edr.is_abnormal_integrity, false) AS edr_abnormal_integrity, if((is_edr_license_exists AND pi.is_edr_installed) AND pd.is_edr_policy_not_applied, true, false) AS edr_policy_not_applied, pi.is_apm_install_target AS is_apm_install_target, if((is_epm_license_exists AND is_apm_install_target) AND NOT pi.is_apm_installed, true, false) AS apm_not_install, if((is_epm_license_exists AND pi.is_apm_installed) AND epm.is_total_patch_ratio, true, false) AS apm_total_patch_ratio, if((is_epm_license_exists AND pi.is_apm_installed) AND epm.is_recommended_patch_ratio, true, false) AS apm_recommended_patch_ratio, epm.total_patch_ratio AS total_patch_ratio, epm.total_recommended_patch_ratio AS total_recommended_patch_ratio, if((is_epm_license_exists AND pi.is_apm_installed) AND epm.is_recommended_sw_not_install, true, false) AS apm_recommended_sw_not_install, if((is_epm_license_exists AND pi.is_apm_installed) AND (sw.installed_prohibited_count > 0), true, false) AS apm_prohibited_sw_install, sw.uninstalled_recommended_count AS recommended_sw_not_install, sw.installed_prohibited_count AS prohibited_sw_install, if((is_epm_license_exists AND pi.is_apm_installed) AND pd.is_epm_policy_not_applied, true, false) AS apm_policy_not_applied, if((pi.is_apm_installed AND NOT is_epm_license_exists) OR ((is_epm_license_exists AND pi.is_apm_installed) AND (epm.license_expire_date IS NOT NULL) AND (epm.license_expire_date < now())), true, false) AS apm_license_expired, if(pi.is_apm_installed, epm.is_abnormal_integrity, false) AS apm_abnormal_integrity, pi.is_aprm_install_target AS is_aprm_install_target, if((is_eprm_license_exists AND is_aprm_install_target) AND NOT pi.is_aprm_installed, true, false) AS aprm_not_install, eprm.is_privacy_file_detect AS aprm_privacy_file_detect, eprm.is_privacy_info_detect AS aprm_privacy_info_detect, eprm.critical_unhandled_file_count + eprm.warning_unhandled_file_count + eprm.attention_unhandled_file_count AS privacy_file_detect, eprm.critical_net_detect_count + eprm.critical_print_detect_count + eprm.critical_usb_detect_count + eprm.critical_share_detect_count + eprm.warning_net_detect_count + eprm.warning_print_detect_count + eprm.warning_usb_detect_count + eprm.warning_share_detect_count + eprm.attention_net_detect_count + eprm.attention_print_detect_count + eprm.attention_usb_detect_count + eprm.attention_share_detect_count AS privacy_info_detect, if((pi.is_aprm_installed AND NOT is_eprm_license_exists) OR ((is_eprm_license_exists AND pi.is_aprm_installed) AND (eprm.license_expire_date IS NOT NULL) AND (eprm.license_expire_date < now())), true, false) AS aprm_license_expired, eprm.is_abnormal_integrity AS aprm_abnormal_integrity, if(is_eprm_license_exists AND pi.is_aprm_installed AND pd.is_eprm_policy_not_applied, true, false) AS aprm_policy_not_applied, pi.is_esa_install_target AS is_esa_install_target, if((is_esa_license_exists AND is_esa_install_target) AND NOT pi.is_esa_installed, true, false) AS esa_not_install, if(pi.is_esa_installed, esa.is_esa_node_weak_status, false) AS esa_node_weak_status, esa.grade AS esa_grade, if(pi.is_esa_installed, esa.is_esa_survey_not_done, false) AS esa_survey_not_done, sv.survey_count AS esa_survey_not_completed_count, if((pi.is_esa_installed AND NOT is_esa_license_exists) OR ((is_esa_license_exists AND pi.is_esa_installed) AND (esa.license_expire_date IS NOT NULL) AND (esa.license_expire_date < now())), true, false) AS esa_license_expired, if(pi.is_esa_installed, esa.is_abnormal_integrity, false) AS esa_abnormal_integrity, if((is_esa_license_exists AND pi.is_esa_installed) AND pd.is_esa_policy_not_applied, true, false) AS esa_policy_not_applied, if(ns.integrity_status = 'ABNORMAL', true, false) AS epp_abnormal_integrity, pi.is_edc_install_target AS is_edc_install_target, if((is_edc_license_exists AND is_edc_install_target) AND NOT pi.is_edc_installed, true, false) AS edc_not_install, if(is_edc_license_exists AND pi.is_edc_installed AND pd.is_edc_policy_not_applied, true, false) AS edc_policy_not_applied, if((pi.is_edc_installed AND NOT is_edc_license_exists) OR ((is_edc_license_exists AND pi.is_edc_installed) AND (edc.license_expire_date IS NOT NULL) AND (edc.license_expire_date < now())), true, false) AS edc_license_expired, if(pi.is_edc_installed, edc.is_abnormal_integrity, false) AS edc_abnormal_integrity FROM tb_node_status_latest AS ns JOIN tb_node AS n ON ns.node_id = n.node_id AND NOT n.is_removed JOIN tb_platform AS p ON p.platform_id = n.platform_id LEFT JOIN ( SELECT node_id, max(if(product_group='EMS' AND policy_status_code = 3, true, false)) AS is_epp_policy_not_applied, max(if(product_group='LUS' AND policy_status_code = 3, true, false)) AS is_lus_policy_not_applied, max(if(product_group='APRM' AND policy_status_code = 3, true, false)) AS is_eprm_policy_not_applied, max(if(product_group='APM' AND policy_status_code = 3, true, false)) AS is_epm_policy_not_applied, max(if(product_group='EDR' AND policy_status_code = 3, true, false)) AS is_edr_policy_not_applied, max(if(product_group='EDC' AND policy_status_code = 3, true, false)) AS is_edc_policy_not_applied, max(if(product_group='ESA' AND policy_status_code = 3, true, false)) AS is_esa_policy_not_applied, max(if(product_group='AV' AND policy_status_code = 3, true, false)) AS is_av_policy_not_applied FROM ( SELECT ps.node_id AS node_id, if(product_id='LUS_CLIENT', 'LUS', product_group) as product_group, max(multiIf(ps.product_feature_id != 'AGENT_MGMT' AND NOT pi.is_installed, 0, policy_status = 'NOT_APPLIED', 3, policy_status = 'APPLYING', 2, 1)) AS policy_status_code FROM tb_node_policy_status as ps FINAL JOIN tb_node_product_install as pi final ON ps.node_id = pi.node_id and ps.product_id = pi.product_id AND (pi.is_install_target OR pi.is_installed) WHERE product_feature_id NOT IN('V3NET_LU_UPDATE', 'V3DTL_UPDATE', 'RAV_LINUX_UPDATE') GROUP BY node_id, product_group ) GROUP BY node_id ) AS pd ON pd.node_id = n.node_id JOIN ( SELECT node_id, max(if(product_group = 'AV' AND is_installed, true, false)) as is_av_installed, max(if(product_group = 'EDC' AND is_installed, true, false)) as is_edc_installed, max(if(product_group = 'EDR' AND is_installed, true, false)) as is_edr_installed, max(if(product_group = 'APM' AND is_installed, true, false)) as is_apm_installed, max(if(product_group = 'APRM' AND is_installed, true, false)) as is_aprm_installed, max(if(product_group = 'ESA' AND is_installed, true, false)) as is_esa_installed, max(if(product_group = 'AV' AND (is_install_target AND NOT is_unwanted), true, false)) AS is_av_install_target, max(if(product_group = 'EDC' AND (is_install_target AND NOT is_unwanted), true, false)) AS is_edc_install_target, max(if(product_group = 'EDR' AND (is_install_target AND NOT is_unwanted), true, false)) AS is_edr_install_target, max(if(product_group = 'APM' AND (is_install_target AND NOT is_unwanted), true, false)) AS is_apm_install_target, max(if(product_group = 'APRM' AND (is_install_target AND NOT is_unwanted), true, false)) AS is_aprm_install_target, max(if(product_group = 'ESA' AND (is_install_target AND NOT is_unwanted), true, false)) AS is_esa_install_target FROM tb_node_product_install final GROUP BY node_id ) AS pi ON pi.node_id = n.node_id LEFT JOIN tb_av_status_latest AS av FINAL ON ns.node_id = av.node_id LEFT JOIN ( SELECT mt.node_id AS node_id, sum(malware_clean_count) AS malware_clean_count, sum(malware_clean_fail_count) AS malware_clean_fail_count FROM tb_av_malware_timeline AS mt WHERE toDateTime(fromUnixTimestamp64Milli(server_time)) >= subtractDays(now(), 30) GROUP BY mt.node_id ) AS mt ON n.node_id = mt.node_id LEFT JOIN tb_apm_node_patch_stat_latest AS epm FINAL ON n.node_id = epm.node_id LEFT JOIN tb_apm_node_sw_stat_latest AS sw FINAL ON n.node_id = sw.node_id LEFT JOIN tb_aprm_node_privacy_stat_latest AS eprm FINAL ON n.node_id = eprm.node_id LEFT JOIN tb_esa_node_weak_latest AS esa FINAL ON n.node_id = esa.node_id LEFT JOIN ( SELECT node_id, count() AS survey_count FROM pg_esa_survey AS es JOIN pg_esa_node_survey AS ens ON ((now() >= es.start_time) AND (now() <= es.end_time)) AND (es.survey_id = ens.survey_id) AND (ens.confirm_status != 'DONE') GROUP BY ens.node_id ) AS sv ON n.node_id = sv.node_id LEFT JOIN tb_edr_status_latest AS edr FINAL ON n.node_id = edr.node_id LEFT JOIN tb_edc_status_latest AS edc FINAL ON n.node_id = edc.node_id;