24 hr report from audit script [message #440020] |
Wed, 20 January 2010 12:10  |
divroro12
Messages: 105 Registered: March 2008 Location: Laurel, MD USA
|
Senior Member |
|
|
Hi All,
I have the following audit script which runs perfectly, but i need the output just for a 24 hr period.
Can someone please modify this code to get my expected output?
code:
SELECT USERNAME, OS_USERNAME, ACTION, ACTION_NAME, TERMINAL, TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24:MI:SS'), COUNT(*) COUNT
FROM DBA_AUDIT_TRAIL
WHERE USERNAME NOT LIKE 'SYS%'
AND USERNAME NOT LIKE 'PERFSTAT%'
AND OS_USERNAME NOT LIKE 'oracle'
AND OS_USERNAME NOT LIKE 'orcl102'
AND OS_USERNAME NOT LIKE 'grdagent'
GROUP BY USERNAME, OS_USERNAME, ACTION, ACTION_NAME, TERMINAL, TIMESTAMP
ORDER BY 1, 2
/
Regards,
- divroro12 -
|
|
|
|
Re: 24 hr report from audit script [message #440023 is a reply to message #440021] |
Wed, 20 January 2010 12:26   |
divroro12
Messages: 105 Registered: March 2008 Location: Laurel, MD USA
|
Senior Member |
|
|
The report should get output from let's say midnight today to midnight the next day.
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
- divroro12 -
|
|
|
|
|
Re: 24 hr report from audit script [message #440709 is a reply to message #440026] |
Tue, 26 January 2010 14:25   |
divroro12
Messages: 105 Registered: March 2008 Location: Laurel, MD USA
|
Senior Member |
|
|
Michel,
I'm getting faulty results from my query ( i don't think it's giving me a 24 hr report). I have a shell script that calls this query & runs at 3pm every day. What i need is for the SQL to be able to capture database user activity between 3pm yesterday to 3pm today.
The statement "WHERE TRUNC(TIMESTAMP) = TRUNC(SYSDATE)" was included in the SQL as per your advice above.
Below is the SQL statement i'm currently using:
code:
SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, RETURNCODE, TERMINAL, TO_CHAR(TIMESTAMP,'DD-MON-YYYY'), COUNT(*) COUNT
FROM DBA_AUDIT_TRAIL
WHERE TRUNC(TIMESTAMP) = TRUNC(SYSDATE)
AND USERNAME <> 'xxxxxxxx'
AND USERNAME NOT LIKE 'xxxxxxxx%'
AND USERNAME NOT LIKE 'xxxxxxxx%'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
AND OS_USERNAME NOT LIKE 'xxxxxxxx'
GROUP BY USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME, RETURNCODE, TERMINAL, TIMESTAMP
ORDER BY 1, 2
/
Thanks in advance for your help.
Regards,
- divroro12 -
|
|
|
Re: 24 hr report from audit script [message #440719 is a reply to message #440023] |
Tue, 26 January 2010 15:21   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
divroro12 wrote on Wed, 20 January 2010 13:26The report should get output from let's say midnight today to midnight the next day.
I think we need a better description. To me, this means from 00:00:00 todays date to tonight at 23:59:59, but unless you run this at 23:59:59, you are not going to get a 24 hour period.
|
|
|
Re: 24 hr report from audit script [message #440721 is a reply to message #440020] |
Tue, 26 January 2010 15:25   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Trunc sets the time component of any date to midnight. So if you want to get all the entries for a particular day from 00:00 to 23:59 then you would use
If you want a 24hr period starting at any time other than midnight then you would need to specify the full date/time to start and stop from. Use to_date with a full date time format mask.
|
|
|
|
|