This discussion is archived
4 Replies Latest reply: Oct 23, 2013 8:15 AM by chijar RSS

two queries against aud$ with different results

chijar Newbie
Currently Being Moderated

hi guys

I'm not so good with queries and that is the reason for my question:

We have audit activated and we want to get the following information (monthly):

  1. - how many times the user logon to database.
  2. - every logon of each user in a month.

 

For the first (1) requirement we have the following query:

 

select USERID "Cuenta", USERHOST, TERMINAL, nombres||' '||PRIMER_APELLIDO||' '||SEGUNDO_APELLIDO "WhiteList"
, count(*) "TOTAL"
from aud$, ab.usuarios
where (ACTION# = 100) and 
(NTIMESTAMP# between (to_date(to_char('01092013 00:00:00'),'ddmmyyyy HH24:MI:SS'))
    and (to_date(to_char('30092013 23:59:00'),'ddmmyyyy HH24:MI:SS'))) and
USERID = CODIGO_USUARIO(+) and
USERID not in ('DBSNMP','SYSMAN')
group by USERID, USERHOST, TERMINAL,  nombres||' '||PRIMER_APELLIDO||' '||SEGUNDO_APELLIDO
order by USERID, USERHOST, TERMINAL;

 

the result of this query shows like this:

Header 1

Cuenta               USERHOST                  TERMINAL        WhiteList                                     TOTAL                                                                                     

-------------------- ------------------------- --------------- ---------------------------------------- ----------                                                                                     

ASEGUR               MAQUINADIAB\SSSSS        IUOOO          PEPITO GARCIA Y GARCIA SOCIED                    10                                                                                     

ASEGUR               POSADASDE\MNNN4550094  MNNN4550094    PEPITO GARCIA Y GARCIA SOCIED                     1                                                                                     

ASEGUR               POSADASDE\YUMI          YUMI            PEPITO GARCIA Y GARCIA SOCIED                    10                                                                                     

ASEGUR               YUH                                       PEPITO GARCIA Y GARCIA SOCIED                    20                                                                                     

ASEGUR               SDFRG                                    PEPITO GARCIA Y GARCIA SOCIED                    13                                                                                     

ASEGUR               signy                                     PEPITO GARCIA Y GARCIA SOCIED                    29                                                                                     

ASEGUR               sigurd                                    PEPITO GARCIA Y GARCIA SOCIED                    32                                                                                     

ASEGUR               valhalla-Legacy                           PEPITO GARCIA Y GARCIA SOCIED                    12                                                                                     

ADMIN                MAQUINADIAB\SSSSS        IUOOO          USUARIO ADMINISTRADOR NETWORKING                3                                                                                     

SPRINGUSR            bragi                                                                                      98                                                                                     

SPRINGUSR            hermod                                                                                     59                                                                                     

 

 

                                                                             

SPRINGUSR            YUH                                                                                        49

So, is the total logons in a month by user.

 

for the second requirement we are using the follow query:

 

select USERID "Cuenta", USERHOST, TERMINAL, to_char(NTIMESTAMP#,'YYYYMMDD HH24:MI:SS') "Fec Ing", nombres||' '||PRIMER_APELLIDO||' '||SEGUNDO_APELLIDO "WhiteList"
--, count(*) "TOTAL"
from aud$, ab.usuarios
where (ACTION# = 100) and 
--to_char(NTIMESTAMP#,'dd-mm-yy')=to_char(sysdate-50,'dd-mm-yy') and 
(NTIMESTAMP# between (to_date(to_char('01062013 18:00:00'),'ddmmyyyy HH24:MI:SS'))
    and (to_date(to_char('30062013 23:59:00'),'ddmmyyyy HH24:MI:SS'))) and
USERID = CODIGO_USUARIO(+) and
USERID not in ('DBSNMP','SYSMAN')
group by USERID, USERHOST, TERMINAL,to_char(NTIMESTAMP#,'YYYYMMDD HH24:MI:SS'), nombres||' '||PRIMER_APELLIDO||' '||SEGUNDO_APELLIDO
order by USERID, USERHOST, TERMINAL;

 

Header 1

Cuenta               USERHOST                  TERMINAL        Fec Ing              WhiteList                                                                                                                                                            

-------------------- ------------------------- --------------- -------------------- ----------------------------------------                                                                                                                             

USER12               DOMINIODDD\IOIPOP        IOIPOP          20130930 12:08:33    ANGEL ROBERTO GARCIA Y GARCIA S                                                                                                                                        

USER12               DOMINIODDD\IOIPOP        IOIPOP          20130930 14:28:47    ANGEL ROBERTO GARCIA Y GARCIA S                                                                                                                                        

USER12               DOMINIODDD\IOIPOP        IOIPOP          20130930 16:24:43    ANGEL ROBERTO GARCIA Y GARCIA S 

so, shows each logon done by user in a month.

 

But in the both queries, the results are different. It is not suppose that they have to be the same number of logons?

I mean, If I sum the numbers in TOTAL column I have less that I get in the second query. Always !!!

 

could you help us?

thank you

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points