Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
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,