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:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi:ss')
    FROM dba_audit_session
    WHERE username ='TIGER')
    AND RETURNCODE = 1017
    AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss')
    BETWEEN '08-FEB-17 20:02:28' AND '08-FEB-17 21:02:28'
    --HAVING COUNT (RETURNCODE) > 3
    --GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24:mi:ss');

    TIGER 08-FEB-17:21:02:14
    TIGER 08-FEB-17:21:02:12
    TIGER 08-FEB-17:21:02:09

    if I remove comments and run the above sql . I am not getting above results.

    can somebody help me out , how to i get above results
     
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    hi jhonnyrip, try changing the having to HAVING COUNT (RETURNCODE)> = 3
     
  3. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi:ss')
    FROM dba_audit_session
    WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    AND RETURNCODE = 1017
    AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss') BETWEEN '08-FEB-17 20:02:28'
    AND '08-FEB-17 21:02:28'
    HAVING COUNT (RETURNCODE) > =3
    GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24:mi:ss');

    no rows selected
     
  4. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    another try

    SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi:ss'), count(1)
    FROM dba_audit_session
    WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    AND RETURNCODE = 1017
    AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss') BETWEEN '08-FEB-17 20:02:28' AND '08-FEB-17 21:02:28'
    HAVING COUNT (1) >= 3
    GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24:mi:ss');
     
  5. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    1 SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi:ss'), count(1)
    2 FROM dba_audit_session
    3 WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    4 AND RETURNCODE = 1017
    5 AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss') BETWEEN '08-FEB-17 20:02:28' AND '08-FEB-17 21:02:28'
    6 HAVING COUNT (1) >= 3
    7* GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24:mi:ss')
    /

    no rows selected
     
  6. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    When you run without having and group by are still returning information?
     
  7. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    1 SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi:ss')
    2 FROM dba_audit_session
    3 WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    4 AND RETURNCODE = 1017
    5* AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss') BETWEEN '08-FEB-17 20:02:28' AND '08-FEB-17 21:02:28'


    USERNAME TO_CHAR(TIMESTAMP,
    ------------------------------ ------------------
    TIGER 08-FEB-17:20:58:11
    TIGER 08-FEB-17:20:58:13
    TIGER 08-FEB-17:20:58:15
     
  8. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    So, the timestamp are different ...
    do not group

    08-FEB-17:20:58:11
    08-FEB-17:20:58:13
    08-FEB-17:20:58:15


    SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi'), count(1)
    FROM dba_audit_session
    WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    AND RETURNCODE = 1017
    AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss') BETWEEN '08-FEB-17 20:02:28' AND '08-FEB-17 21:02:28'
    HAVING COUNT (1) >= 3
    GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24:mi')

    or

    SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24'), count(1)
    FROM dba_audit_session
    WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    AND RETURNCODE = 1017
    AND TO_CHAR(timestamp,'DD-MON-YY hh24:mi:ss') BETWEEN '08-FEB-17 20:02:28' AND '08-FEB-17 21:02:28'
    HAVING COUNT (1) >= 3
    GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24')
     
  9. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    Super and it worked

    Many thanks U
     
  10. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    one more question

    the query returns rows on toad and not in linux(sqlplus)
    i closed everything and started as fresh and still same thing

    do u know why ?
     
  11. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    I have no idea, I do not know linux well, but if I were to guess, I would think of date format
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    641
    Likes Received:
    131
    Trophy Points:
    810
    Location:
    Russian Federation
    other queries are working correctly?for example : select to_char(sysdate) from dual;

    what set the environment variables?

    you need to check environment variables in your sqlplus or toad(sqlplus window)
     
  13. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    select to_char(sysdate) from dual;


    TO_CHAR(S
    ---------
    09-FEB-17

    returns same data on toad and linux
     
  14. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    the query woks in SQL developer too
     
  15. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    jhonnyrip, try running with this formatting in linux


    SELECT username,TO_CHAR(timestamp,'DD-MON-YY:hh24:mi'), count(1)
    FROM dba_audit_session
    WHERE username IN (SELECT username FROM dba_users WHERE username='TIGER')
    AND RETURNCODE = 1017
    AND timestamp BETWEEN to_date('08-FEB-17 20:02:28','DD-MON-YY hh24:mi:ss') AND to_date('08-FEB-17 21:02:28','DD-MON-YY hh24:mi:ss')
    HAVING COUNT (1) >= 3
    GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY:hh24:mi')
     
  16. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    worked in linux
     
  17. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Sometimes work with date in varchar2 format of some problems ...
     
  18. jhonnyrip

    jhonnyrip Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    lansing
    select TIMESTAMP,returncode,username FROM dba_audit_session where username='TIGER' AND RETURNCODE = 1017 order by TIMESTAMP desc
    output
    *******
    09-FEB-17 17:25 1017 TIGER
    09-FEB-17 17:25 1017 TIGER
    09-FEB-17 17:25 1017 TIGER
    09-FEB-17 15:02 1017 TIGER
    09-FEB-17 15:02 1017 TIGER
    09-FEB-17 15:02 1017 TIGER
    09-FEB-17 10:40 1017 TIGER
    09-FEB-17 10:40 1017 TIGER
    09-FEB-17 10:40 1017 TIGER
    08-FEB-17 22:02 1017 TIGER
    08-FEB-17 22:02 1017 TIGER
    08-FEB-17 22:02 1017 TIGER
    08-FEB-17 20:58 1017 TIGER
    08-FEB-17 20:58 1017 TIGER
    08-FEB-17 20:58 1017 TIGER
    08-FEB-17 19:00 1017 TIGER
    08-FEB-17 19:00 1017 TIGER
    08-FEB-17 19:00 1017 TIGER
    08-FEB-17 17:33 1017 TIGER
    08-FEB-17 17:33 1017 TIGER
    08-FEB-17 17:33 1017 TIGER
    08-FEB-17 15:16 1017 TIGER
    08-FEB-17 15:12 1017 TIGER
    08-FEB-17 15:07 1017 TIGER
    08-FEB-17 15:06 1017 TIGER
    08-FEB-17 15:02 1017 TIGER
    08-FEB-17 14:13 1017 TIGER
    08-FEB-17 14:13 1017 TIGER
    08-FEB-17 14:13 1017 TIGER
    07-FEB-17 11:01 1017 TIGER
    03-FEB-17 16:22 1017 TIGER
    03-FEB-17 10:54 1017 TIGER
    05-DEC-16 14:04 1017 TIGER

    why am I not getting any rows ?

    1 SELECT username,TO_CHAR(timestamp,'DD-MON-YY hh24:mi'),count(*)
    2 FROM dba_audit_session
    3 WHERE username='TIGER'
    4 AND RETURNCODE = 1017
    5 AND to_date(timestamp,'DD-MON-YY hh24:mi') BETWEEN to_date('09-FEB-17 16:27','DD-MON-YY hh24:mi') AND to_date('09-FEB-17 17:30','DD-MON-YY hh24:mi')
    6 HAVING COUNT (*) >= 3
    7* GROUP BY username, TO_CHAR (timestamp, 'DD-MON-YY hh24:mi')


    no rows selected
     
  19. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Good morning jhonnyrip, this time the problem is in and to_date(timestamp,'DD-MON-YY hh24:mi') BETWEEN.
    Replace with to_date(to_char(timestamp,'DD-MON-YY hh24:mi'),'DD-MON-YY hh24:mi') BETWEEN
     
  20. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    20
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    jhonnyrip, To get better use trunc(timestamp,'mi') insted to_date(to_char(timestamp,'DD-MON-YY hh24:mi'),'DD-MON-YY hh24:mi')
    It gets cleaner

    SELECT username, trunc(timestamp,'mi'), count(*)
    FROM dba_audit_session
    WHERE username='TIGER'
    AND RETURNCODE = 1017
    AND trunc(timestamp,'mi') BETWEEN to_date('09-FEv-17 16:27','DD-MON-YY hh24:mi') AND to_date('09-FEv-17 17:30','DD-MON-YY hh24:mi')
    HAVING COUNT (*) >= 3
    GROUP BY username, trunc(timestamp,'mi')
    /