Logons by service name
From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 17 Apr 2020 11:18:20 -0500
Message-ID: <CAJvnOJbJZsBfSNc0sh584c7_wAgYNuudEQUXXLSOyqmhAbL_1g_at_mail.gmail.com>
I need to report new logons by service name, and the output makes me suspect I am doing something wrong. Here is the query I designed, can anyone tell me if I have a logic problem with the query, and if so what I need to do:
and h1.snap_id=h.snap_id+1
group by h.snap_id, h.instance_number, to_char(s.begin_interval_time,'dd-MON-yyyy hh24mi'),h.service_name, h.value order by h.service_name, h.instance_Number, begin_time;
Date: Fri, 17 Apr 2020 11:18:20 -0500
Message-ID: <CAJvnOJbJZsBfSNc0sh584c7_wAgYNuudEQUXXLSOyqmhAbL_1g_at_mail.gmail.com>
I need to report new logons by service name, and the output makes me suspect I am doing something wrong. Here is the query I designed, can anyone tell me if I have a logic problem with the query, and if so what I need to do:
select h.snap_id, h.instance_number,
to_char(s.begin_interval_time,'dd-MON-yyyy hh24mi') begin_time,
h.service_name, h.value, sum(h1.value)-sum(h.value) new_logons
from dba_hist_service_stat h, dba_hist_service_stat h1, dba_hist_snapshot s
where s.snap_id between 95000 and 96123
and s.snap_id=h.snap_id and s.dbid=h.dbid and s.instance_number=h.instance_number and h.stat_name='logons cumulative'
and h1.snap_id=h.snap_id+1
and h.dbid=h1.dbid and h.instance_Number=h1.instance_number and h.service_name=h1.service_name and h.service_name_hash=h1.service_name_hash and h.stat_id=h1.stat_id)
group by h.snap_id, h.instance_number, to_char(s.begin_interval_time,'dd-MON-yyyy hh24mi'),h.service_name, h.value order by h.service_name, h.instance_Number, begin_time;
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 17 2020 - 18:18:20 CEST