How to search the Oracle Database Availability Percentage from the following results?

[rmsit]

Hi all, I am attempting to calculate the availability of an Oracle database using the alert log for a given month, last month to be exact. How can I query the downtime percentage from the following results? I have used the DB Connect to give me the number of days the database has been running since it last shutdown down, but that does not help with calculating for the entire month.

Startup (last startup message)

10/18/15
11:50:55.666 AM  
msg time='2015-10-18T11:50:55.666-04:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksu_setup_oracle_base:28994:2787919602' type='NOTIFICATION' group='startup'
 level='16' host_id='xxx' host_addr='xxx'
 pid='3108'>
 txt>ORACLE_BASE from environment = d:\app\orainstall
 /txt>
/msg>

Shutdown

10/18/15
8:12:06.113 AM  
msg time='2015-10-18T08:12:06.113-04:00' org_id='oracle' comp_id='rdbms'
 msg_id='opistp_real:1938:503144415' type='NOTIFICATION' group='shutdown'
 level='16' host_id='xxx' host_addr='xxx'
 pid='2520'>
 txt>Instance shutdown complete

Thanks

[rambo9]

is this for checking the availibility of oracle ? like if the OEM shuts down or if OEM restarts ?

Please help me in this i am making a dashboard in which whenever OEM shuts down we get the red glass table .

thanks a lot in advance 🙂

[woodcock · Accepted solution]

Like this (might need adjust for field host😞

index=oracle host=* sourcetype="oracle:alert:xml" group="'startup'" OR group="'shutdown'" | reverse | streamstats current=t count(eval(group="'startup'")) AS sessionID by host | eventstats earliest(_time) AS startTime latest(_time) AS endTime count by sessionID host | where group="'startup'" | eval uptimeSeconds= if((count>1), endTime, now()) - startTime | stats sum(uptimeSeconds) AS totalUptimeSeconds earliest(startTime) AS firstTime by host | eval totalSeconds = now() - firstTime | eval pctUp = 100 * totalUptimeSeconds / totalSeconds | table host pctUp

[rmsit]

It works! Thanks so much!

[rmsit]

I forgot to add the search that produces the results above.

index=oracle host=* sourcetype="oracle:alert:xml" group="'startup'" OR group="'shutdown'" | dedup group