Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Result set difference SQL vs.SWQL

Hi, Was given the task of writing the tsql statements to trigger an alert for the Voip piece of the Solarwinds suite that we use. I manage the SQL server where the SolarWinds database resides, and have no problem writing code there versus the database and returning the desired result set. I use the 'Custom SQL Alert (advanced)' method of pasting in my tsql code to create the alert, and configure all the way through to Submit. At that point I see that the code I have entered will trigger 4000+ alerts if I were to enable it and let it run.

When I run the code versus the Solarwinds database on the SQL server it returns 5 or less rows, which would be 5 alerts fired, I think. Why when creating the alert in the application am I notified that 4000+ alerts will be fired and emails sent accordingly? That would rattle the voice guys a bit, and probably lead to a conversation with them. And probably the Exchange guys too.

Here's what I wrote in TSQL below, with description of what I'm trying to alert on. I'm thinking something tsql doesn't convert to swql the right way.

Any thoughts or guidance would be greatly appreciated. Thanks, Dobbs

-- Query 1 -- The FinalCalledPartyNumber(voicemail 3699) is called more than 5 times in a 5 minutes.

Select VoipCallDetails.[FinalCalledPartyNumber], count(*) as FCPN_count

FROM VoipCallDetails

WHERE (( VoipCallDetails.[FinalCalledPartyNumber] = '3699')

AND VoipCallDetails.[Datetime] > DATEADD(ss, -300, GetDate()))

Group By VoipCallDetails.[FinalCalledPartyNumber]

Having count(*) > 5

--Query 2 -- The FinalCalledPartyNumber(voicemail 3699) is called by the same CallingPartyNumber more than 20 times within 5 minutes.

Select CallingPartyNumber, count(*) as CPN_count

FROM VoipCallDetails

WHERE (( VoipCallDetails.[FinalCalledPartyNumber] = '3699')

AND VoipCallDetails.[Datetime] > DATEADD(ss, -300, GetDate()))

Group By [CallingPartyNumber]

Having count(*) > 20

Find more posts tagged with

Accepted answers

All comments

zackm

Can you send a screenshot of the alert trigger screen? It sounds like you're missing a join.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

FormerMember

Hi Zach, there query that I wrote didn't need a join, as only one table involved. Here the screenshot. Thanks, Dobbs77.

zackm

Roger that, here's the issue:

In the Alert Engine, Custom SQL and SWQL alerts have a built-in SELECT statement (highlighted below)

So, what you are doing right now is this:

SELECT VoipCallDetailsAlert.ID, VoipCallDetailsAlert.Name FROM VoipCallDetailsAlertSELECT     VoipCallDetails.[FinalCalledPartyNumber],     count(*) as FCPN_countFROM VoipCallDetailsWHERE (( VoipCallDetails.[FinalCalledPartyNumber] = '3699')AND VoipCallDetails.[Datetime] > DATEADD(ss, -300, GetDate()))Group By  VoipCallDetails.[FinalCalledPartyNumber]Having count(*) > 5

You need to do a subquery and join that in to the default SELECT statement.

That should fix you right up

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

FormerMember

Hi Zack,

Thanks. It now makes sense why I returned so many rows. Is it necessary to include the "Select voipCallDetailsAlert.ID, voipCallDetailsAlert.name from voipCallDetailsAlert".

I don't need that code and can't figure out how to include it and have this return the proper number of rows to fire the alert. I'll kick it around some today and figure out what join can get this done.

Thanks for the replies.

Dobbs

FormerMember

Hello, I've written the query in SQL three different ways in MS TSQL, and ran it against the Solarwinds database, and the results are as expected. When I implement the code in the manage alerts section of the SW app, the code fails to validate when it is converted to SWQL. Below's code is does a self join which won't validate in SWQL. I must be overlooking some keyword translation between the languages that doesn't jive. I prefer the code I initially wrote but having to use the forced default language code pushed down by the app yielded the 4000+ alerts. I can just create the trigger on the view in the db and email to the admins when it fires, but I think keeping all alerts in the app is the optimal solution.

Thoughts?

Select vcda1.ID, vcda1.Name

From VoipCallDetailsAlert vcda1

Right OUTER Join

(select vcda2.[FinalCalledPartyNumber], count(*) as FCPN_count

FROM VoipCallDetailsAlert vcda2

WHERE ((vcda2.[FinalCalledPartyNumber] = '3699')

AND [DatetimeOrigination] > DATEADD(ss, -300, GetDate()))

group by [FinalCalledPartyNumber]

Having count(*) > 5) X

ON X.FinalCalledPartyNumber = vcda1.FinalCalledPartyNumber

zackm

Try this. The limitation with the alert is that you can only return the columns identified in the top-level query. If you need other details, you need to pull thos into your alert notification using variables and/or SQL Qeury-variables in the body of the message.

SELECT VoipCallDetailsAlert.ID, VoipCallDetailsAlert.Name FROM VoipCallDetailsAlert--EVERYTHING ABOVER HERE IS AUTO-FILLED--JOIN (  SELECT ID, COUNT(*) QTY FROM VoipCallDetailsAlert  WHERE FinalCalledPartyNumber = '3699'  AND Datetime > DATEADD(ss, -300, GetDate()))  GROUP BY ID  HAVING COUNT(*) >= 5) Results ON Results.ID = VoipCallDetailsAlert.ID

FormerMember

Thanks zackm. This piece was key: "The limitation with the alert is that you can only return the columns identified in the top-level query".

The alert count has dropped to the expected level and it appears to be working as needed!!

Dobbs77

aaguilar1

Semantic Web Query Language (SWQL) is a proprietary, read-only subset of SQL. Similar to SQL, you can use SWQL to query your SolarWinds database for specific network information. The following guide provides examples of SWQL syntax and valid SWQL queries.

Common SQL Constructs Supported

SWQL supports the following constructs from SQL:
Note: You must use CRUD operations to create, read, update, or delete entities.

SELECT … FROM …
WHERE clauses
GROUP BY clauses and HAVING clauses
ORDER BY clauses
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
UNION and UNION ALL
SELECT TOP n
SELECT DISTINCT
Subqueries in the SELECT clause
Subqueries in the FROM/JOIN clauses

SWQL supports the following aggregate functions:

SUM
MAX
MIN
AVG
COUNT

SWQL supports the following regular functions:

ISNULL
ABS

Common SQL Constructs Not Supported

SWQL does not support the following common SQL constructs:
Note: You must use CRUD operations to create, read, update, or delete entities.

SELECT * FROM … (You must list the actual properties you want to select.)
UPDATE, INSERT, DELETE, etc. (You can only use SWQL to read data.)

Constructs Unique to SWQL

SWQL supports various features that have no direct analog in SQL.

Navigation Properties

In SWQL, you can connect different entities (which are like tables or views in SQL) by relationships. You can traverse these relationships by using navigation properties in your queries. The closest equivalent in SQL is a join over a foreign key relationship.

For example, Orion.Nodes is connected to Orion.NPM.Interfaces by a hosting relationship that defines an Interfaces property on Orion.Nodes and a Node property on Orion.Interfaces. To get the contact associated with the node for all wireless interfaces, you could use a query like the following:

SELECT I.Caption, I.Node.Contact FROM Orion.NPM.Interfaces I WHERE I.TypeName='ieee80211'

This is easier to read and write than a SQL query to get the same data from the Orion database:

SELECT I.Caption, N.Contact FROM Interfaces I INNER JOIN Nodes AS N ON I.NodeID = N.NodeID WHERE I.InterfaceTypeName='ieee80211

You can only use one navigation property in a single expression. This prevents you from doing something like I.Node.CustomProperties.City because Node and CustomProperties are both navigation properties.

Row Windows

To fetch just a subset of the rows returned (for example for paging through a large result set), add a WITH ROWS start TO end clause after the ORDER BY clause.

For example, to get the second hundred nodes, you could use this query:

SELECT NodeID, Caption, IPAddress, Status FROM Orion.Nodes ORDER BY Caption WITH ROWS 100 TO 199

Wildcards

The wildcard character in SWQL syntax is: %.
Example: Node.Caption Like 'AX3%'

Filtering by Custom Property

The proper syntax to filter by custom property is:
dataType.CustomProperties.propertyName
Example filter to only show nodes with the custom property City that matches Atlanta:
Node.CustomProperties.City = 'Atlanta'

Filtering by Built-in Properties

Many properties have the same name between data types. To prevent ambiguity, SolarWinds SAM prefixes the property names with the data type.

Examples

Example filter to show data from Cisco devices:
Node.Vendor = 'Cisco'

Example filter to show data from Windows Server 2003-2008 applications:
Application.Name = 'Windows Server 2003-2008'

Example filter to show data from devices beginning with "AX3":
Node.Caption Like 'AX3%'

Example filter to show data from Process Monitor – SNMP type component monitors:
Monitor.ComponentType = 8

Filtering by Status

To filter by the status property, you must know the valid status levels.

Level	Status
0	Unknown
1	Up
2	Down
3	Warning
14	Critical

Example filter to only show monitors that are not down:

MonitorStatus.Availability<>2

Built-in SWQL Nodes Properties

Node.AvgResponseTime	Node.CPULoad	Node.Caption
Node.Contact	Node.DNS	Node.Description
Node.GroupStatus	Node.IOSImage	Node.IOSVersion
Node.IPAddress	Node.LastBoot	Node.LastSync
Node.Location	Node.MachineType	Node.MaxResponseTime
Node.MemoryUsed	Node.MinResponseTime	Node.NodeID
Node.ObjectSubType	Node.OrionID	Node.PercentLoss
Node.PercentMemoryUsed	Node.ResponseTime	Node.Severity
Node.Status	Node.StatusDescription	Node.SysName
Node.SysObjectID	Node.SystemUpTime	Node.TotalMemory
Node.Vendor	Node.VendorIcon	Node.ID