EC - Sql - 计划报表发送电源详细报表方法

EC - Sql - 计划报表发送电源详细报表方法

1. 查询报表使用SQL
PGSQL
SELECT
    Resource.NAME AS "Computer Name",
    Resource.DOMAIN_NETBIOS_NAME AS "Domain Name",
    FORMAT(DATEADD(SECOND, PH.DATE / 1000, '19700101'), 'MMM dd, yyyy') AS "Date",
    FORMAT(DATEADD(MILLISECOND, PH.BOOTUP_TIME, '00:00:00'), 'HH:mm') AS "Startup Time",
    FORMAT(DATEADD(MILLISECOND, PH.SHUTDOWN_TIME, '00:00:00'), 'HH:mm') AS "Shutdown Time",
    FORMAT(DATEADD(MILLISECOND, PH.LAST_CONTACT_TIME, '00:00:00'), 'HH:mm') AS "Last Contact Time",
    FORMAT(DATEADD(MILLISECOND,CASE WHEN 0 >= PH.BOOTUP_TIME AND 86399999 <= PH.SHUTDOWN_TIME THEN 86399999 - 0 ELSE CASE
WHEN 0 >= PH.BOOTUP_TIME THEN PH.SHUTDOWN_TIME - 0
ELSE
CASE
WHEN 86399999 <= PH.SHUTDOWN_TIME THEN 86399999 - PH.BOOTUP_TIME
ELSE PH.SHUTDOWN_TIME - PH.BOOTUP_TIME
END
END
END, '00:00:00'), 'HH \h\o\u\r\s mm \m\i\n\s') AS "UpTime"
FROM
Resource
INNER JOIN ManagedComputer ON Resource.RESOURCE_ID = ManagedComputer.RESOURCE_ID
INNER JOIN ComputerPowerEvtHistory AS PH ON Resource.RESOURCE_ID = PH.RESOURCE_ID
WHERE
(0 <= PH.BOOTUP_TIME OR 0 <= PH.SHUTDOWN_TIME)
AND (PH.BOOTUP_TIME <= 86399999 OR PH.SHUTDOWN_TIME <= 86399999)
AND (EVENT_STATUS <> -1)
AND ManagedComputer.MANAGED_STATUS = 61
ORDER BY
Resource.NAME,
PH.DATE;
MSSQL
SELECT
    Resource.NAME AS "Computer Name",
    Resource.DOMAIN_NETBIOS_NAME AS "Domain Name",
    FORMAT(DATEADD(SECOND, PH.DATE / 1000, '19700101'), 'MMM dd, yyyy') AS "Date",
    FORMAT(DATEADD(MILLISECOND, PH.BOOTUP_TIME, '00:00:00'), 'HH:mm') AS "Startup Time",
    FORMAT(DATEADD(MILLISECOND, PH.SHUTDOWN_TIME, '00:00:00'), 'HH:mm') AS "Shutdown Time",
    FORMAT(DATEADD(MILLISECOND, PH.LAST_CONTACT_TIME, '00:00:00'), 'HH:mm') AS "Last Contact Time",
    FORMAT(DATEADD(MILLISECOND,
            CASE
                WHEN 0 >= PH.BOOTUP_TIME AND 86399999 <= PH.SHUTDOWN_TIME THEN 86399999 - 0
                ELSE
                    CASE
                        WHEN 0 >= PH.BOOTUP_TIME THEN PH.SHUTDOWN_TIME - 0
                        ELSE
                            CASE
                                WHEN 86399999 <= PH.SHUTDOWN_TIME THEN 86399999 - PH.BOOTUP_TIME
                                ELSE PH.SHUTDOWN_TIME - PH.BOOTUP_TIME
                            END
                    END
            END, '00:00:00'), 'HH \h\o\u\r\s mm \m\i\n\s') AS "UpTime"
FROM
    Resource
INNER JOIN ManagedComputer ON Resource.RESOURCE_ID = ManagedComputer.RESOURCE_ID
INNER JOIN ComputerPowerEvtHistory AS PH ON Resource.RESOURCE_ID = PH.RESOURCE_ID
WHERE
    (0 <= PH.BOOTUP_TIME OR 0 <= PH.SHUTDOWN_TIME)
    AND (PH.BOOTUP_TIME <= 86399999 OR PH.SHUTDOWN_TIME <= 86399999)
    AND (EVENT_STATUS <> -1)
    AND ManagedComputer.MANAGED_STATUS = 61
ORDER BY
    Resource.NAME,
    PH.DATE;

注意:sql长度过长报错,把多余空格去掉即可。

2. 计划报表发送该查询报表。

    • Related Articles

    • 【OPM】计划报表导出为PDF格式,报表名称异常

      问题 OPM通过邮件发送pdf格式的计划报表,邮件收到后,附件报表名称格式异常。 解决方案 1.编辑OpManager/conf/system_properties.conf文件,最后添加以下属性并保存文件 mail.mime.encodeparameters=false 2.重新启动OpManager服务。
    • 【APM】添加计划报表页面选择业务组 子组无法显示报HTTP Status 400

      报错截图: 解决方法: 备份以下路径的Server.xml文件,再用附件Server.xml文件替换--重启apm--清除浏览器缓存 AppManager_Home\working\apache\tomcat\conf\backup AppManager_Home\working\apache\tomcat\conf
    • ELA-计划报表中的附件是否可直接设置成PDF格式(而非zip包)?

      如下图,在产品设置中可修改报表附件的格式: 如果邮件中包含1个以上的报表,还是统一的zip压缩包;单个报表可以设置成PDF或CSV格式。
    • 【APM】新建计划报表报错(如图)

      报错截图 解决方法: 先分别备份以下两路径下的 server.xml 文件,用附件新的server.xml替换 然后重启APM AppManager_Home\working\apache\tomcat\conf\backup AppManager_Home\working\apache\tomcat\conf
    • EC - 屏幕水印

      EC 已发布屏幕水印内部测试版,可通过如下方式开启: 1. 将EC版本升级到 11.4.2524.01 及以上,许可开启 Endpoint DLP 模块 2. 访问 URL :<Server with port>/webclient#/uems/internal/endpoint-security/endpoint-dlp ,替换<Server with port> 为服务器ip 端口。 3. 启用 "Screen Watermark" 选项,设置水印参数并保存。 水印设置选项说明: ...