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

Convert SWQL to SQL query for external reports

Hi guys, still fairly new to all of this. I've found a great SWQL query for listing alerts with hyperlinked references in an easy access table and it runs great as a report in Solarwinds but we need to hook it into our external NOC for the ticketing system, can anyone help me convert the query below into an SQL query please?

SELECT DISTINCT

NodeCP.City AS [Site],

'<A HREF="'+AlertObjects.EntityDetailsURL+'" target=”_blank">'+Nodes.Caption+'</a>' AS NodeName,

'<A HREF="/Orion/Netperfmon/AckAlert.aspx?AlertDefID='+tostring(AlertObjects.AlertObjectID)+'" target="_blank">ClickToAck</a>' AS AcknowledgeIt,

AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType,

AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption, DetailsUrl,

ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,

AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,

Case

When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then

ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then

ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then

ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

Else ''

End AS ActiveTime

FROM Orion.AlertObjects (nolock=true) AlertObjects

INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID

INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID

INNER JOIN Orion.NodesCustomProperties (nolock=true) NodeCP ON AlertObjects.RelatedNodeID = NodeCP.NodeID

INNER JOIN Orion.Nodes Nodes ON AlertObjects.RelatedNodeID = Nodes.NodeID

WHERE Acknowledged = 1

Order By AlertConfigurations.Name, AlertObjects.EntityCaption

Many thanks in advance.

Find more posts tagged with

Accepted answers

leigh.robinson

I got the query to work:

SELECT DISTINCT

NodeCP.City AS [Site],

'<A HREF="'+AlertObjects.EntityDetailsURL+'" target=”_blank">'+Nodes.Caption+'</a>' AS NodeName,

'<A HREF="/Orion/Netperfmon/AckAlert.aspx?AlertDefID='+CONVERT(varchar,(AlertObjects.AlertObjectID))+'" target="_blank">ClickToAck</a>' AS AcknowledgeIt,

AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType,

AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption,

AlertActive.TriggeredDateTime AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,

AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,

Case

WHEN Floor((DateDiff(ss, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then

CONVERT(varchar,(CAST(round(Floor(DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400,0) as int))) +'d '+

CONVERT(varchar,(Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)))+'h '+

CONVERT(varchar,(Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)))+'m '

WHEN Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then

CONVERT(varchar,(Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)))+'h '+

CONVERT(varchar,(Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)))+'m '

WHEN Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then

CONVERT(varchar,(Floor(((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((DateDiff(s, AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

Else ''

End AS ActiveTime

FROM dbo.AlertObjects AlertObjects

INNER JOIN dbo.AlertActive AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID

INNER JOIN dbo.AlertConfigurations AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID

INNER JOIN dbo.NodesCustomProperties NodeCP ON AlertObjects.RelatedNodeID = NodeCP.NodeID

INNER JOIN dbo.Nodes Nodes ON AlertObjects.RelatedNodeID = Nodes.NodeID

WHERE Acknowledged = 1

Order By AlertConfigurations.Name, AlertObjects.EntityCaption

All comments

jrouviere

I didn't go through every item, but the two big ones that stick out to me are:

Your table references in the "FROM" section and the joins will need to be updated, usually you should just be able to drop the "Orion." portion, but you would want to check against the database/schema to make sure that's exactly right. As they have common names you shouldn't need to update any of the references in the select (I believe). So Orion.AlertActive would just be AlertActive.

The other thing is that I'm not sure that SQL has "ToString" implemented the same way, so you could try cast instead or there seems to be a "thing.tostring()", but I haven't used it.

leigh.robinson

Thanks, yeah I worked out the table references. The biggest issues are tostring, floor, and seconddiff (think I got this one using datediff in seconds).

I'll have a play with thing.tostring().

Cheers.