16 Replies Latest reply on Mar 22, 2010 3:34 PM by adamras

    Event time - BETWEEN

      WHERE
      ( EventTime BETWEEN 40223 AND 40254 )
       AND 
      (
        (Nodes.Department = 'Airport Operational Systems') OR
        (Nodes.Department = 'Telecommunications') OR
        (Nodes.Department = 'Corporate Systems') OR
        (Nodes.Department = 'Network Operations') OR
        (Events.Message LIKE '%%has stopped responding%%') OR
        (Nodes.Escalation_Severity LIKE '%%Non-Critical%%') OR
        (Events.EventTime BETWEEN '2010-01-03 17:00:00' AND '2010-02-03 07:00:00')
      )

       

      Can anyone tell me why this isn't working?
      When I perform this query I get I HUGE range of dates.
      I have tried the 2010-01-03 in the 2010-03-01 syntax as well and get the same results. 

      What I am trying to accomplish is I want to know about Non-Critical Nodes that go down between the hours of 17:00 and 07:00.  I don't know if I am trying to get this information from the wrong spot or what.

        • Re: Event time - BETWEEN

          Can you provide a little more information?  It appears you are looking for a report that shows non-critical nodes that were down between the hours of 5:00p.m. and 7:00a.m.?  What defines a node as non-critical?  Is it the Escalation_Severity field?  Also, is there a certain time period that you want to look at with the report (i.e. last 24 hours, last week, last 30 days, etc...) 

          As for your current query...

          EventTime BETWEEN 40223 and 40254 translates to

          EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00

          sometimes it helps me to think of these types of queries as multiple individual queries in order to see the logic more clearly.  Your current query will select records where any of these individual pieces of logic are true

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Airport OPerational Systems)

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Telecommunications)

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Corporate Systems)

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Network OPerations)

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (Message Like has stopped responding)

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (Escalation_Severity like Non-Critical)

          (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (EventTime BETWEEN 01-03-2010 17:00:00 and 02-03-2010 07:00:00)

            • Re: Event time - BETWEEN

              Hey! Thanks for the reply.  I would be happy to give you some more information. 

              You're right, the "Escalation_Severity" defines the criticality of the Node. 
              There is a certain time frame that I am looking for.  it'll be in the "last month" context.

              Something I am confused about is the EventTime BETWEEN 40223 and 40254

              Are those seconds? Are you using a 3rd party tool to determine those dates or SQL or are you just a wizard with time?  I thought that me defining the hours in the EventTime BETWEEN 01-03-2010 17:00:00 and 02-03-2010 07:00:00) would override that.  Why I thought that, I don't know. 

              So what i think you're telling me that instead of defining the days and the hours that I want to report on in the "BETWEEN" that I should make it using the 40223 and 40254?

                • Re: Event time - BETWEEN
                  scottd

                  Adam,

                  Those are dates converted to days as a number.

                  you can convert these eaisily in excel by putting a date in the field and formatting it to 'number' with no decimal spaces.

                  • Re: Event time - BETWEEN

                    I was going to go with the time wizard angle but I guess scottd already blew that for me ;)

                    I like to use this sql to convert the numbers to time.

                    DECLARE @StartDate DateTime
                    DECLARE @EndDate DateTime
                    SET @StartDate = 40223
                    SET @EndDate = 40254
                    select @StartDate as Start, @EndDate as Finish

                    One other clarification.  Are you trying to find events that occurred between 7:00am and 5:00pm or between 5:00pm and 7:00am?

                      • Re: Event time - BETWEEN

                        Time wizard.. denied! haha

                        Yea, I am looking for events that took place between 5pm and 7am and also another report (though it would be cool to have it in the same one) of events that happened between 7:01 and 4:59

                          • Re: Event time - BETWEEN

                            See if this is close to what you are looking for.

                            WHERE 
                            (EventTime BETWEEN
                            dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND
                            dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
                            )
                            AND
                            (
                            (CONVERT(Char, EventTime, 108)< '0600') OR
                            (CONVERT(Char, EventTime, 108)> '1600')
                            )
                            AND
                            (
                            Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
                            Events.Message LIKE '%has stopped responding%' AND
                            Nodes.Escalation_Severity LIKE '%Non-Critical%'
                            )

                              • Re: Event time - BETWEEN

                                This is what is spit out:

                                 

                                Msg 156, Level 15, State 1, Line 1
                                Incorrect syntax near the keyword 'WHERE'.

                                  • Re: Event time - BETWEEN

                                    What I posted earlier was just the WHERE clause portion of the query.  You will also need to select the appropriate fields that you want the query to display.  Here is one possible example of a complete query.

                                    select Nodes.caption, events.eventtime, events.message from nodes
                                    join events on nodes.nodeid = events.networknode

                                    WHERE 
                                    (EventTime BETWEEN
                                    dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND
                                    dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
                                    )
                                    AND
                                    (
                                    (CONVERT(Char, EventTime, 108)< '0600') OR
                                    (CONVERT(Char, EventTime, 108)> '1600')
                                    )
                                    AND
                                    (
                                    Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
                                    Events.Message LIKE '%has stopped responding%' AND
                                    Nodes.Escalation_Severity LIKE '%Non-Critical%'
                                    )
                                    order by eventtime

                                      • Re: Event time - BETWEEN

                                        Thank you.  I was lookin' at it, knowing that something was missing but I couldn't determine it.  As you can probably ascertain, my SQL skills are bad, at best.  I am takin' all of this as a "teach a man to fish" kinda thing.  So your patience is appreciated.

                                         

                                        so what I did was added:

                                        SELECT Nodes.Caption AS NodeName,
                                        Events.EventTime AS Event_Time

                                        and it's giving me grief about the "EventTime" in

                                        WHERE 
                                        (EventTime BETWEEN

                                        I change that EventTime to Event_Time and still got an issue

                                          • Re: Event time - BETWEEN

                                            You will need to add something like this after your select statement and before the WHERE clause

                                            from nodes join events on nodes.nodeid = events.networknode

                                            See the complete query I posted earlier for an example.  Also, if you could post your complete query that would make troubleshooting easier.

                                              • Re: Event time - BETWEEN

                                                Sedmo,

                                                I am trying to determine what I need to put at the start.  In the following query I had just copied what I had in the last one I was using in hopes that it would be what I need.

                                                 

                                                SELECT Nodes.Caption AS NodeName,
                                                Events.EventTime AS Event_Time

                                                WHERE
                                                (EventTime BETWEEN
                                                dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND
                                                dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
                                                )
                                                AND
                                                (
                                                (CONVERT(Char, EventTime, 108)< '0600') OR
                                                (CONVERT(Char, EventTime, 108)> '1600')
                                                )
                                                AND
                                                (
                                                Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
                                                Events.Message LIKE '%has stopped responding%' AND
                                                Nodes.Escalation_Severity LIKE '%Non-Critical%'
                                                )

                                                  • Re: Event time - BETWEEN

                                                    Try this.

                                                    SELECT Nodes.Caption AS NodeName, Events.EventTime AS Event_Time
                                                    FROM nodes JOIN events on nodes.nodeid = events.networknode
                                                    WHERE
                                                    (EventTime BETWEEN
                                                    dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND
                                                    dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
                                                    )
                                                    AND
                                                    (
                                                    (CONVERT(Char, EventTime, 108)< '0600') OR
                                                    (CONVERT(Char, EventTime, 108)> '1600')
                                                    )
                                                    AND
                                                    (
                                                    Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
                                                    Events.Message LIKE '%has stopped responding%' AND
                                                    Nodes.Escalation_Severity LIKE '%Non-Critical%'
                                                    )

                                                      • Re: Event time - BETWEEN

                                                        Well, it didn't error out.
                                                        That's awesome! Thank you.

                                                        It gave me data from Feb 20 - 26th. 

                                                        From what I have learned from you and Scottd over the last few days, I believe what this query is doing is pulling data from last month, determined by the -1.  Also things that are less than 6:00 am and more than 4:00 pm which was determined by

                                                        (CONVERT(Char, EventTime, 108)< '0600') OR
                                                        (CONVERT(Char, EventTime, 108)> '1600')

                                                        I am confused as to the millisecond portion of the query that's at the top.
                                                        Also, how is it only pulling from Feb 20th and later?

                                                        If these questions are out of scope, that's cool.  I just thought I'd ask the source.

                                                        Thanks again!!

                                                          • Re: Event time - BETWEEN

                                                            I got almost exactly what I need with the following!

                                                            SELECT department, Nodes.Caption AS NodeName, Events.EventTime AS Event_Time, Nodes."System" AS System
                                                            FROM nodes JOIN events on nodes.nodeid = events.networknode
                                                            WHERE
                                                            (EventTime BETWEEN
                                                            40236 AND 40258
                                                            )
                                                            AND
                                                            (
                                                            (CONVERT(Char, EventTime, 108)< '0700') OR
                                                            (CONVERT(Char, EventTime, 108)> '1700')
                                                            )
                                                            AND
                                                            (
                                                            Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
                                                            Events.Message LIKE '%has stopped responding%' AND
                                                            Nodes.Escalation_Severity NOT LIKE '%Non-Critical%'
                                                            )

                                                            Thank you so much, guys

                                                            Now the bosses want me to put this report and another together.  This one that has 17:00-7:00 and then one that is 7:00 to 17:00. 

                                                            So like
                                                            After hours
                                                                      Nodes
                                                            Business Hours
                                                                      Nodes

                                                            Making the reports separately is easy now!  Combining them is again out of my realm of wizardly apprenticey knowledge

                                                              • Re: Event time - BETWEEN

                                                                You'll find that dealing with dates in SQL can be very involved and there are many different ways of specifying the same thing.  Here is what this particular query is doing.

                                                                This line of the query finds the first day of the previous month.
                                                                dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0))

                                                                If you run just this line as it's own query

                                                                select dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0))

                                                                It will currently return 2010-02-01 00:00:00.000

                                                                The next line of the query:

                                                                dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))

                                                                returns a value equal to 3 milliseconds before the end of the last day of the previous month.

                                                                Here is what it looks like when you run this line as it's own query.

                                                                select dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
                                                                2010-02-28 23:59:59.997

                                                                Your query is using these two statments to select records from the events table where the eventtime is between 2010-02-01 00:00:00.000 and 2010-02-28 23:59:59.997.

                                                                The next part of the query

                                                                (CONVERT(Char, EventTime, 108)< '0600') OR
                                                                (CONVERT(Char, EventTime, 108)> '1600')

                                                                looks like it is selecting records where the time is less than 6:00am or greater than 4:00pm but because of the syntax used it's actually selecting records where the eventtime is less than 7:00a.m. or greater than 5:00p.m.  This could also be written with the following syntax to specify less than 7:00am or greater than 5:00pm and would probably be more intuitive. 

                                                                (CONVERT(Char, EventTime, 108)< '07:00') OR
                                                                (CONVERT(Char, EventTime, 108)> '17:00')

                                                                As for the query only pulling records from February 20 - 26th I can think of two possibilities.  One is that those were the only dates last month where a non-critical node was down.  The other possibility is that you only have event records in the database for the last 30 days.  There is a setting in the System Manager that controls how long events are kept in the database.  If this setting is set to 30 days and you ran the query on March 20, then the records in the events table would only go back to sometime around February 20th.