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. 计划报表发送该查询报表。