1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
|
SELECT Serveurs.server_name, TotalJob.TOTALJOB, TotalClient.TOTALCLIENT, TotalFullOK.TOTALFULLOK, TotalFullKO.TOTALFULLKO, TotalIncrOK.TOTALINCROK, TotalIncrKO.TOTALINCRKO
FROM (
SELECT DISTINCT server_name
FROM crp_tb_job
) AS Serveurs
LEFT JOIN (
SELECT count( * ) AS TOTALJOB, server_name
FROM crp_tb_job
WHERE job_state =3
GROUP BY server_name
)TotalJob ON TotalJob.server_name = Serveurs.server_name
LEFT JOIN (
SELECT count( DISTINCT Client_name ) AS TOTALCLIENT, server_name
FROM crp_tb_job
GROUP BY server_name
)TotalClient ON TotalClient.server_name = Serveurs.server_name
LEFT JOIN (
SELECT count( DISTINCT job_type_id ) AS TOTALFULLOK, server_name
FROM crp_tb_job
WHERE job_type_id =121
AND job_alarm =0
GROUP BY server_name
)TotalFullOK ON TotalFullOK.server_name = Serveurs.server_name
LEFT JOIN (
SELECT count( DISTINCT job_type_id ) AS TOTALFULLKO, server_name
FROM crp_tb_job
WHERE job_type_id =121
AND job_alarm = (1,2,3)
GROUP BY server_name
)TotalFullKO ON TotalFullKO.server_name = Serveurs.server_name
LEFT JOIN (
SELECT count( DISTINCT job_type_id ) AS TOTALINCROK, server_name
FROM crp_tb_job
WHERE job_type_id =122
AND job_alarm =0
GROUP BY server_name
)TotalIncrOK ON TotalIncrOK.server_name = Serveurs.server_name
LEFT JOIN (
SELECT count( DISTINCT job_type_id ) AS TOTALFULLOK, server_name
FROM crp_tb_job
WHERE job_type_id =122
AND job_alarm =(1,2,3)
GROUP BY server_name
)TotalIncrKO ON TotalIncrKO.server_name = Serveurs.server_name |
Partager