/*
 * SQL - Event Count by Tool 14 DAYS
 *
 * Data Source: Infrastructure
 * View: SQL Report
 * 2020-03-26 17:20:55
 */

SELECT 
    SUM("EVENT COUNT") AS "Event Count",
    "Tool" AS "Tool",
    "DATE" AS "Date"
FROM 
(SELECT DISTINCT
   COUNT(DISTINCT("EVENT_INFO_F"."MC_UEID")) AS "EVENT COUNT",
   CASE
      WHEN ("EVENT_REPORTER_D"."MC_TOOL" LIKE '%:3181' OR
            "EVENT_REPORTER_D"."MC_TOOL" LIKE 'ALARM%') THEN 'PATROL'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'ATM%' THEN 'ATM'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'DELL_EMC%' THEN 'DELL_EMC'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'DT_MANAGED%' THEN 'DT_MANAGED'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'DYNATRACE%' THEN 'DYNATRACE'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'EMAIL%' THEN 'EMAIL'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'EMC_DPA%' THEN 'EMC_DPA'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'Eye of the Storm%' THEN 'ENTUITY'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'ITCAM%' THEN 'ITCAM'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'LOGINSIGHT-MNEM%' THEN 'LOGINSIGHT'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'NECTAR%' THEN 'NECTAR'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'NINTEX%' THEN 'NINTEX'
      WHEN ("EVENT_REPORTER_D"."MC_TOOL" LIKE 'oci_%' OR
            "EVENT_REPORTER_D"."MC_TOOL" LIKE 'OnCommand%') THEN 'OCI'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'OEM%' THEN 'OEM'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'OPSMVS%' THEN 'OPSMVS'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'PROXY_CONVERGED_%' THEN 'PROXY_CONVERGED'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'REMOTE_PROXY_LINUX_%' THEN 'PROXY_LINUX'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'REMOTE_PROXY_Win_%' THEN 'PROXY_WIN'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'SCC_%' THEN 'SCC'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'SCOM%' THEN 'SCOM'
      WHEN "EVENT_REPORTER_D"."MC_TOOL" LIKE 'VRO-MNEM%' THEN 'VRO'
      ELSE "EVENT_REPORTER_D"."MC_TOOL"
   END AS "Tool",
   TRUNC("EVENT_INFO_F"."DATE_RECEPTION") AS "DATE"
   --TO_DATE("EVENT_INFO_F"."DATE_RECEPTION",'MM-DD-YYYY') AS "DATE"
   --EXTRACT('hh',"EVENT_INFO_F"."DATE_RECEPTION") AS "DATE HOUR"

FROM "EVENT_EXTENDED_SLOTS_O"
INNER JOIN "EVENT_D"
ON (
   "EVENT_EXTENDED_SLOTS_O"."BPPM_SERVER_ID" = "EVENT_D"."BPPM_SERVER_ID"
   AND "EVENT_EXTENDED_SLOTS_O"."MC_UEID" = "EVENT_D"."MC_UEID"
)
INNER JOIN "EVENT_INFO_F"
ON (
   "EVENT_D"."MC_UEID" = "EVENT_INFO_F"."MC_UEID"
   AND "EVENT_D"."BPPM_SERVER_ID" = "EVENT_INFO_F"."BPPM_SERVER_ID"
)
INNER JOIN "EVENT_CLASS_D"
ON (
   "EVENT_INFO_F"."BPPM_SERVER_ID" = "EVENT_CLASS_D"."BPPM_SERVER_ID"
   AND "EVENT_INFO_F"."EVENT_CLASS_KEY" = "EVENT_CLASS_D"."EVENT_CLASS_KEY"
)
LEFT OUTER JOIN "EVENT_REPORTER_D"
ON (
   "EVENT_INFO_F"."BPPM_SERVER_ID" = "EVENT_REPORTER_D"."BPPM_SERVER_ID"
   AND "EVENT_INFO_F"."TOOL_KEY" = "EVENT_REPORTER_D"."TOOL_KEY"
)
WHERE (
   "EVENT_INFO_F"."DATE_RECEPTION" >= trunc(sysdate)-14 AND
       (
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'ALARM%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'ATM%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'DELL_EMC%' OR 
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'DT_MANAGED%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'DYNATRACE%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'EMAIL%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'EMC_DPA%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'Eye of the Storm%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'ITCAM%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE '%:3181' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'LOGINSIGHT-MNEM%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'NECTAR%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'NINTEX%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'oci_%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'OEM%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'OnCommand%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'OPSMVS%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'PROXY_CONVERGED_%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'REMOTE_PROXY_LINUX_%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'REMOTE_PROXY_Win_%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'SCC_%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'SCOM%' OR
        "EVENT_REPORTER_D"."MC_TOOL" LIKE 'VRO-MNEM%'
      )
     )
GROUP BY 
    "EVENT_REPORTER_D"."MC_TOOL",
     TRUNC("EVENT_INFO_F"."DATE_RECEPTION")
) 
GROUP BY 
    "Tool",
    "DATE"

ORDER BY 
    "DATE"