cancel
Showing results for 
Search instead for 
Did you mean: 
mharvey
Level 17

Custom SWQL Query Resource Help

Jump to solution

I'm working with EOC trying to use the Custom SWQL query resources to get a resource to show me all Acknowledged alerts from Orion servers.  The following works in SQL:

Select

EOC_AlertStatus.ObjectName, EOC_AlertStatus.TriggerTimeStamp,

EOC_AlertDefinition.Name, EOC_AlertStatus.Acknowledged,

EOC_AlertStatus.AcknowledgedTime, EOC_AlertStatus.AcknowledgedBy

From EOC_AlertStatus

Join EOC_AlertDefinition on EOC_AlertStatus.AlertDefID = EOC_AlertDefinition.AlertDefID

Where EOC_AlertStatus.Acknowledged = 1

But if I try to use it following the SWQL documentation I've found, trying to run it this way:

SELECT

EOC.AlertStatus.ObjectName, EOC.AlertStatus.TriggerTimeStamp,

EOC.AlertDefinition.Name, EOC.AlertStatus.Acknowledged,

EOC.AlertStatus.AcknowledgedTime, EOC.AlertStatus.AcknowledgedBy

FROM EOC.AlertStatus

INNER JOIN EOC.AlertDefinition on EOC.AlertStatus.AlertDefID = EOC.AlertDefinition.AlertDefID

WHERE EOC.AlertStatus.Acknowledged = 1

results in the resource being unable to process the request.  Anyone thoughts?

Tags (3)
6 Replies
mharvey
Level 17

Re: Custom SWQL Query Resource Help

Jump to solution

Figured out the SWQL that I needed to use.  For anyone else who runs across this, here is what you'll need.

SELECT q.ObjectName, q.TriggerTimeStamp, q.Name, q.AcknowledgedBy, q.AcknowledgedTime, q.Notes, q._LinkFor_ObjectName, q._IconFor_ObjectName AS [_IconFor_ObjectName], q._LinkFor_Name

FROM

(

SELECT a.ObjectName, a.TriggerTimeStamp, d.Name, a.AcknowledgedBy, a.AcknowledgedTime, a.Notes, o.Website + '/Orion/View.aspx?NetObject=N%3A' + ToString(n.NodeID) AS [_LinkFor_ObjectName], '/images/Small-' + GroupStatus AS [_IconFor_ObjectName],'/Default.aspx?viewId=1004' AS [_LinkFor_Name]

FROM EOC.AlertStatus a

JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

JOIN EOC.Node n ON n.NodeID = a.ActiveObject AND a.ObjectType = 'Node' and n.OrionID = a.OrionID

JOIN EOC.Orion o ON o.OrionID = a.OrionID

where (Acknowledged = '1')

UNION

(SELECT a.ObjectName, a.TriggerTimeStamp, d.Name, a.AcknowledgedBy, a.AcknowledgedTime, a.Notes, o.Website + '/Orion/View.aspx?NetObject=I%3A' + ToString(i.InterfaceID) AS [_LinkFor_ObjectName], '/images/Small-' + s.StatusName + '.gif' AS [_IconFor_ObjectName],'/Default.aspx?viewId=1004' AS [_LinkFor_Name]

FROM EOC.AlertStatus a

JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

JOIN EOC.Interface i ON i.InterfaceID = a.ActiveObject AND a.ObjectType = 'Interface' AND i.OrionID = a.OrionID

JOIN EOC.Orion o ON o.OrionID = a.OrionID

JOIN EOC.StatusInfo s ON s.StatusId = i.Status

where (a.Acknowledged = '1'))

UNION

(SELECT a.ObjectName, a.TriggerTimeStamp, d.Name, a.AcknowledgedBy, a.AcknowledgedTime, a.Notes, o.Website + '/Orion/View.aspx?NetObject=AM%3A' + ToString(c.ComponentID) AS [_LinkFor_ObjectName], '/images/Small-' + c.Application.Node.GroupStatus AS [_IconFor_ObjectName],'/Default.aspx?viewId=1004' AS [_LinkFor_Name]

FROM EOC.AlertStatus a

JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

JOIN EOC.APM.Component c ON c.ComponentID = a.ActiveObject AND a.ObjectType = 'APM: Component' and c.OrionID = a.OrionID

JOIN EOC.Orion o ON o.OrionID = a.OrionID

where (a.Acknowledged = '1'))

) q

ORDER BY q.TriggerTimeStamp DESC

Highlighted
rsprim
Level 12

Re: Custom SWQL Query Resource Help

Jump to solution

Matt,

How were you able to test this with the SDK?  I'm unable to connect to the EOC server with v1.9 of the SDK installed on my EOC v1.5 server.  I keep getting unable to connect to Information Service.  The server has rejected the client credentials.

Robert

0 Kudos
mharvey
Level 17

Re: Custom SWQL Query Resource Help

Jump to solution

This was not done with the sdk, this was done in a custom query resource on the EOC page.

0 Kudos
rsprim
Level 12

Re: Custom SWQL Query Resource Help

Jump to solution

Thanks for the quick reply Matt.  Do you know if it's possible to use the SDK to test code like this?  If not, perhaps someone from SolarWinds can respond.  I'd like to be able to use the SDK to test it prior to placing it on a production server.

0 Kudos
mharvey
Level 17

Re: Custom SWQL Query Resource Help

Jump to solution

I don't think the SDK would be of use trying to connect to an EOC server. Fortunately with EOC, you can create a view that only you see, or use the customize page option and add the custom query to your view rather than the whole view for all users to see.

0 Kudos
contactjt
Level 10

Re: Custom SWQL Query Resource Help

Jump to solution

Just a note the SDK does connect to EOC fine and is decent for testing queries. You have to make sure the SWIS type is set for EOC in the drop down. Also the EOC website applys some filters the SDK doesn't so while good you should always verify the final result on the website for additional issues.

0 Kudos