Home » SQL & PL/SQL » SQL & PL/SQL » 24 hr report from audit script (Oracle 10.2.0.4)
24 hr report from audit script [message #440020] Wed, 20 January 2010 12:10 Go to next message
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 #440021 is a reply to message #440020] Wed, 20 January 2010 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>I have the following audit script which runs perfectly, but i need the output just for a 24 hr period.

Do we get to choose the 24 hour period?

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: 24 hr report from audit script [message #440023 is a reply to message #440021] Wed, 20 January 2010 12:26 Go to previous messageGo to next message
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 #440024 is a reply to message #440023] Wed, 20 January 2010 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where trunc(timestamp) = trunc(sysdate)

Regards
Michel
Re: 24 hr report from audit script [message #440026 is a reply to message #440020] Wed, 20 January 2010 12:40 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Thanks Michel

- divroro12 -
Re: 24 hr report from audit script [message #440709 is a reply to message #440026] Tue, 26 January 2010 14:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4618
Registered: February 2005
Location: East Coast USA
Senior Member
divroro12 wrote on Wed, 20 January 2010 13:26
The 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 Go to previous messageGo to next message
cookiemonster
Messages: 12321
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
trunc(date_col) = date


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.
Re: 24 hr report from audit script [message #440722 is a reply to message #440709] Tue, 26 January 2010 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
> need is for the SQL to be able to capture database user activity between 3pm yesterday to 3pm today.

WHERE TIMESTAMP BETWEEN TRUNC(SYSDATE-1)+(15/24) AND TRUNC(SYSDATE)+(15/24)

--assuming SQL runs after 3PM "today"
Re: 24 hr report from audit script [message #440725 is a reply to message #440722] Tue, 26 January 2010 16:13 Go to previous message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Blackswan,

The SQL actually runs at exactly 3 PM every day..

Previous Topic: MAX(DECODE) to make value (row) as column header
Next Topic: Problem with collections
Goto Forum:
  


Current Time: Sat Oct 01 07:39:09 CDT 2016

Total time taken to generate the page: 0.08321 seconds