Convert SQL to SWQL for component down alerting

WinterSoldier

Hi,

I need below SQL query into SWQL which shows me same output like SQL. Please help me in this.

SELECT  
   APM_AlertsAndReportsData.NodeId  
  ,APM_AlertsAndReportsData.ApplicationID  
  ,APM_AlertsAndReportsData.ComponentID  
  ,APM_AlertsAndReportsData.NodeName AS 'NODE' --/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID} 
  ,APM_AlertsAndReportsData.ComponentName AS 'CMPNT' --/Orion/APM/MonitorDetails.aspx?NetObject=AM:${ComponentID} 
  ,APM_AlertsAndReportsData.ApplicationName AS 'APP' --/Orion/APM/ApplicationDetails.aspx?NetObject=AA:${ApplicationID}  
  ,APM_AlertsAndReportsData.ComponentStatus+'.gif' AS 'ICON' 
  ,APM_AlertsAndReportsData.ComponentStatus
  ,APM_AlertsAndReportsData.TimeStamp

FROM SolarWindsOrion.dbo.APM_AlertsAndReportsData WITH (NOLOCK)  

Left outer JOIN SolarWindsOrion.dbo.Nodes n WITH (NOLOCK) ON n.NodeID = APM_AlertsAndReportsData.NodeId  
Left outer JOIN SolarWindsOrion.dbo.APM_Application a WITH (NOLOCK) ON a.ID = APM_AlertsAndReportsData.ApplicationID  
Left outer JOIN SolarWindsOrion.dbo.APM_ApplicationTemplate at WITH (NOLOCK) ON at.ID = a.TemplateID  
Left outer JOIN SolarWindsOrion.dbo.APM_Component c WITH (NOLOCK) ON c.ID = APM_AlertsAndReportsData.ComponentID  
Left outer JOIN SolarWindsOrion.dbo.APM_ComponentTemplate ct WITH (NOLOCK) ON ct.ID = c.TemplateID  
Left outer JOIN AlertSuppression2 als WITH (NOLOCK) ON (als.Entityuri like concat('%ApplicationId=',a.ID) OR als.Entityuri like concat('%NodeId=',n.Nodeid))

WHERE  

  n.StatusDescription <> 'Unmanaged' AND --Node is not unmanaged
  n.UnManaged = 0 AND --Node is not unmanaged  
  APM_AlertsAndReportsData.ApplicationStatus <> 'Unmanaged' AND
  APM_AlertsAndReportsData.ComponentStatus Not in ( 'Up', 'Disabled' , 'Unmanaged' , 'Not Running' , 'Critical' , 'Warning' )

There might be difference in tables between SQL and swql. What I need to achieve is it should show me Component Name (with hyperlink), Application Name (with hyperlink), Component Status(watermark) grouped by Node Name.

Refer below screenshot as an example of SQL. I need same kind of output in SWQL query.

mesverrum

This looks pretty close

SELECT
cs.Component.Application.NodeID
,cs.ApplicationID
,cs.ComponentID
,cs.Component.Application.Node.Caption AS NODE
,cs.Component.Application.Node.DetailsUrl as [_linkfor_NODE]
,cs.Component.name AS [CMPNT]
,cs.Component.DetailsUrl as [_linkfor_CMPNT]
,'/Orion/images/StatusIcons/small-' + si.StatusEnumValue + '.gif' AS [_iconfor_CMPNT]
,cs.Component.Application.Name AS [APP]
,cs.Component.Application.DetailsUrl as [_linkfor_APP]


FROM Orion.APM.CurrentComponentStatus cs
JOIN Orion.APM.StatusMetadata si ON si.ApmStatusValue = cs.Availability
Left JOIN orion.AlertSuppression als ON (als.Entityuri like concat('%ApplicationId=',cs.component.ApplicationID) OR als.Entityuri like concat('%NodeId=',cs.Component.Application.NodeID))

WHERE

cs.component.Application.Node.status not in (9) --Node is not unmanaged
and cs.Component.Application.Status not in (9) -- App is not unmanaged
and si.DisplayName Not in ( 'Up', 'Disabled' , 'Unmanaged' , 'Not Running' , 'Critical' , 'Warning' )