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

Extracting IPSLA status changes with SWQL query

Hi,

The following query outputs the status of IPSLA tests, in the form shown in the picture below.

SELECT

oa.OperationInstanceID,

oa.RecordTime,

oa.OperationStatusID

FROM Orion.IpSla.OperationAvailability oa

queryoutput2.jpg

I'm trying to modify the output so that it only shows rows where OperationStatusID has changed.  i.e. in this example, only the highlighted rows should be in the output.

After much searching, the only approaches I can find are SQL examples that either use functions that SWQL does not have, or requires a subquery within a WHERE clause, which is also not supported by SWQL.

Is there any way this can be achieved, without resorting to directly querying the db with SQL?

Tags (1)
0 Kudos
5 Replies
Level 13

This is untested, but something like this might work.  It will likely be slow, though:

SELECT
    oa.OperationInstanceID,
    oa.RecordTime,
    oa.OperationStatusID
FROM Orion.IpSla.OperationAvailability oa
WHERE oa.OperationStatusID <>
(
    SELECT TOP 1
        oap.OperationStatusID
    FROM Orion.IpSla.OperationAvailability oap
    WHERE oa.OperationInstanceID = oap.OperationInstanceID AND oa.RecordTime > oap.RecordTime
    ORDER BY oap.RecordTime DESC
)
0 Kudos
Level 8

I've tried almost exactly that.  Unfortunately it doesn't work.  Some further investigation leads me to believe that SWQL doesn't support subqueries in the WHERE clause (only in SELECT and JOIN clauses) 

0 Kudos

I haven't tested this yet, but have you checked if the events table records anything for status changes?   I find in almost all cases it is much more practical to look for an event in the history than to try and parse every data point in the historical data.

- Marc Netterfield, Github
0 Kudos
Level 8

Hi,

yes, that was my first approach.  From what I can see, it's only recorded in the event history if there is an Alert configured.

Therefore my first approach was to configure alerts and track the trigger and reset times.

This works, but creates a whole bunch of unwanted alerts.  I was looking for an approach that didnt require alerts.

If anyone knows a way of getting these events into the event log without configuring alerts - please shout!

0 Kudos

Makes sense, I've had environments where we just set up informational alerts and then I edited all the views so informational alerts don't show, but it was kind of an elaborate scheme.

- Marc Netterfield, Github
0 Kudos