langchao_fayuan/views/v_bi_alm_all_alarm_day.sql

-- lawe.v_bi_alm_all_alarm_day source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `v_bi_alm_all_alarm_day` AS
select
    `m`.`alarm_object_ip` AS `alarm_object_ip`,
    `m`.`alarm_object_id` AS `alarm_object_id`,
    (case
        when (`m`.`alarm_object_type` = '000100001') then '物理机'
        when (`m`.`alarm_object_type` = '000100002') then '云主机'
        when (`m`.`alarm_object_type` = '000100004') then '宿主机'
        when (substr(`m`.`alarm_object_type`, 1, 4) = '0001') then '其他主机'
        when (`m`.`alarm_object_type` = '000200001') then '交换机'
        when (`m`.`alarm_object_type` = '000200002') then '路由器'
        when (`m`.`alarm_object_type` = '000300001') then '存储设备'
        when (substr(`m`.`alarm_object_type`, 1, 4) = '0004') then '安全设备'
        when (substr(`m`.`alarm_object_type`, 1, 4) = '0014') then '网络'
        when ((`m`.`alarm_object_type` like '%0012%')
        or (`m`.`alarm_object_type` like '%app%')) then '应用'
        else '其它未知类型'
    end) AS `alarm_object_type`,
    `m`.`alarm_object_name` AS `alarm_object_name`,
    `m`.`severity_id` AS `severity_id`,
    `m`.`title` AS `title`,
    `m`.`alarm_text` AS `alarm_text`,
    `m`.`clr_status` AS `clr_status`,
    `m`.`alarm_count` AS `alarm_count`,
    `m`.`bg_time` AS `bg_time`,
    `m`.`ed_time` AS `ed_time`,
    `m`.`days` AS `days`,
    `m`.`dtype` AS `dtype`,
    `m`.`thedvalue` AS `thedvalue`,
    `m`.`is_active_alarm` AS `is_active_alarm`,
    floor((`m`.`alarm_count` / `m`.`days`)) AS `avg_alarm_count`,
    if((`m`.`alarm_source` = '性能告警'),
    ((60 / 5) * 24),
    ((60 * 2) * 24)) AS `collection_frequency`,
    (case
        when ((`m`.`occur_time` <= `m`.`thedvalue_b`)
        and (`m`.`update_time` >= `m`.`thedvalue_e`)) then (60 * 24)
        when ((`m`.`occur_time` > `m`.`thedvalue_b`)
        and (`m`.`update_time` >= `m`.`thedvalue_e`)) then ((60 * 24) - (`m`.`occur_time` % 100))
        when ((`m`.`occur_time` <= `m`.`thedvalue_b`)
        and (`m`.`update_time` < `m`.`thedvalue_e`)) then (`m`.`update_time` % 100)
        when ((`m`.`occur_time` > `m`.`thedvalue_b`)
        and (`m`.`update_time` < `m`.`thedvalue_e`)) then ((`m`.`update_time` % 100) - (`m`.`occur_time` % 100))
    end) AS `alarm_duration`
from
    (
    select
        `aa`.`alarm_object_ip` AS `alarm_object_ip`,
        `aa`.`alarm_object_id` AS `alarm_object_id`,
        `aa`.`alarm_object_type` AS `alarm_object_type`,
        `aa`.`alarm_object_name` AS `alarm_object_name`,
        `aa`.`severity_id` AS `severity_id`,
        `aa`.`title` AS `title`,
        `aa`.`alarm_text` AS `alarm_text`,
        `aa`.`clr_status` AS `clr_status`,
        `aa`.`alarm_count` AS `alarm_count`,
        cast(date_format(`aa`.`occur_time`, '%Y%m%d') as unsigned) AS `bg_time`,
        cast(date_format(`aa`.`update_time`, '%Y%m%d') as unsigned) AS `ed_time`,
        (abs((to_days(`aa`.`update_time`) - to_days(`aa`.`occur_time`))) + 1) AS `days`,
        'DAY' AS `dtype`,
        `dates`.`YYYYMMDD` AS `thedvalue`,
        1 AS `is_active_alarm`,
        `aa`.`alarm_source` AS `alarm_source`,
        date_format(str_to_date(`dates`.`YYYYMMDD`, '%Y%m%d%H%m'), '%Y%m%d%H00') AS `thedvalue_b`,
        date_format((str_to_date(`dates`.`YYYYMMDD`, '%Y%m%d%H%m') + interval 1 day), '%Y%m%d%H00') AS `thedvalue_e`,
        date_format(`aa`.`occur_time`, '%Y%m%d%H%m') AS `occur_time`,
        date_format(`aa`.`update_time`, '%Y%m%d%H%m') AS `update_time`
    from
        (`t_alm_active_alarm` `aa`
    join (
        select
            `dd`.`YYYYMMDD` AS `YYYYMMDD`
        from
            `dim_date` `dd`
        where
            (`dd`.`YYYYMMDD` between 20230101 and 20241231)) `dates`)
union all
    select
        `ha`.`alarm_object_ip` AS `alarm_object_ip`,
        `ha`.`alarm_object_id` AS `alarm_object_id`,
        `ha`.`alarm_object_type` AS `alarm_object_type`,
        `ha`.`alarm_object_name` AS `alarm_object_name`,
        `ha`.`severity_id` AS `severity_id`,
        `ha`.`title` AS `title`,
        `ha`.`alarm_text` AS `alarm_text`,
        `ha`.`clr_status` AS `clr_status`,
        `ha`.`alarm_count` AS `alarm_count`,
        cast(date_format(`ha`.`occur_time`, '%Y%m%d') as unsigned) AS `bg_time`,
        cast(date_format(`ha`.`update_time`, '%Y%m%d') as unsigned) AS `ed_time`,
        (abs((to_days(`ha`.`update_time`) - to_days(`ha`.`occur_time`))) + 1) AS `days`,
        'DAY' AS `dtype`,
        `dates`.`YYYYMMDD` AS `thedvalue`,
        0 AS `is_active_alarm`,
        `ha`.`alarm_source` AS `alarm_source`,
        date_format(str_to_date(`dates`.`YYYYMMDD`, '%Y%m%d%H%m'), '%Y%m%d%H00') AS `thedvalue_b`,
        date_format((str_to_date(`dates`.`YYYYMMDD`, '%Y%m%d%H%m') + interval 1 day), '%Y%m%d%H00') AS `thedvalue_e`,
        date_format(`ha`.`occur_time`, '%Y%m%d%H%m') AS `occur_time`,
        date_format(`ha`.`update_time`, '%Y%m%d%H%m') AS `update_time`
    from
        (`t_alm_history_alarm` `ha`
    join (
        select
            `dd`.`YYYYMMDD` AS `YYYYMMDD`
        from
            `dim_date` `dd`
        where
            (`dd`.`YYYYMMDD` between 20230101 and 20241231)) `dates`)) `m`
where
    (`m`.`thedvalue` between `m`.`bg_time` and `m`.`ed_time`);