Re: Logons by service name

Andrew,

  You might get better info from unified_audit_trail or dba_audit_sessions, depending on how auditing is configured in you system.

Jay

Sent from my iPhone

> On Apr 17, 2020, at 12:21 PM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
>
> 
> 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-l