• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Would want this to be 5 failed within an hour.

SELECT username, extended_timestamp, owner, obj_name, action_name
  2     FROM   dba_audit_trail where ACTION_NAME = 'LOGON' and returncode > 5;

USERNAME    EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAM
----------- -----------------------------------                 ----------           -------    ----------
AADM 21-APR-11 01.46.39.922000 PM -04:00                                              LOGON    
AADM 21-APR-11 10.02.46.516000 AM -04:00                                              LOGON    
AADM 21-APR-11 10.02.52.719000 AM -04:00                                               LOGON    
AADM 21-APR-11 10.25.53.024000 AM -04:00                                              LOGON    


not sure what this is showing, but need more detail.  Can we identify from what location?  Would want this to be 5 failed within an hour.
0
Cha1tu
Asked:
Cha1tu
  • 4
  • 2
  • 2
1 Solution
 
mrjoltcolaCommented:
I don't understand the question. Are you asking how to display users with 5 or more failed logins in an hour?
0
 
mrjoltcolaCommented:
Assuming that is what you want, try this.

SELECT username, TRUNC(timestamp, 'HH'), owner, obj_name, action_name, count(1)
   FROM dba_audit_trail where ACTION_NAME = 'LOGON' and returncode > 0
GROUP BY username, TRUNC(timestamp, 'HH'), owner, obj_name, action_name
HAVING COUNT(1) >= 5

Open in new window

0
 
Cha1tuAuthor Commented:
i need to find from which place or which server(userhost ) the user is connecting  and also...

i want to find how many times the user is connecting with a HOUR. if the user is failed to login like invalid username and password..if so i want to know howmnay times he failed within hour
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awa2008Commented:
Hello ,

You could try this when the user try to connect

SELECT terminal, machine FROM v$session WHERE username = 'AADM';

I hope that helps you

Regards


0
 
awa2008Commented:
Hello ,

You could also check with this sql :

select 'standard audit', sessionid,
    proxy_sessionid, statementid, entryid, extended_timestamp, global_uid,
    username, client_id, null, os_username, userhost, os_process, terminal,
    instance_number, owner, obj_name, null, new_owner,
    new_name, action, action_name, audit_option, transactionid, returncode,
    scn, comment_text, sql_bind, sql_text,
    obj_privilege, sys_privilege, admin_option, grantee, priv_used,
    ses_actions, logoff_time, logoff_lread, logoff_pread, logoff_lwrite,
    logoff_dlock, session_cpu
  from
  dba_audit_trail where username='AADM'


Hope that helps you

Regards
0
 
awa2008Commented:
Sorry for the missing link on the previous post , when I submitted the post I have forgotten this link

Sorry sorry

http://www.dba-oracle.com/t_audit_table_command.htm

Regards
0
 
Cha1tuAuthor Commented:
@awa2008:  
==>
select 'standard audit', sessionid,
    proxy_sessionid, statementid, entryid, extended_timestamp, global_uid,
    username, client_id, null, os_username, userhost, os_process, terminal,
    instance_number, owner, obj_name, null, new_owner,
    new_name, action, action_name, audit_option, transactionid, returncode,
    scn, comment_text, sql_bind, sql_text,
    obj_privilege, sys_privilege, admin_option, grantee, priv_used,
    ses_actions, logoff_time, logoff_lread, logoff_pread, logoff_lwrite,
    logoff_dlock, session_cpu
  from
  dba_audit_trail where username='AADM'


<==


For this query  its giving the all the hisory of that user. But i want to login failed attemts  within an hour.
and also the location(server name)  they are connecting.
Thanks
0
 
awa2008Commented:
Hello,

like user mrjoltcola sql's query you could add userhost field who gives you host name from where the user is connect from :

try this query :

SELECT username,userhost, TRUNC(timestamp, 'HH'), owner, obj_name, action_name, count(1)
   FROM dba_audit_trail where ACTION_NAME = 'LOGON' and returncode > 0
GROUP BY username,userhost, TRUNC(timestamp, 'HH'), owner, obj_name, action_name
HAVING COUNT(1) >= 5

I hope that helps you

Regards
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now