cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

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
Tags (2)
15 Replies
Level 10

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.

0 Kudos

The complete list of functions is in this thread just above. It will be in the Orion SDK document as well as soon I can finish the SDK 1.8 release.

0 Kudos

tdanner - thanks for adding the functions into the SDK Tech Reference doc - I see ToLocal() and changetimezone() which are helpful - but is there any plans to really support dateadd for when we want more flexibility then simply UTC <-> local time or timezone changes?

0 Kudos

We didn't have plans for a DATEADD function yet, but that's a very reasonable thing for us to include. I opened a feature request for it. 308248 is the internal case number.

0 Kudos
Level 19

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? 

0 Kudos

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

0 Kudos

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

0 Kudos

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

Is there a chance this table of functions could be added to the KB article: SolarWinds Knowledge Base :: How to use SolarWinds Query Language (SWQL)

0 Kudos

Thanks for the tip. I sent a note to our doc team to get that article updated.

0 Kudos

It looks like this list failed to migrate into the SDK 1.7 release.  Could you please try again for 1.8's documentation?

0 Kudos

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

0 Kudos

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

0 Kudos

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

0 Kudos