10 Replies Latest reply on Sep 3, 2014 6:57 PM by mwb

    Difference between SWQL Studio and Orion Custom SWQL Query

    mwb

      Strangely when I use this query below, it works perfectly in SWQL Studio, but plugged into an Orion Custom Query box for a dashboard exempts some entries for reasons I can't discern.  Anyone able to explain?

       

      Capture9989.PNG

      Capture9988.PNG

      Capture9979.PNG

        • Re: Difference between SWQL Studio and Orion Custom SWQL Query
          bluefunelemental

          I've had some issues with group by in custom query resources. Try it with out the max and group by in the inner sub select to see if it works. Might need to group by both fields.

           

          Thanks,

          Christian

            • Re: Difference between SWQL Studio and Orion Custom SWQL Query
              mwb

              The only issue with that is that the MAX is absolutely required to return the latest DateTime instead of every datetime on every CPU poll.  This is a modification on the technique that others use to measure SNMP polling failures by reporting on the last DateTime of a CPU poll and how far it differs from the current time.

               

              This seems to work perfectly in SQL and Alert Manager (with mods for SQL vs SWQL), it seems to work perfectly in SWQL Studio, but then the same query in the Custom Query in the Orion dashboard just exempts certain entries - but the query works.

               

              I've got three installs of SAM and can confirm that its doing the same on at least two of them - the third may just not have an example to point to.

               

              Grouping by both fields seems to fail, to remove the group is to remove the MAX, which is to return far to many results.

            • Re: Difference between SWQL Studio and Orion Custom SWQL Query
              mwb

              I pulled it apart and put it back together, but again, it's leaving out an entry:

               

              Capture3359.PNG

              3360.PNG

               

              3361.PNG

                • Re: Difference between SWQL Studio and Orion Custom SWQL Query
                  mwb

                  there is a distinct time difference between the database values when querying through SQL or SWQL.  These are from the same server desktop session - what would account for a four hour difference in the times returned by the queries in the screencaps below?

                   

                  Capture1212.PNGCapture1213.PNG

                    • Re: Difference between SWQL Studio and Orion Custom SWQL Query
                      tdanner

                      This difference is due to time zone translation. SWIS attempts to present a consistent UTC format for all datetime values going in or out, even though some columns in the Orion database are stored in local time (generally the system time zone for the polling engine that stored them) and some are stored in UTC. The CPULoad.DateTime column is stored in local time. You must be on the US East Coast, so SWIS is adding four hours to convert to UTC before returning those values.

                      1 of 1 people found this helpful
                        • Re: Difference between SWQL Studio and Orion Custom SWQL Query
                          mwb

                          Thanks - yes, UTC occurred to me, but I didn't understand that SWIS was adjusting values to UTC even when they were server local to begin with - however, this only makes sense with the SWQL query if 'getdate' is returning local server in server time with no adjustment, leading to my MinuteDiff taking four hours and thirty minutes instead of thirty minutes.  I believe I have fixed the query with 'getUTCdate' - however, to account for the differences between the SWQL Studio and the Custom Query, it seems that 'getdate' would have to be pulling as UTC for Studio (resulting in correct results, but +4) but on Orion Custom Query pulling local server time for 'getdate' while still be doing +4 on the values from Orion.CPULoad.DateTime.

                           

                          If there were no difference between SWQL Studio and the Custom Query, then the results certainly should have matched, regardless of the timezone formatting.

                            • Re: Re: Difference between SWQL Studio and Orion Custom SWQL Query
                              mwb

                              If anyone is looking for something similar, this the modified version I've gone with - UTC for timing, ToLocal for display, with a server link to the node added in:

                               

                              SELECT Server, '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(NodeID) AS [_LinkFor_Server], MachineType,Last_Poll FROM (
                              
                               SELECT c.NodeID, a.Caption as Server, a.MachineType, ToLocal(MAX(DateTime)) as Last_Poll , MinuteDIFF(MAX(DateTime), getutcdate()) as TimeSinceLastPoll
                              FROM Orion.CPULoad c
                              
                                  INNER JOIN (  
                                    SELECT NodeID, Caption, MachineType 
                                    FROM Orion.Nodes
                                    where Status=1
                                    and UnManaged=0
                                      ) a ON c.NodeID=a.NodeID 
                              GROUP BY c.NodeID, a.Caption, a.MachineType
                              ) as polling
                              where TimeSinceLastPoll>30
                              ORDER BY Last_Poll
                              
                            • Re: Difference between SWQL Studio and Orion Custom SWQL Query
                              mwb

                              There's still something odd going on with the UTC timing - this alert showed itself as ahead of UTC somehow. 

                               

                              Capture1234.PNG

                                • Re: Difference between SWQL Studio and Orion Custom SWQL Query
                                  mwb

                                  Huh.  Apparently GetDate and GetUTCDate as input into the Custom Query window are dependent on local browser rather than Orion Server, SQL Server or database?

                                  Same dashboard in each, different times from the database.  To my knowledge there's no other part of SAM that adjusts the times to the timezone of the browser.

                                   

                                  Furthermore, how would UTC report differently?  The value here is from the simple query:

                                  SELECT GetUtcDate() as a1, GetDate() as a2 FROM Orion.Engines

                                  This is from the desktop of the PST Orion SAM Server: 

                                  Capture1111.PNG

                                  This is from a browser from an EST desktop to that same SAM:

                                  Capture1112.PNG

                                  How is the second one pulling from the browser's timezone versus the Orion server itself?