1 2 Previous Next 23 Replies Latest reply on Jun 21, 2019 12:44 PM by 3987328

    how to find occurrence in 3 different dates

    3987328

      Hello ,

       

      i have syslog table which saving millions of IP and the times,

      i'm trying to find same IP with 3 different times, the time is known and it's random depends on usage,

      so i've tried using OR but it does't find the correct results because any accordance will be counted regardless of others, while it must find the IP in 3 different known times ,

      so is there any way to check the 3 occurrences in a one query ?

       

       

      select * from syslog

      where srcip like '130.130.130.130'

      and (startdate='26-MAY-2019 00:02:46'

      or startdate='26-MAY-2019 00:13:28'

      or startdate='26-MAY-2019 01:12:57');

        • 1. Re: how to find occurrence in 3 different dates
          Frank Kulash

          Hi,

           

          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

          See the forum FAQ:Re: 2. How do I ask a question on the forums?

           

          3987328 wrote:

           

          Hello ,

           

          i have syslog table which saving millions of IP and the times,

          i'm trying to find same IP with 3 different times, the time is known and it's random depends on usage,

          so i've tried using OR but it does't find the correct results because any accordance will be counted regardless of others, while it must find the IP in 3 different known times ,

          so is there any way to check the 3 occurrences in a one query ?

           

           

          select * from syslog

          where srcip like '130.130.130.130'

          and (startdate='26-MAY-2019 00:02:46'

          or startdate='26-MAY-2019 00:13:28'

          or startdate='26-MAY-2019 01:12:57');

          Is startdate really a string?  That's very inefficient.  It's much better to use DATE columns for information about dates and times.

           

          Do you want to produce one row of output for each srcip that has all 3 startdates occurring at east one time each?

          If so, you can do it this way:

          select    srcip

          from      syslog

          where     startdate  IN ( '26-MAY-2019 00:02:46'  -- or TO_DATE ('26...'

                                  , '26-MAY-2019 00:13:28'

                                  , '26-MAY-2019 01:12:57'

                                  )

          group by  srcip

          having    count (distinct startdate)  = 3

          ;

           

          If you want to see all columns from all rows with those srcips then you can use the query above as as IN sub-query or you can use the analytic COUNT function instead of the aggregate COUNT.

          • 2. Re: how to find occurrence in 3 different dates
            3987328

            sorry for insufficient information , and here is the table along with the data .

             

            we are trying to find the srcip according to time and transip, almost all the millions srcip sharing few transip, and i'm looking for the scrip which i know it's did some 3 actions around specific times ..

            for instance i'm looking for this scrip (10.80.29.210) (which i should find it based on Transip and specific time )

             

            so the query should do the following , and the result should return all the scrip with that time and that transip

             

            select srcip , transip, startdate, SRCPORT

            from syslog

            where

            transip='130.193.223.254'

            and  startdate between ('26-MAY-2019 13:07:00') and ('26-MAY-2019 13:07:30')

            and startdate between ('26-MAY-2019 13:08:00') and ('26-MAY-2019 13:08:45')

            and startdate between ('26-MAY-2019 13:09:00') and ('26-MAY-2019 13:09:59')

             

             

             

            create table syslog

            (startdate date,

            srcip varchar2(15),

            transip varchar2(15),

            srcport varchar2(15));

             

             

             

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:00','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','48773');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','41877');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS'),'10.71.195.23','130.193.223.254','53338');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS'),'10.71.195.23','130.193.223.254','41316');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:06:02','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','48773');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:03','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','41878');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:08:04','DD-MON-YYYY HH24:MI:SS'),'10.81.52.201','130.193.223.254','62978');

            Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:09:04','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','41879');

            • 3. Re: how to find occurrence in 3 different dates
              CarlosDLG

              3987328 wrote:

               

              sorry for insufficient information , and here is the table along with the data .

               

              we are trying to find the srcip according to time and transip, almost all the millions srcip sharing few transip, and i'm looking for the scrip which i know it's did some 3 actions around specific times ..

              for instance i'm looking for this scrip (10.80.29.210) (which i should find it based on Transip and specific time )

               

              so the query should do the following , and the result should return all the scrip with that time and that transip

               

              select srcip , transip, startdate, SRCPORT

              from syslog

              where

              transip='130.193.223.254'

              and startdate between ('26-MAY-2019 13:07:00') and ('26-MAY-2019 13:07:30')

              and startdate between ('26-MAY-2019 13:08:00') and ('26-MAY-2019 13:07:45')

              and startdate between ('26-MAY-2019 13:09:00') and ('26-MAY-2019 13:07:59')

               

               

               

              create table syslog

              (startdate date,

              srcip varchar2(15),

              transip varchar2(15),

              srcport varchar2(15));

               

               

               

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:00','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','48773');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','41877');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS'),'10.71.195.23','130.193.223.254','53338');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS'),'10.71.195.23','130.193.223.254','41316');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:06:02','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','48773');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:07:03','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','41878');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:08:04','DD-MON-YYYY HH24:MI:SS'),'10.81.52.201','130.193.223.254','62978');

              Insert into syslog (STARTDATE,SRCIP,TRANSIP,SRCPORT) values (to_date('26-MAY-2019 13:09:04','DD-MON-YYYY HH24:MI:SS'),'10.80.29.210','130.193.223.254','41879');

              You are saying "the query should do the following..." and then you post a query, so, if you already have a query that does what you do, what kind of help do you need?

               

              I suppose that is not what you were trying to do, so, if you need to explain something, then explain it in English, not with code that doesn't do what you want.

               

              One of the reasons your query could be giving you incorrect results is that you should put the earliest date first in your BETWEEN conditions first, otherwise they will never be true.  In addition to that, you probably want ORs to connect your conditions about the dates, instead of ANDs, because, no date can be included in different periods of time unless they include each other or overlap in some way.

              • 4. Re: how to find occurrence in 3 different dates
                mathguy

                Getting to a complete and clear understanding of the problem before we start working on solutions is GOOD! Let's make that the first goal.

                 

                I am trying to understand what you are saying (from your words and from your code).

                 

                You have a table with (at least) the four columns you show: STARTDATE, SRCIP, TRANSIP, SRCPORT.

                 

                What role does SRCPORT play here?  In words you say you just want to find SCRIP (based on date-times and TRANSIP), no mention of SRCPORT, but then in the attempted code you include SRCPORT.  Why?  Do you, or do you not, need any information about SRCPORT in the output of your query?

                 

                (Note - it is OK to solve the problem in small steps, one at a time - but it is not OK to state the problem to us in small steps, one step at a time; tell us the WHOLE problem from the beginning.)

                 

                Then: in the original post you were looking for exact STARTDATE, but in the attempted code you seem to assign date RANGES (short time intervals). Which is the actual problem requirement? Importantly, how are you presenting those inputs to your query? Are they bind variables? Do they come from the calling interface? Same with the "given" TRANSIP.

                 

                The assignment itself seems reasonably simple, if I understood it correctly. You are given your big table, and some inputs: a TRANSIP and three time ranges (presumably non-overlapping?). Then you must find all the SCRIP that appear - with the same TRANSIP - in all three time ranges. (Here both your language and your code are confusing... I believe you are looking for SCRIP that appear in ALL THREE time windows, but perhaps you are looking for SCRIP that appear in AT LEAST ONE of those ranges?  This must be clarified!)

                 

                So, let's take it from here... please answer these questions first, and we'll see if we are ready to start working on solutions.

                • 5. Re: how to find occurrence in 3 different dates
                  3987328

                  Thank you very much , 

                   

                  i'm looking for the SCRIP that appears in all three time windows,

                  and all the columns will be required during displaying the results ,

                  , and the code that i've posted is just an example of my problem, is not the solution for sure

                  • 6. Re: how to find occurrence in 3 different dates
                    mathguy

                    OK, so let's say you are able to find the corresponding SCRIP  SRCIP  (don't confuse me!!)   for the given inputs.

                     

                    How are you going to display the SRCPORT then? The SCRIP SRCIP (make this change everywhere)   is the same, but it comes from three different rows (three different time windows), where the SRCPORT may be different every time. Do you want to display FULL ROWS from the base table, but only for those SCRIP that satisfy your conditions?  (And if so - just the rows from those three time windows, or ALL the rows for the (SCRIP, TRANSIP) pair, even the possible additional rows that have times outside the three windows?)

                     

                    Also, I assume - but please confirm - that simply counting how many times the SCRIP appears in the three windows is not enough (a SCRIP can appear three times in the first windows, and not at all in the other two - that will NOT satisfy your conditions, the SCRIP must appear AT LEAST ONCE in EVERY window).

                    • 7. Re: how to find occurrence in 3 different dates
                      mathguy

                      Also - importantly - WHAT IS YOUR ORACLE DATABASE VERSION?   Such as  12.2.0.1.0?   You can find that out by running  SELECT * FROM V$VERSION.

                      • 8. Re: how to find occurrence in 3 different dates
                        Frank Kulash

                        Hi,

                         

                        Thanks for posting the sample data.  Don't forget to post the exact results you want from the given data, and an explanation of why you want those results and not others.

                         

                        For example:

                        "The results I want in this example are:

                        SRCIP

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

                        10.80.29.210

                        that is, one row per srcid for every srcid that has a startdate in all three of these ranges

                        • 13:06:00 to 13:06:30 on May 26, 2019
                        • 13:07:00 to 13:07:45 on the same day, May 26, 2019
                        • 13:09:00 to 13:09:59 on the same day, May 26, 2019

                        I don't want srcip='10.71.195.23' because it has startdates in only one of the ranges.

                        I don't want srcip='10.81.52.201' because it doesn't  have startdates in any of the ranges."

                         

                         

                        If that were what you wanted, then you could do it like this:

                        WITH    target_ranges (startrange, endrange)   AS

                        (

                            SELECT  TO_DATE ('26-MAY-2019 13:06:00', 'DD-MON-YYYY HH24:MI:SS')

                            ,       TO_DATE ('26-MAY-2019 13:06:30', 'DD-MON-YYYY HH24:MI:SS')

                            FROM    dual

                        UNION ALL

                            SELECT  TO_DATE ('26-MAY-2019 13:07:00', 'DD-MON-YYYY HH24:MI:SS')

                            ,       TO_DATE ('26-MAY-2019 13:07:45', 'DD-MON-YYYY HH24:MI:SS')

                            FROM    dual

                        UNION ALL

                            SELECT  TO_DATE ('26-MAY-2019 13:09:00', 'DD-MON-YYYY HH24:MI:SS')

                            ,       TO_DATE ('26-MAY-2019 13:09:59', 'DD-MON-YYYY HH24:MI:SS')

                            FROM    dual

                        )

                        SELECT    s.srcip

                        FROM      syslog         s

                        JOIN      target_ranges  t  ON   s.startdate  BETWEEN  t.startrange

                                                                      AND      t.endrange

                        GROUP BY  s.srcip

                        HAVING    COUNT (DISTINCT t.startrange)  = 3

                        ;

                        assuming each range you want has a unique starting point (which will always be the case unless the ranges overlap).

                         

                         

                        As Carlos pointed out in reply #3, be careful using BETWEEN.

                        q BETWEEN x AND y

                        is just another way of saying

                        q >= x  AND  q <= y

                        If x > y, then that condition will never be true, no matter what q is.

                        • 9. Re: how to find occurrence in 3 different dates
                          mathguy

                          To illustrate some of my comments and questions - here is a possible solution, using the MATCH_RECOGNIZE clause (available only since Oracle 12.1). The output includes all the rows from the input table where the SRCIP satisfies the requirements (even for times OUTSIDE the three windows). If only the rows from the three time windows are needed, then the query can be modified easily to accommodate. Also, this solution shows all the columns from the input (SRCPORT and whatever else you may need).

                           

                          I included a "flag" column to show whether the row is in the first window (A), second window (B), third window (C) or not in any window (X). I changed the windows since the ones you wrote didn't make sense - find them in the DEFINE clause of MATCH_RECOGNIZE.

                           

                          select startdate, srcip, transip, srcport, flag

                          from  (select * from syslog where transip = '130.193.223.254')

                          match_recognize(

                            partition by srcip

                            order     by startdate

                            measures  classifier() as flag

                            all rows per match

                            pattern   (x*? a+ x*? b+ x*? c+ x*)

                            define    a as startdate between to_date('26-MAY-2019 13:06:00','DD-MON-YYYY HH24:MI:SS') and to_date('26-MAY-2019 13:06:30','DD-MON-YYYY HH24:MI:SS'),

                                      b as startdate between to_date('26-MAY-2019 13:07:01','DD-MON-YYYY HH24:MI:SS') and to_date('26-MAY-2019 13:07:25','DD-MON-YYYY HH24:MI:SS'),

                                      c as startdate between to_date('26-MAY-2019 13:08:50','DD-MON-YYYY HH24:MI:SS') and to_date('26-MAY-2019 13:09:05','DD-MON-YYYY HH24:MI:SS')

                          );

                           

                          STARTDATE             SRCIP            TRANSIP          SRCPORT  FLAG

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

                          26-MAY-2019 13:06:02  10.80.29.210     130.193.223.254    48773  A

                          26-MAY-2019 13:07:00  10.80.29.210     130.193.223.254    48773  X

                          26-MAY-2019 13:07:01  10.80.29.210     130.193.223.254    41877  B

                          26-MAY-2019 13:07:03  10.80.29.210     130.193.223.254    41878  B

                          26-MAY-2019 13:09:04  10.80.29.210     130.193.223.254    41879  C

                          • 10. Re: how to find occurrence in 3 different dates
                            Frank Kulash

                            Hi,

                             

                            I was writing reply #8 when you posted reply #5:

                            3987328 wrote:

                             

                            Thank you very much ,

                             

                            i'm looking for the SCRIP that appears in all three time windows,

                            and all the columns will be required during displaying the results ,

                            , and the code that i've posted is just an example of my problem, is not the solution for sure

                            Once again, post the exact results you want from the given sample data.

                            Do you want

                            STARTDATE            SRCIP           TRANSIP         SRCPORT

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

                            26-May-2019 13:06:02 10.80.29.210    130.193.223.254 48773

                            26-May-2019 13:07:03 10.80.29.210    130.193.223.254 41878

                            26-May-2019 13:07:01 10.80.29.210    130.193.223.254 41877

                            26-May-2019 13:07:00 10.80.29.210    130.193.223.254 48773

                            26-May-2019 13:09:04 10.80.29.210    130.193.223.254 41879

                            that is, all columns from all rows with srcips in all 3 ranges (as defined in reply #8)?

                            If so, you can modify the query in reply #8 like this:

                            WITH    target_ranges (startrange, endrange)   AS

                            (

                                SELECT  TO_DATE ('26-MAY-2019 13:06:00', 'DD-MON-YYYY HH24:MI:SS')

                                ,       TO_DATE ('26-MAY-2019 13:06:30', 'DD-MON-YYYY HH24:MI:SS')

                                FROM    dual

                            UNION ALL

                                SELECT  TO_DATE ('26-MAY-2019 13:07:00', 'DD-MON-YYYY HH24:MI:SS')

                                ,       TO_DATE ('26-MAY-2019 13:07:45', 'DD-MON-YYYY HH24:MI:SS')

                                FROM    dual

                            UNION ALL

                                SELECT  TO_DATE ('26-MAY-2019 13:09:00', 'DD-MON-YYYY HH24:MI:SS')

                                ,       TO_DATE ('26-MAY-2019 13:09:59', 'DD-MON-YYYY HH24:MI:SS')

                                FROM    dual

                            )

                            ,    got_matchcount    AS

                            (

                                SELECT    s.*

                                ,         COUNT (DISTINCT t.startrange)

                                              OVER (PARTITION BY  srcip) AS matchcount

                                FROM             syslog         s

                                LEFT OUTER JOIN  target_ranges  t  ON   s.startdate  BETWEEN  t.startrange

                                                                                     AND      t.endrange

                            )

                            SELECT  startdate, srcip, transip, srcport

                            FROM    got_matchcount

                            WHERE   matchcount = 3

                            ;

                            This will include all that are not within any of the ranges, if the same srcip as occurs in all 3 ranges.

                            If you want only the rows that are in one of the target ranges, then, in the sub-query called got_matchcount, change the LEFT OUTER JOIN to an inner JOIN.  (The results happen to be the same with this sample data.)

                            • 11. Re: how to find occurrence in 3 different dates
                              3987328

                              thank you very much, and the SCRIP must appear AT LEAST ONCE in EVERY window , and oracle version is

                              Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

                              PL/SQL Release 12.2.0.1.0 - Production

                              "CORE 12.2.0.1.0 Production"

                              TNS for Linux: Version 12.2.0.1.0 - Production

                              NLSRTL Version 12.2.0.1.0 - Production

                               

                              while when i run MATCH_RECOGNIZE query that you have posted i've got this message (Missing IN or OUT parameter at index:: 1)

                              • 12. Re: how to find occurrence in 3 different dates
                                mathguy

                                3987328 wrote:

                                 

                                while when i run MATCH_RECOGNIZE query that you have posted i've got this message (Missing IN or OUT parameter at index:: 1)

                                 

                                Are you using SQL Developer, or something similar?

                                 

                                SQL Developer uses JDBC (whatever that means), which interprets ? as a placeholder. I am not able to run my own query in SQL Developer either, although I use it for almost all my work. Instead, I must run the query in SQL*Plus.

                                 

                                Note that this is strictly a limitation of the product I use to interact with the database, and unfortunately the writers/keepers/maintainers of SQL Developer, who are aware of the issue, have stated that they have no intention to make any changes to their product to allow it to run perfectly correct Oracle SQL statements.

                                 

                                Can you run the query in SQL*Plus? That's what I did.

                                • 13. Re: how to find occurrence in 3 different dates
                                  3987328

                                  Yes correct, i was suing sqlDevleoper but it's working on sqlplus perfectly on the test table , while in the real table with millions records it takes 40 minutes till this moments without results,

                                  so please is there any suggestion to tune it further, please note that i've created a partion on the Startdate column on each hour .

                                  • 14. Re: how to find occurrence in 3 different dates
                                    3987328

                                    it's a great query and a nice idea and working perfectly on the test table, while on the real table with tons of data, it take too long,

                                    so please can we tune further .

                                    for instance, the interval for checking the 3 times normally is not big let's say we will check within 2-3 hours maximum ,

                                    and we have created a partion on the startdate column on hourly basis ,

                                    so is it possible to fetch only 2-3 hours first and then run the query so it will not check all the table ?

                                    1 2 Previous Next