why is this SQL query failing to run?
1st : We JOIN tables/view or sub-query that returns a result-set.

2nd : ON condition is used to get the result matching given condition.
      Note: CROSS JOIN don't need ON condition.

I tried to correct you query. Hope this query will help you out to get desired result.

WITH CTE AS

(SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION 
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION 
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3
)

SELECT gs.h hours, severity."alarmSeverity", COUNT(CTE.eventtime) 
     FROM GENERATE_SERIES(0, 23, 1) gs(h)
     CROSS JOIN (SELECT DISTINCT "alarmSeverity"
                    FROM CTE
                    WHERE "alarmSeverity" != 0
                ) AS severity
LEFT JOIN CTE
ON          extract(hour FROM CTE.eventtime) = gs.h AND CTE."alarmSeverity" = severity."alarmSeverity" 
            AND CTE.eventdate+CTE.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z' 
GROUP BY gs.h, severity."alarmSeverity" 
ORDER BY gs.h, severity."alarmSeverity";


Thanks!