history log - subtotals: jobs per hour

History_Log__jobs_per_hour

-- category: History_Log
-- description: history log - subtotals: jobs per hour

-- change date time to lok for
-- if looking for specific job name or user submitted job, 
-- change as needed.

SELECT DATE(Message_Timestamp) AS Date_Started
       ,HOUR(Message_Timestamp) AS Hour_Started
       ,COUNT(From_Job) AS Jobs_Count
    FROM TABLE (
            Qsys2.History_Log_Info(
                Start_Time => '2020-08-24 00:00:00.000000',
                End_Time   => '2020-08-24 23:59:00.000000')
        ) I
    WHERE Message_Id = ('CPF1124')  -- CPF1124 = Job Start / CPF1164 = Job End
        --    AND from_job LIKE '%/Your_job%'
        --    And From_User = 'Your_User'
    GROUP BY Grouping Sets (
                (
                    HOUR(Message_Timestamp)
                    ,DATE(Message_Timestamp)
                )                            --  Detail
                ,(DATE(Message_Timestamp))   --  subtotal
                ,()                          --  Grand Total
            )
    ORDER BY Date_Started;