Is there any utlity in EM to view GAS alerts ?

sharas · Post by **sharas** » 22 Oct 2010 5:18

Hi ,

I am trying create a console that can show all the alerts on server from command prompt, with a script .

This can be used by monitoring (pilotage ) in case GAS is not working.

I would like to know if there is a utility that can get the list of alerts so that we can trim them to how many days worth we want...

Thanks,
Sharas

philmalmaison · Post by **philmalmaison** » 26 Oct 2010 3:19

In case of GAS not working you lost every alerts that are send by control-m server as long as it will be up again.
You can use an sql query to the ALARM table from Control-m/em server,
or
You can scan by ctmlog listmsg that could grep the SHOUT actions.

Regards,
Philmalmaison

sharas · Post by **sharas** » 23 Dec 2010 8:03

Hi Phil,

Yeah , I am trying to hit EM table ALARM, but it does not fetch me the alert time . It only gets the alert date from attribute HOST_TIME .

How can I get the alert time ? Do I have to look at other tables as well ?

I am not finding any table that has alert time ...

Could you please help me out ?

Thanks in Advance,
Sharas.

philmalmaison · Post by **philmalmaison** » 23 Dec 2010 3:24

Hi,
Host_time is a datetime format for the date / time where the alert were generated for the first time.
So it is the date time from the alert.

Regards,
Philmalmaison

sharas · Post by **sharas** » 24 Dec 2010 7:01

Hi Phil,

I have queried HOST_TIME but it gets only DATE. See below example .

SQL> select SERIAL,HOST_TIME,UPD_TIME from ALARM order by SERIAL ;

SERIAL HOST_TIME UPD_TIME
---------- ------------------ ------------------
68968404 24-DEC-10
68968405 24-DEC-10
68968406 24-DEC-10
68968407 24-DEC-10
68968408 24-DEC-10
68968409 24-DEC-10
68968410 24-DEC-10
68968411 24-DEC-10
68968412 24-DEC-10
68968413 24-DEC-10
68968414 24-DEC-10

Do I have to modify anything in SQL ?

Thanks in advance ...

Regards,
Sharas .

rchkevin · Post by **rchkevin** » 24 Dec 2010 7:22

Please use the Query below to get complete detail on the alert.

SELECT ALARM.APPLICATION, ALARM.DATA_CENTER, ALARM.GROUP_NAME, ALARM.HOST_TIME, ALARM.JOB_NAME, ALARM.MEMNAME, ALARM.MESSAGE, ALARM.NODE_ID, ALARM.ORDER_ID, ALARM.USER_ID
FROM EMUSER.ALARM ALARM

sharas · Post by **sharas** » 24 Dec 2010 7:56

Hi Kevin,

My problem is that HOST_TIME attribute gets only alert date like 24-DEC-2010 , But I need alert time as well . You have any idea ?

Thanks in advance,
Sharas .

rchkevin · Post by **rchkevin** » 24 Dec 2010 8:15

Oracle uses default date format when queried. Please try the query below

select to_char(HOST_TIME, 'Dy DD-Mon-YYYY HH24:MI:SS') as "HOST_TIME",SERIAL,UPD_TIME from ALARM
order by SERIAL;

philmalmaison · Post by **philmalmaison** » 27 Dec 2010 3:59

i confirm this is only a format pb
i confirm too, that it's only an oracle solution ... the sybase format is different...
but perhaps the database doesn't matter, you can even calculate a time / date on a datetime format.
Regards,
Philmalmaison

sharas · Post by **sharas** » 28 Dec 2010 7:37

Hi Kevin/Phil,

Yes. Both of you are true .

Kevin,
Your suggestion was very helpful for me and I appreciate your help .
As you said I have selected the HOST_DATE in YYYYMMDDHH24MISS format. The selection part works well now. But when I try to perform a between operation to select alerts of a range ( example: from 20101228000000 to 20101228050000 ), it would throw me an error " ORA-01861: literal does not match format string " .

why is this happening ?

I guess, I have to alter the format of the column HOST_DATE in the session, correct ? how can it be done ?

Thanks in advance,
Sharas

sharas · Post by **sharas** » 28 Dec 2010 9:23

hi,

for your reference . I have copied below the command I ran against database .

SQL> select to_char(HOST_TIME, 'YYYYMMDDHH24MISS') as "HOST_TIME",SERIAL,UPD_TIME from ALARM where HOST_TIME between '20101228' and '20101228' order by SERIAL;
select to_char(HOST_TIME, 'YYYYMMDDHH24MISS') as "HOST_TIME",SERIAL,UPD_TIME from ALARM where HOST_TIME between '20101228' and '20101228' order by SERIAL
*
ERROR at line 1:
ORA-01861: literal does not match format string

Thanks,
Sharas.

rchkevin · Post by **rchkevin** » 28 Dec 2010 11:29

select to_char(HOST_TIME, 'YYYYMMDDHH24MISS') as "HOST_TIME",SERIAL,UPD_TIME from ALARM
where HOST_TIME between to_date('2010122800000','YYYYMMDDHH24MISS') and to_date('20101228235959','YYYYMMDDHH24MISS')
order by SERIAL;

The above query changes the data to date format and then executes. Let me know if it works