Question about using SWQL/SWIS or SQL in Alert Messages

In order to provide more information in the Alert Message for a Volume breaching a threshold I've added a SQL query that uses a Join to pull the Name of the VServer (NetApp) hosting the Volume (bolded below.) This works great when the Volume in question is indeed being hosted under a NetApp Cluster VServer. If the Volume is on a NetApp 7-mode I see the SQL query text after the "VServer: " label. I would like to use some logic that will only list the VServer when applicable.

I know it is preferred by Solarwinds that we use SWQL/SWIS because any queries or logic used there should survive product updates. So my questions;

How could the query below be re-written in SWQL/SWIS?
Can I use the "IsNull" or "Case" function to only display the VServer if it is applicable?

Thanks!

## Alert Message Excerpt Below ##

Timestamp: ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

Array: ${SQL: SELECT a.Name

FROM dbo.[SRM_StorageArrays] a

WHERE a.StorageArrayID = ${N=SwisEntity;M=StorageArrayID;F=OriginalValue} }

VServer: ${SQL: SELECT svm.Name

FROM dbo.[SRM_VServers] svm

INNER JOIN dbo.[SRM_VServerToVolumesMapping] m

ON svm.VServerID = m.VServerID

WHERE m.VolumeID = ${N=SwisEntity;M=VolumeID}}

Volume ${N=SwisEntity;M=Name} has only ${N=SwisEntity;M=CapacityFreePercentage} (${N=SwisEntity;M=CapacityFree}) free

Find more posts tagged with

orion swql

alert message

sql

alert_message

Accepted answers

All comments

njoylif

${SQL: SELECT

-- ISNULL(svm.Name,'') as Name

-- CASE WHEN svm.Name IS NOT NULL and svm.Name <> '' then svm.name else '' END as Name

FROM dbo.[SRM_VServers] svm

INNER JOIN dbo.[SRM_VServerToVolumesMapping] m

ON svm.VServerID = m.VServerID

WHERE m.VolumeID = ${N=SwisEntity;M=VolumeID}}

uncomment and test one at a time. I think ISNULL will work (not in email fields though).

rupdegraff

Ok, forgive me, issues grasping the syntax as well as specifying that I'm using true SQL or SWQL is my problem. The options you gave me above may be correct, but I'm not sure what to include and what not.

I've tried using exactly what you provided from the $ to the last } but it doesn't work
I also tried using either just the ISNUL or CASE without the --

Neither of these work.

Sorry to be so simplistic, but the syntax of formatting these things in the confines of SWIS/SWQL is where I'm struggling. I'm using this logic in the Message of an Alert. Can you reformat to exactly what will work without the options? If I can get a good working example of using logic I can reverse engineer and experiment from there.

Thanks for your help.

LubomirKrausko

Hi rupdegraff,

maybe this is what you are looking for:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

## Alert Message Excerpt Below ##

Timestamp: ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

Array: ${SQL: SELECT a.Name

FROM dbo.[SRM_StorageArrays] a

WHERE a.StorageArrayID = ${N=SwisEntity;M=StorageArrayID;F=OriginalValue} }

${N=SWQL;M=SELECT CASE WHEN ISNULL(Volumes.VServers.DisplayName, '') = '' THEN '' ELSE ('VServer: ' + Volumes.VServers.DisplayName) END as Name

FROM Orion.SRM.Volumes

WHERE VolumeID = ${N=SwisEntity;M=VolumeID} }

Volume ${N=SwisEntity;M=Name} has only ${N=SwisEntity;M=CapacityFreePercentage} (${N=SwisEntity;M=CapacityFree}) free

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Regards

Lubo

LubomirKrausko

And if you'd like to rewrite also the first SQL query to SWQL one (the one for Storage Array), you could use following:

Array: ${N=SWQL;M=SELECT Volumes.StorageArray.DisplayName

FROM Orion.SRM.Volumes

WHERE VolumeID = ${N=SwisEntity;M=VolumeID} }

Regards

Lubo

njoylif

Let us know if LubomirKrausko's code worked.

what I suggested is SQL. if you replace the variable part ${} with a valid volumeID (select * from SRM_VServerToVolumesMapping), then you can test in management studio.

swap out 1205 with a valid volume ID to test. This is what SQL would look like using the case statement for volume 1205.

SELECT

CASE WHEN svm.Name IS NOT NULL and svm.Name <> '' then svm.name else '' END as Name

FROM dbo.[SRM_VServers] svm

INNER JOIN dbo.[SRM_VServerToVolumesMapping] m

ON svm.VServerID = m.VServerID

WHERE m.VolumeID =1205

rupdegraff

The responses below have helped a lot and in fact allowed me to add the additional detail to the Alert messages. I also used some of my newfound knowledge in the criteria for alerts and a report.

I am still curious about the "N=", "M=" and "F=" that are necessary in the syntax. I assume they are not just arbitrary letters and gleaning from the examples, N= specifies the provided syntax as either SQL, SWQL or an SwisEntity.

Let me ask a question about the SwisEntity. In the example ${N=SwisEntity;M=StorageArrayID;F=OriginalValue} for the Alert that is based on a Volume exceeding a threshold, does the syntax effectively allow pulling of any field of the object the alert is based on?

I've read that there are some build in relationships between tables. Are there and where do I find a clear list? Would this allow me to pull the VServer Name or Array Name for a Volume that generated an alert without this need for SQL or SWQL JOINs?

I did find my way to the OrionSDK on GitHub @ Release v2.0.50 · solarwinds/OrionSDK · GitHub. Maybe I'm challenged, but I'm still not able to answer some of my questions that I posed above.

Where are these things documented?

Thanks!

njoylif

best way to learn is to blunder forward with tidbits to help steer.

the F= is optional - Format. I can't remember what the N and M stand for, but N defines the "source", i.e., alerting,SwisEntity,General.

M is the table structure (best easyspeak term, I think). if you install the SDK, it has the Schema reference, or open SWQL Studio and drop into tables. you'll see the linked tables at the bottom.

Documentation on GitHub is HERE

HTH