15 Replies Latest reply on Aug 26, 2014 9:19 AM by tdanner

    Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration

    jdtruman

      Here is Report Writer SQL code that produces the duration of down time for a node.  I need to be able to convert this to SWQL and populate the 'hours down' time into a custom property field.  I want to use the 'hours down' time custom property field in conjunction with changing Google map Icon colors once time frame meets specified thresholds.   I'm not very savvy with SQL or coding so all suggestions are welcome.  Thanks everyone.  

       

      SELECT     DATEDIFF(hh, T1.DownTime, CURRENT_TIMESTAMP) AS HoursDown, Nodes.Caption, Nodes.IP_Address,

      T1.DownTime, Nodes.NodeID

      FROM         (SELECT     MAX(EventTime) AS DownTime, NetObjectID, NetworkNode

                             FROM          Events

                             WHERE      (EventType = 1) AND (NetObjectType = 'N')

                             GROUP BY NetObjectID, NetworkNode) AS T1 INNER JOIN

                            Nodes ON T1.NetworkNode = Nodes.NodeID

      WHERE     (Nodes.Status = '2') AND (Nodes.Owner = 'RHD')

      ORDER BY HoursDown DESC, Nodes.Caption, T1.DownTime DESC, Nodes.NodeID

       

      Output from this Query:

      HoursDownNodeIP AddressDownTimeNode ID
      139rtr.316864.ph.us.tgr.net10.165.4.111/2/2012 14:1818188
      131rt2.023675.ph.us.tgr.net10.180.119.211/2/2012 22:1316384
      120rt2.013146.ph.us.tgr.net10.81.4.211/3/2012 9:5615487
      63rt2.013426.ph.us.tgr.net10.250.112.211/5/2012 18:1315560
      42rtr.023040.ph.us.tgr.net10.80.164.111/6/2012 15:4816194
      41rt2.012626.ph.us.tgr.net10.81.76.211/6/2012 16:5515348
      39rt2.013236.ph.us.tgr.net10.7.34.211/6/2012 18:2215534
      39rtr.023016.ph.us.tgr.net10.80.141.111/6/2012 18:0116174
      36rt2.008384.ph.us.tgr.net10.74.64.211/6/2012 21:1314776
      24rt2.026530.ph.us.tgr.net10.76.43.211/7/2012 9:4617140
      17rt2.013122.ph.us.tgr.net10.81.94.211/7/2012 16:1115475
      17rt2.013139.ph.us.tgr.net10.81.103.211/7/2012 16:1815484
      16rtr.012934.ph.us.tgr.net10.82.80.111/7/2012 17:4815429
      15rt2.006231.ph.us.tgr.net10.13.12.211/7/2012 18:2814567
        • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
          tdanner

          We just need to make a few changes to turn this into a SWQL query:

          1. Change "FROM Events" to "FROM Orion.Events"

          2. Change "INNER JOIN Nodes" to "INNER JOIN Orion.Nodes"

          3. Change "Nodes.Owner" to "Nodes.CustomProperties.Owner"

          4. Change "DATEDIFF(hh, T1.DownTime, CURRENT_TIMESTAMP)" to "HOURDIFF(T1.DownTime, GetDate())"

          1 of 1 people found this helpful
            • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
              jdtruman

              Changes worked perfectly - thanks.  Changes 1-3 I understand, change #4 - normal SWQL/SQL parms? 

                • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
                  tdanner

                  SWIS uses different syntax for some functions than SQL. The various DATEDIFF SQL functions are represented as DAYDIFF, HOURDIFF, MINUTEDIFF, SECONDDIFF, etc., in SWQL. Instead of CURRENT_TIMESTAMP, you have GETDATE().

                    • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
                      msawyer

                      tdanner,

                       

                      Do you know if I can pull of this kind of Summary Count in SWQL?  Below are the two SQL views I'm using to get the correct data:

                       

                      NAME=dbo.NetOps_Hardware_Status_Detail

                      SELECT     TOP (100) PERCENT NodeData.SysName AS NodeName, ahcs.HardwareCategoryID, ahc.Name AS HardwareCategoryName,

                                            ahc.IsDisabled AS HardwareCategoryIsDisabled, ahcs.Status AS StatusID, si.StatusName, ahcs.ItemsWithProblems, ahcs.ID, ahcs.NodeID

                      FROM         dbo.APM_HardwareCategoryStatus AS ahcs INNER JOIN

                                            dbo.APM_HardwareCategory AS ahc ON ahc.ID = ahcs.HardwareCategoryID INNER JOIN

                                            dbo.StatusInfo AS si ON ahcs.Status = si.StatusId INNER JOIN

                                            dbo.Nodes AS NodeData ON ahcs.NodeID = NodeData.NodeID

                      WHERE     (NodeData.Department = 'Network Operations') AND (NOT (ahcs.ItemsWithProblems LIKE 'NEXSAN%')) AND

                                            (NOT (ahcs.ItemsWithProblems LIKE 'HP Serial Attached SCSI%'))

                      ORDER BY si.StatusName

                       

                      NAME=dbo.NetOps_Hardware_Status_Summary

                      SELECT     StatusName AS Status, COUNT(*) AS Nodes

                      FROM         dbo.NetOps_Hardware_Status_Detail

                      GROUP BY StatusName

                       

                      Thanks in advance!

                      -Michael

                        • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
                          tdanner

                          The first query runs fine with just some minor syntax changes: remove the parenthesis around 100 in the TOP clause, change the table names to the names of the corresponding SWIS entities, and add ".CustomProperties" before the reference to the Department property. The result looks like this:

                           

                          SELECT TOP 100 PERCENT NodeData.SysName AS NodeName, ahcs.HardwareCategoryID, ahc.Name AS HardwareCategoryName,

                                                ahc.IsDisabled AS HardwareCategoryIsDisabled, ahcs.Status AS StatusID, si.StatusName, ahcs.ItemsWithProblems, ahcs.ID, ahcs.NodeID

                          FROM         Orion.APM.HardwareCategoryStatus AS ahcs INNER JOIN

                                                Orion.APM.HardwareCategory AS ahc ON ahc.ID = ahcs.HardwareCategoryID INNER JOIN

                                                Orion.StatusInfo AS si ON ahcs.Status = si.StatusId INNER JOIN

                                                Orion.Nodes AS NodeData ON ahcs.NodeID = NodeData.NodeID

                          WHERE     (NodeData.CustomProperties.Department = 'Network Operations') AND (NOT (ahcs.ItemsWithProblems LIKE 'NEXSAN%')) AND

                                                (NOT (ahcs.ItemsWithProblems LIKE 'HP Serial Attached SCSI%'))

                          ORDER BY si.StatusName

                           

                          SWIS does not support user-created views, so to do the second query you will need to replace that view reference with the query expanded in place. Also, you can't do COUNT(*), so you need to count an actual column. This should work:

                           

                          SELECT     NetOps_Hardware_Status_Detail.StatusName AS Status, COUNT(NetOps_Hardware_Status_Detail.NodeName) AS Nodes FROM (

                           

                          SELECT TOP 100 PERCENT NodeData.SysName AS NodeName, ahcs.HardwareCategoryID, ahc.Name AS HardwareCategoryName,

                                                ahc.IsDisabled AS HardwareCategoryIsDisabled, ahcs.Status AS StatusID, si.StatusName, ahcs.ItemsWithProblems, ahcs.ID, ahcs.NodeID

                          FROM         Orion.APM.HardwareCategoryStatus AS ahcs INNER JOIN

                                                Orion.APM.HardwareCategory AS ahc ON ahc.ID = ahcs.HardwareCategoryID INNER JOIN

                                                Orion.StatusInfo AS si ON ahcs.Status = si.StatusId INNER JOIN

                                                Orion.Nodes AS NodeData ON ahcs.NodeID = NodeData.NodeID

                          WHERE     (NodeData.CustomProperties.Department = 'Network Operations') AND (NOT (ahcs.ItemsWithProblems LIKE 'NEXSAN%')) AND

                                                (NOT (ahcs.ItemsWithProblems LIKE 'HP Serial Attached SCSI%'))

                          ORDER BY si.StatusName

                          ) AS NetOps_Hardware_Status_Detail

                          GROUP BY NetOps_Hardware_Status_Detail.StatusName

                        • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
                          algarciat

                          When i read the SIWS documentation i dont see any of this functions. Where can i find documentation for all this other functions? how do i know what is available? we are trying to change the UTC time result from a query to the local time, but we don't know which functions we have available to handle times.

                          I really would appreciate any help on this.

                           

                          Thanks in Advance,

                          Alberto Garcia

                            • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
                              tdanner

                              Here's a list of the supported functions. I'll get this added to the packaged documentation in the next SDK release.

                               

                              In the current version of the product we don't have a function for changing UTC time to Local time, but the next major release will have that.


                              Function

                              Example
                              GetDate

                              SELECT GetDate() as a FROM Orion.Engines

                              --  3/12/2013 1:09:45 PM

                              GetUtcDate

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

                              --  3/12/2013 8:31:28 AM, 3/12/2013 9:31:28 AM

                              DateTime

                              SELECT EngineID, KeepAlive FROM Orion.Engines where KeepAlive > DateTime('2013-03-11 15:45:50')

                              -- 1, 3/11/2013 2:46:44 PM

                              IsNullSELECT Restart, IsNull(restart, '01/01/1 0:0:0 AM') as r FROM Orion.Engines-- NULL,  1/1/2001 12:00:00 AM
                              Max

                              SELECT min(AlertID) as a1, max(AlertID) as a2, sum(AlertID) as a3, avg(AlertID) as a4 FROM Orion.Alerts

                              Count
                              Avg

                              SELECT abs(-1) as a1 FROM Orion.Engines

                              -- 1

                              Min
                              Abs
                              Sum
                              ToString

                              SELECT EngineID, KeepAlive, ToString(1) as String FROM Orion.Engines

                              EscapeSWISUriValue

                              SELECT Uri, EscapeSWISUriValue(Uri) as c FROM Orion.Engines

                              -- swis://mkuncvmtest./Orion/Orion.Engines/EngineID=1, "swis://mkuncvmtest./Orion/Orion.Engines/EngineID=1"

                              SplitStringToArray

                              SELECT SplitStringToArray('Hello|§|§|world') as a FROM Orion.Engines

                              Note: Internal

                              -- [Hello, world]

                              Floor

                              SELECT Floor(1.5) as c1, Ceiling(1.5) as c2 FROM Orion.Engines

                              -- 1, 2

                              Round

                              SELECT Round(1.543, 1) as c1, Round(1.543, 2) as c2 FROM Orion.Engines

                              -- 1.5, 1.54

                              Ceiling

                              SELECT Floor(1.5) as c1, Ceiling(1.5) as c2 FROM Orion.Engines

                              -- 1, 2

                              YearDiff

                              SELECT YearDiff('1/01/2013 0:0:0 AM','1/01/2012 0:0:0 AM') as a FROM Orion.Engines

                              -- -1

                              MonthDiff

                              SELECT WeekDiff(KeepAlive, KeepAlive + 60) as a FROM Orion.Engines

                              -- 2

                              WeekDiff

                              SELECT WeekDiff(KeepAlive, KeepAlive + 7) as a FROM Orion.Engines

                              -- 1

                              DayDiff

                              SELECT DayDiff(KeepAlive, KeepAlive + 1) as a FROM Orion.Engines

                              -- 1

                              HourDiff

                              SELECT HourDiff(KeepAlive, KeepAlive + 1) as a FROM Orion.Engines

                              -- 24

                              MinuteDiff

                              SELECT MinuteDiff(KeepAlive, KeepAlive + 1) as a FROM Orion.Engines

                              --  1440

                              SecondDiff

                              SELECT SecondDiff(KeepAlive, KeepAlive + 1) as a FROM Orion.Engines

                              --  86400

                              MillisecondDiff

                              SELECT MillisecondDiff(KeepAlive, KeepAlive + 1) as a FROM Orion.Engines

                              --  86400000

                              Year

                              SELECT Year('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 2013

                              QuarterOfyear

                              SELECT QuarterOfYear('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 1

                              DayOfYear

                              SELECT DayOfYear('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 71

                              Month

                              SELECT Month('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 3

                              Week

                              SELECT Week('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 11

                              Day

                              SELECT Day('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 12

                              Hour

                              SELECT Hour('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              --  8

                              Minute

                              SELECT Minute('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 39

                              Second

                              SELECT Second('3/12/2013 8:39:05 AM') as a FROM Orion.Engines

                              -- 5

                              Millisecond

                              SELECT MilliSecond('3/12/2013 8:39:05.123 AM') as a FROM Orion.Engines

                              -- 123

                              UriEquals
                      • Re: Need Help Converting SQL Report Writer Code to SWQL - Down Time Duration
                        William Vitalec

                        Is there a complete list of functions anywhere.  I have spent a lot of wasted time trying to locate things on this site and to be honest, this site has bits and pieces of information all over the place which makes it time consuming to locate.