1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query dba_audit_session and if 3 invalid login attempts in an hr of logins

Discussion in 'SQL PL/SQL' started by jhonnyrip, Feb 9, 2017.

  1. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    worked and Thanks
     
  2. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    I need hep

    Please query dba_audit_session and if 3 invalid login attempts(1017) in an hr of logins

    can somebody please give me query to find out
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, but you will get help.

    You need to be considering the min(timestamp) and max(timestamp) values for the given user, return those and calculate whether or not the difference is less than or equal to 60.
     
  4. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Jhonnyrip, your intention is to block these users?
     
  5. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    here is query that worked

    SELECT username,USERHOST,OS_USERNAME,COUNT(username) AS failed_count,
    TO_CHAR(MIN(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time, TO_CHAR(MAX(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time
    FROM sys.dba_audit_session
    WHERE username IN (SELECT username FROM dba_users WHERE profile = 'dddd_PROFILE') and returncode =1017 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 00:15:00')
    having COUNT(username) >=1
    group by username,USERHOST,OS_USERNAME;