Creating a SWQL statement from more than one table

I'm trying to filter and list information from two linked tables (AlertDefinitions and AlertStatus), but to do that I think I need

an inner join statement or some other SQL stuff but I cannot make that work. Can you please advise?

>>This works fine
Select ObjectName, TriggerTimeStamp, Acknowledged, AcknowledgedBy, Notes
From Orion.AlertStatus
Where Acknowledged = '1'

>>This does not work
Select AlertName, ObjectName, TriggerTimeStamp, Acknowledged, AcknowledgedBy, Notes
From Orion.AlertStatus, Orion.AlertDefinitions
Where Acknowledged = '1'

I also need to list and filter the AlertName field from the AlertDefinitions table. How do I do that please?

Find more posts tagged with

statements

inner

sql

tables

Join

SWQL

multiple

Accepted answers

All comments

zbynek.botlo

Select Name, ObjectName, TriggerTimeStamp, Acknowledged, AcknowledgedBy, Notes

From Orion.AlertStatus s

Join Orion.AlertDefinitions d on s.AlertDefID = d.AlertDefID

Where Acknowledged = '1'

FormerMember

zbynek.botlo,

Do you know how I can get this same report but filter by a nodes custom property? I am trying to just give our Network Ops department their alerts.

Thanks for your help!

-Michael

zbynek.botlo

You can try something like this:

Select Name, ObjectName, TriggerTimeStamp, Acknowledged, AcknowledgedBy, Notes

From Orion.AlertStatus s

Join Orion.AlertDefinitions d on s.AlertDefID = d.AlertDefID

Join Orion.NodesCustomProperties ncp on ncp.NodeID = s.ActiveObject

Where Acknowledged = '1' and s.ObjectType = 'Node' and ncp.Department = 'IT'

You should be able to change the 'Department' custom property for your own.

Hope this helps,

Zbynek Botlo