Discussion in 'SQL PL/SQL' started by jhonnyrip, Feb 9, 2017.
worked and Thanks
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
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.
Jhonnyrip, your intention is to block these users?
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
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;