How to find the number of incidents for a particular monitor for a specified time period
Article ID: 159825
Updated On:
Products
Issue/Introduction
Resolution
The following scripts will help to verify that incidents are being processed into the database by specific monitors during a specific period of time.
The first script creates a list of the monitors and their IDs. Use this to find the monitorid of the monitor you are interested in.
1) Log onto SQLPlus as user protect, and run the following script:
set pagesize 100
set linesize 132
column informationmonitorid format 99999
column monitorname format a40
select im.informationmonitorid monitorid, im.monitorname monitorname
from informationmonitor im
/
This will create a list of monitors and their monitorid.
2) Identify the monitorid associated with the monitor you are interested in.
3) While still logged into SQLPlus as user protect, run the following script:
set pagesize 100
set linesize 132
column monitorid format 9999
column incidentcount format 9999
column detect_date format a12
select im.informationmonitorid monitorid, im.monitorname, count(i.incidentid) incidentcount, to_char(detectiondate, 'DD-MON-YYYY') detect_date
from informationmonitor im,
message m,
incident i
where im.informationmonitorid = m.monitorid
and m.monitorid = &monitorid
and m.messageid = i.messageid
and i.detectiondate > sysdate-&days_prior_to_today
group by im.monitorname, im.informationmonitorid, to_char(detectiondate, 'DD-MON-YYYY')
order by 2
/
this script will prompt for the monitorid and the number of days prior to today to start checking for incidents in the database.
for example, if the requirement is for incidents that came through the monitor called "Single Tier Nakota" during the past 20 days (up to and including today), the first script would be used to get the monitorid for "Single Tier Nakota":
SQL> set pagesize 100
SQL> set linesize 132
SQL> column informationmonitorid format 99999
SQL> column monitorname format a40
SQL> select im.informationmonitorid monitorid, im.monitorname monitorname
2 from informationmonitor im
3 /
MONITORID MONITORNAME
--------- ----------------------------------------
61 Server 1
1 Nakota_monitor
3 Nakota_endpoint
4 Nakota_Prevent
21 Arikara (Network Monitor and Discover)
41 Single Tier Nakota
2 Nakota_Discover
7 rows selected.
The second script prompts for the monitorid and the days_prior_to_today:
SQL> set pagesize 100
SQL> set linesize 132
SQL> column monitorid format 9999
SQL> column incidentcount format 9999
SQL> column detect_date format a12
SQL> select im.informationmonitorid monitorid, im.monitorname, count(i.incidenti
d) incidentcount, to_char(detectiondate, 'DD-MON-YYYY') detect_date
2 from informationmonitor im,
3 message m,
4 incident i
5 where im.informationmonitorid = m.monitorid
6 and m.monitorid = &monitorid
7 and m.messageid = i.messageid
8 and i.detectiondate > sysdate-&days_prior_to_today
9 group by im.monitorname, im.informationmonitorid, to_char(detectiondate, 'D
D-MON-YYYY')
10 order by 2
11 /
Enter value for monitorid: 41
old 6: and m.monitorid = &monitorid
new 6: and m.monitorid = 41
Enter value for days_prior_to_today: 20
old 8: and i.detectiondate > sysdate-&days_prior_to_today
new 8: and i.detectiondate > sysdate-20
MONITORID MONITORNAME INCIDENTCOUNT DETECT_DATE
--------- ---------------------------------------- ------------- ------------
41 Single Tier Nakota 7 16-AUG-2010
41 Single Tier Nakota 5 18-AUG-2010
41 Single Tier Nakota 9 19-AUG-2010
41 Single Tier Nakota 5 30-AUG-2010
This provides a list of incident counts as captured by monitor "Single Tier Nakota", for the last 20 days, broken out by date of detection.
Feedback
