15 Replies Latest reply on Feb 14, 2015 1:40 AM by rsprim

    DateTime SWQL Question

    azabielski

      I need help converting this to SWQL : This is how far i got.....

       

      convert( varchar (10 ), ( DATEDIFF (HH , 0, GETDATE () - MAX ( Events. EventTime )) % 24 )) + ' Hours '


      This is how far i got.....


      ToString( HourDiff(GETDATE (),MAX ( Events. EventTime )) % 24 ) + ' Hours '


      The % 24 is giving me the issue, i guess that function doesn't exist in SWQL unless it does and i need to know the equivalent any help would be awesome!

        • Re: DateTime SWQL Question
          ET

          Hi,

          you are right SWQL doesn't support MODULO operator. But you can still do it self

           

          SELECT

              ToString( Ceiling( (HourDiff(MAX (EventTime ), GETUTCDATE()) / 24.0 - Floor(HourDiff(MAX ( EventTime ), GETUTCDATE ()) / 24.0) ) * 24 )) + ' Hours ' AS Hours

          FROM Orion.Events

           

           

          In SWQL use almost always GETUTCDATE since it's default time kind we work with.

           

           

          Thanks

          ET;

            • Re: DateTime SWQL Question
              azabielski

              Thank you sir! Now it looks nice again

              ToString(DayDiff(MAX(EventTime),GETUTCDATE())) + ' Days ' +

                ToString(Ceiling((HourDiff(MAX(EventTime), GETUTCDATE()) / 24.0 - Floor(HourDiff(MAX(EventTime),GETUTCDATE()) / 24.0) ) * 24 )) + ' Hours ' +

                ToString(Ceiling((MinuteDiff(MAX(EventTime), GETUTCDATE()) / 60.0 - Floor(MinuteDiff(MAX(EventTime),GETUTCDATE()) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime

              • Re: DateTime SWQL Question
                azabielski

                Actually i lied , seems there is an issue Let's say the alert comes in at 12:45pm then its now 1:05PM it will show as 1 hour and 20 mins instead of just 20 mins because the ceiling is 1 more int than the floor now which makes sense but the MODULO op takes care of that already.... Any other ideas?

                • Re: DateTime SWQL Question
                  azabielski

                  Slight change and i got it i just had to reverse the math a bit

                   

                  ,ToString(DayDiff(0,GETUTCDATE() - MAX(EventTime))) + ' Days ' +

                    ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(EventTime)) / 24.0)) * 24 )) + ' Hours ' +

                    ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime

                    • Re: DateTime SWQL Question
                      rsprim

                      I'm trying the below code on my Orion using v3 and I keep getting the Error "Entity Orion.Nodes does not contain requested navigation property Events".  Any idea how I can fix this?  I eventually want to place this information into an EOC Custom Query Resource to pull the data from all my Orions.  Possible?

                       

                      Select N.Caption, N.CustomProperties.Base as Location, N.CustomProperties.Tower, N.CustomProperties.Comments, ToString(DayDiff(0,GETUTCDATE() - MAX(N.Events.EventTime))) + ' Days ' + ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(N.Events.EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(N.Events.EventTime)) / 24.0)) * 24 )) + ' Hours ' + ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(N.Events.EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(N.Events.EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime From Orion.Nodes N Where N.CustomProperties.Tower = 'DST' AND N.Status = 2

                        • Re: Re: DateTime SWQL Question
                          tdanner

                          What version(s) of Orion are you using?

                           

                          You might be able to work around the missing "Events" navigation property by using a LEFT JOIN instead. Like this:

                           

                          Select N.Caption, N.CustomProperties.Base as Location, N.CustomProperties.Tower, N.CustomProperties.Comments,
                               ToString(DayDiff(0,GETUTCDATE() - MAX(NEvents.EventTime))) + ' Days '
                               + ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(NEvents.EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(N.Events.EventTime)) / 24.0)) * 24 )) + ' Hours '
                               + ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(NEvents.EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(NEvents.EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime 
                          From Orion.Nodes N 
                          LEFT JOIN Orion.Events NEvents ON N.NodeID= NEvents.NetworkNode
                          Where N.CustomProperties.City = 'DST' AND N.Status = 2
                          GROUP BY N.Caption, N.CustomProperties.Base, N.CustomProperties.Tower, N.CustomProperties.Comments
                          

                           

                          I added the LEFT JOIN on line 6 and changed all occurences of "N.Events" to "NEvents".

                            • Re: DateTime SWQL Question
                              rsprim

                              Thanks for the feedback TDanner!  I'm using 10.7 and I'm getting on a flight now but I'll test it as soon as possible.  How did you put the code in numbered lines within your response and is there anyway I can test this with SWQL Studio on EOC before plugging it into an EOC SWQL query resource?  Do you expect much will need to be changed to pull this data from EOC?

                                • Re: Re: DateTime SWQL Question
                                  tdanner

                                  To get the syntax highlighting and line numbering on thwack, you have to click the "use advanced editor" link above the reply box. This will pop you over to an editor page with more options. It will carry over your in-progress draft, so don't worry if you realize that you need something rom the better editor halfway through a reply. The advanced editor's toolbar has a blue >> button on the bottom right that pops out a menu with even more options. One of those options is "syntax highlighting", which lets you choose from a few languages. Sadly, SWQL is not one of the native ones. So I just pick SQL and it is close enough.

                                   

                                  Let me refer the EOC part of that question to derhally.

                                  • Re: DateTime SWQL Question
                                    derhally

                                    You will have to change the entity names to the corresponding ones from EOC.  e.g. EOC.Event and EOC.Node.,  Also will have to use the OrionID in the join statements to make sure the data lines up correctly.

                                     

                                    You should be able to test your query using SWQL Studio, just make sure you select "EOC" from the Server Type dropdown.

                                      • Re: DateTime SWQL Question
                                        rsprim


                                        tdanner - I tried the code and it works great.  derhally - When I try to use SWQL to login to my EOC server with EOC credentials I get "Unable to connect to Information Service.  The server has rejected the client credentials."  Any idea what I should check?  The Information service on the server is running and I'm at v1.5 of EOC.

                                    • Re: Re: Re: DateTime SWQL Question
                                      rsprim

                                      derhally - I have converted this to work with EOC and everything works except for the Duration piece.  I'm  not sure what the problem is either because I can't connect to the EOC Server with SWQL Studio.  On the webpage, it just says error processing request.  Any ideas?  I'm attaching a code snippet because for some reason I can't paste it in this window.

                                        • Re: Re: Re: DateTime SWQL Question
                                          derhally

                                          rsprim, Sorry but looks EOC hasn't been updated to add support for some of the functions that you are using.  Orion is a little a head in functionality than EOC's service.  Specifically functions like Floor  Ceiling are not part of the EOC grammar.  At this time it isn't possible to run a query like the one you want.

                                            • Re: Re: Re: Re: DateTime SWQL Question
                                              rsprim

                                              derhally,  Thanks for the update.  When do you expect this functionality to be integrated into EOC?  I hear there is an Orion like EOC on the horizon.  Will this functionality be included?  Are there any options available to me to at least show hours or minutes down?  I tried just including the EventTime and some of my nodes that are down are showing blanks for the EventTime. Any idea why that might be happening and how to fix it? Is it possible to show the Orion that's reporting the outage with SQL?  If so, could you help me do that?  Thanks so much!

                                                • Re: Re: Re: Re: Re: DateTime SWQL Question
                                                  derhally

                                                  I hope you understand, but I can't really speak about future releases.

                                                   

                                                  However, regarding your question about the query.  You would get blank EventTime when a node doesn't have any matching rows in EOC.Event.  This would happen because you are using a LEFT JOIN.  By default EOC tries to retrieve the last 24hrs worth of events or the newest events since it last polled the Orion server, you can try querying the Orion.Events entity and verify that there are no records for the nodes that are down.

                                                   

                                                  To figure out which Orion you can join against the EOC.Orion entity.  Something like this

                                                   

                                                  Select O.Name AS OrionServer, NEvents.EventTime, N.Caption, N.CustomProperty.Base as Location, N.CustomProperty.Tower, N.CustomProperty.SLA, N.CustomProperty.Comments   
                                                  From EOC.Node N
                                                  INNER JOIN EOC.Orion O ON N.OrionID = O.OrionID
                                                  LEFT JOIN EOC.Event NEvents ON N.OrionID=NEvents.OrionID AND N.NodeID = NEvents.NetworkNode
                                                  Where N.Status = 2 AND N.CustomProperty.ON_DEMAND != 1 AND N.CustomProperty.Base not like '%deployed%' AND N.CustomProperty.sla in (1,3,51,99) AND N.Caption not like '%KUSS%'
                                                  Order by N.CustomProperty.sla