Hi,
I have a script below
set linesize 200
col username format a20
col os_username format a20
col userhost format a30
SELECT
username,
os_username,
userhost,
TRUNC(timestamp) AS timestamp_date, -- Truncates the timestamp to the day
returncode,
min(timestamp) min_timestamp,
max(timestamp) max_timestamp
FROM
dba_audit_session
WHERE username = '&v_username'
group by username, os_username, userhost, trunc(timestamp), returncode
order by TRUNC(timestamp)
/
That produces this output here below.
Can someone help me change the SQL so that I can get the number of days between the two timestamp_date? I guess I have to group by USERHOST to as that seems to be the more distinct information compare to username and os_username.
Please advise. Thanks in advance.
USERNAME OS_USERNAME USERHOST TIMESTAMP_DATE RETURNCODE MIN_TIMESTAMP MAX_TIMESTAMP
-------------------- -------------------- ------------------------------ ------------------- ---------- ------------------- -------------------
ABC_TEST1 cp-dummy-connect tester2 2025-07-31 00:00:00 0 2025-07-31 13:55:25 2025-07-31 15:30:48
ABC_TEST1 cp-dummy-connect tester3 2025-07-31 00:00:00 0 2025-07-31 13:55:35 2025-07-31 16:06:01
ABC_TEST1 cp-dummy-connect tester1 2025-07-31 00:00:00 0 2025-07-31 13:55:34 2025-07-31 15:30:55
ABC_TEST1 cp-dummy-connect tester0 2025-07-31 00:00:00 0 2025-07-31 09:22:37 2025-07-31 15:30:48
ABC_TEST1 cp-dummy-connect tester2 2025-08-05 00:00:00 0 2025-08-05 13:08:18 2025-08-05 13:08:19
ABC_TEST1 cp-dummy-connect tester1 2025-08-05 00:00:00 0 2025-08-05 13:08:19 2025-08-05 13:08:26
ABC_TEST1 cp-dummy-connect tester0 2025-08-05 00:00:00 0 2025-08-05 11:50:18 2025-08-05 13:08:18
ABC_TEST1 cp-dummy-connect tester1 2025-08-11 00:00:00 0 2025-08-11 09:21:44 2025-08-11 09:21:52
ABC_TEST1 cp-dummy-connect tester0 2025-08-11 00:00:00 0 2025-08-11 08:30:11 2025-08-11 09:21:44
ABC_TEST1 cp-dummy-connect tester2 2025-08-11 00:00:00 0 2025-08-11 09:21:44 2025-08-11 09:21:44
ABC_TEST1 cp-dummy-connect tester1 2025-08-13 00:00:00 0 2025-08-13 11:51:02 2025-08-13 11:51:04
ABC_TEST1 cp-dummy-connect tester0 2025-08-13 00:00:00 0 2025-08-13 11:51:02 2025-08-13 11:51:18
ABC_TEST1 cp-dummy-connect tester2 2025-08-13 00:00:00 0 2025-08-13 11:51:02 2025-08-13 11:51:04
ABC_TEST1 cp-dummy-connect tester3 2025-08-13 00:00:00 0 2025-08-13 11:51:00 2025-08-13 11:56:40