4 Replies Latest reply: Oct 23, 2013 10:15 AM by chijar RSS

    two queries against aud$ with different results

    chijar

      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