115 lines
3.0 KiB
SQL
115 lines
3.0 KiB
SQL
|
|
/*
|
|
## Insert Notification
|
|
*/
|
|
|
|
INSERT INTO SMP_VDI_AOBJECT_NOTIFICATION
|
|
SELECT 'HBILAR',IOT.OBJECT_ID, 1
|
|
FROM SMP_VDI_OBJECT_TABLE iot, SMP_VDE_EVENT e
|
|
WHERE iot.TYPE = 'EVENT'
|
|
AND iot.object_name = e.ID
|
|
-- AND e.is_library = 'Y'
|
|
--AND e.NAME LIKE 'DB01%Node%'
|
|
AND E.TARGET_TYPE = 'oracle_sysman_node'
|
|
AND NOT EXISTS (SELECT 1 FROM SMP_VDI_AOBJECT_NOTIFICATION iao
|
|
WHERE iao.OBJECT_ID = iot.OBJECT_ID
|
|
AND iao.USERNAME = 'HBILAR')
|
|
/
|
|
|
|
INSERT INTO SMP_VDU_PRIVILEGE_TABLE
|
|
SELECT P.PRINCIPAL_ID, 'VIEW', O.OBJECT_ID
|
|
FROM SMP_VDU_OBJECTS_TABLE O, SMP_VDU_PRINCIPALS_TABLE P, SMP_VDE_EVENT E
|
|
WHERE NOT EXISTS (SELECT 1 FROM SMP_VDU_PRIVILEGE_TABLE PRIV
|
|
WHERE PRIV.OBJECT_OID = O.OBJECT_ID
|
|
AND PRIV.PRINCIPAL_OID = P.PRINCIPAL_ID)
|
|
AND O.OBJECT_NAME = E.ID
|
|
AND O.TYPE = 'EVENT'
|
|
AND P.PRINCIPAL_NAME = 'VGUPTA'
|
|
/
|
|
|
|
|
|
|
|
/*
|
|
--- Delete Notification
|
|
*/
|
|
|
|
DELETE FROM SMP_VDI_AOBJECT_NOTIFICATION C WHERE (C.USERNAME, C.OBJECT_ID) IN
|
|
(
|
|
SELECT A.USERNAME, B.OBJECT_ID
|
|
--SELECT DISTINCT A.USERNAME
|
|
FROM SMP_VDI_AOBJECT_NOTIFICATION a, SMP_VDI_OBJECT_TABLE b , SMP_VDE_EVENT e
|
|
WHERE e.ID = b.OBJECT_NAME
|
|
AND b.OBJECT_ID = a.OBJECT_ID
|
|
AND b.TYPE = 'EVENT'
|
|
--AND E.TARGET_TYPE = 'oracle_sysman_database'
|
|
AND UPPER(e.NAME) LIKE 'APP-LNX-01 - HELDESK TOOL'
|
|
AND A.USERNAME LIKE ('%MOBILE%')
|
|
)
|
|
|
|
/*
|
|
List of Notifications Sent
|
|
*/
|
|
|
|
SELECT e.ID
|
|
, e.NAME
|
|
, n.TARGET_NAME
|
|
, n.TIMESTAMP
|
|
, n.ADMINISTRATOR_NAME
|
|
, n.METHOD
|
|
, DECODE(n.OBJECT_STATUS
|
|
, 25, 'Crit'
|
|
, 20 ,'Warn'
|
|
, 18 ,'Error'
|
|
, 15 ,'Clear'
|
|
, 303 ,'NodeDown'
|
|
, n.OBJECT_STATUS) Status
|
|
FROM SMP_VIEW_NOTIFICATION_HISTORY n
|
|
,SMP_VDE_EVENT e
|
|
WHERE n.OBJECT_TYPE = 'EVENT'
|
|
AND e.ID = n.OBJECT_ID
|
|
AND n.TIMESTAMP > SYSDATE - 30
|
|
-- AND e.NAME = 'TIGRIS - CPU'
|
|
-- AND n.ADMINISTRATOR_NAME LIKE '%%'
|
|
-- AND n.TARGET_NAME = 'tigris.eiffel.servista.com'
|
|
ORDER BY n.TIMESTAMP DESC
|
|
|
|
|
|
/*
|
|
Event Log
|
|
*/
|
|
|
|
SELECT e.ID
|
|
, el.EVENT_OCCURRENCE_ID
|
|
, e.NAME
|
|
, ec.TARGET_NAME
|
|
, el.TIMESTAMP
|
|
, DECODE(el.SEVERITY
|
|
, 25, 'Crit'
|
|
, 20 ,'Warn'
|
|
, 18 ,'Error'
|
|
, 15 ,'Clear'
|
|
, 303 ,'NodeDown'
|
|
, el.SEVERITY) Status
|
|
, el.ENTRY
|
|
FROM SMP_VDE_EVENT e
|
|
,SMP_VDE_EVENT_OCCURRENCE ec
|
|
,SMP_VDE_EVENT_LOG el
|
|
WHERE ec.EVENT_ID = e.ID
|
|
AND el.EVENT_OCCURRENCE_ID = ec.EVENT_OCCURRENCE_ID
|
|
AND el.TIMESTAMP > SYSDATE - 30
|
|
-- AND e.NAME = 'TIGRIS - CPU'
|
|
-- AND n.ADMINISTRATOR_NAME LIKE '%%'
|
|
-- AND n.TARGET_NAME = 'tigris.eiffel.servista.com'
|
|
AND NAME LIKE '%DatabaseLimit%'
|
|
ORDER BY el.TIMESTAMP DESC
|
|
|
|
|
|
SELECT * FROM SMP_VDE_EVENT_LOG a
|
|
ORDER BY a.TIMESTAMP DESC
|
|
|
|
|
|
|
|
SELECT * FROM SMP_VDE_EVENT_OCCUR_DETAILS a
|
|
WHERE a.OCCUR_DATE IS NOT NULL
|
|
ORDER BY a.OCCUR_DATE DESC
|