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

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
  • 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())"

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

  • 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().

  • 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

  • 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

  • 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

  • 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
  • It looks like this list failed to migrate into the SDK 1.7 release.  Could you please try again for 1.8's documentation?

  • Sorry about that. I'll make sure it doesn't get missed again.

  • 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.