Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Filtering aud$ table contents in Oracle !!

Chris Fung
Frequent Advisor

Filtering aud$ table contents in Oracle !!

Hi there,

I would like to know how to specify the critieria for extracting records from the aud$ table according to the following requirements.

1. Retain all terminal column with values (i.e. terminal is not null)

2. Not in the combination of spare1='administrator' and terminal='server01' and userid='scott'

3. Not in the combination of spare1='administrator' and terminal='client01' and userid='peter'

and finally I need to join 1, 2 and 3 criteria together to get the final result sets for my audit report.

I have tried to used parenthesis to hold criteria 2 and 3, e.g. ... AND (spare1='administrator' and terminal='client01' and userid='peter') AND ....


However, the result set excluded all the records owed by 'peter' even the terminal is not 'client01'.

Any idea ?

Cheers,

Chris,


7 REPLIES
Indira Aramandla
Honored Contributor

Re: Filtering aud$ table contents in Oracle !!

Hi Chris,

From your mentioned criteria I understand that you wanted to list all the values provided the terminal is not null and not one of the two ('server01' or 'client01') and the spare1 not = to administrator'.

If that is the case try this where clause.

where terminal is not null
and spare1 <> 'administrator'
and terminal not in ('server01','clinet01');

I hope this helps.
Never give up, Keep Trying
Chris Fung
Frequent Advisor

Re: Filtering aud$ table contents in Oracle !!

Hi Indira,

Thanks for your input. However, I am afraid the suggested sql statement may not be able to extract record for:

- those spare1 not equal to administrator e.g. peter or scott if they are log in through 'server01' or 'client01'.

What I would like to do is to exclude the combined criteria such that only when spare1='administrator' and terminal='client01' and userid='peter all return turn then I am going to exclude this record.

If spare1='administrator', terminal='client01' and userid='John', then I am going to print this record in my audit report.

I hope this can make the requirement clear.

Thanks for your effort.

Cheers,

Chris,
Sanjay Kumar Suri
Honored Contributor

Re: Filtering aud$ table contents in Oracle !!

Check the following sql:
where terminal is not null
and (spare1 <> 'administrator' and terminal <> 'server01' and userid <> 'scott')
and (spare1 <> 'administrator' and terminal <> 'client01' and userid <> 'peter');

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Chris Fung
Frequent Advisor

Re: Filtering aud$ table contents in Oracle !!

Hi SKS,

Thanks for your input. However, the result set for

(spare1 <> 'administrator' and terminal <> 'client01' and <> userid <> 'peter') is just the same as without the parenthesis.

Therefore the statement will elborated to =>

1. skip the record for spare1 = administrator
2. then skip the record for terminal = client01
3 and finally skip the record for userid = peter.

under this senario, those records e.g. with the criteria as spare1 = administrator and terminal = client01 but userid not equal to peter will be excluded from the result set.

Cheers,

Chris,

Sanjay Kumar Suri
Honored Contributor

Re: Filtering aud$ table contents in Oracle !!

Yes Chris, you are right. Try the following. It has worked in the test environment.

select * from tab1
where terminal is not null
and userid <> 'peter'
and spare1 not in (select spare1 from tab1 where spare1 <> 'administrator')
and terminal not in (select terminal from tab1 where terminal <> 'client01');

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Sanjay Kumar Suri
Honored Contributor

Re: Filtering aud$ table contents in Oracle !!

Check the following for your original post:

select * from tab1
where terminal is not null
and userid not in ('peter', 'scott')
and spare1 not in (select spare1 from tab1 where spare1 <> 'administrator')
and terminal not in (select terminal from tab1 where terminal <> 'client01' and terminal <> 'server01');

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Chris Fung
Frequent Advisor

Re: Filtering aud$ table contents in Oracle !!

Hi Sks,

thanks for your effort !!

I just figured out an easy way to do it !!!

E.g.

.....where terminal is not null and NOT (spare='Administartor' and terminal='server01' and userid='peter') ...

Cheers,

Chris,