SWQL: Where is the table to find the SQL port number

timt

Hi,

Can you show me how to find the port of the APM SQL port on SWQL?

When I try to link the component port number, it's showing '0' or NULL.

SELECT DISTINCT n.caption, sc.NodeName as [Clustered Node], sc.Current as [Active Node], sq.InstanceName, sq.ProductVersion, sq.ProductLevel, sq.Edition, sa.Components.CurrentStatistics.ComponentPortNumber

FROM Orion.APM.SqlServerApplicationAlert sq

left join orion.apm.SqlServerApplication sa on sa.ApplicationID = sq.ApplicationID

left join orion.nodes n on sq.nodeid = n.NodeID

left join orion.NodesCustomProperties cp on sq.NodeID = cp.NodeID

left join orion.apm.SqlClusterNode sc on sc.ApplicationID = sq.ApplicationID

left join orion.apm.SqlDatabaseAlert sd on sd.ApplicationID = sq.ApplicationID   

pawelk

Hi Timt

Have you tried reading the port number from settings of the application?

There are two settings: PortType and PortNumber. PortType is either 'Default' or 'Static'. The PortNumber is set for 'Static' with the custom-port number.

Here is a query which selects AppInsight for SQLs and returns port numbers of SQLs:

SELECT sql.InstanceName, sql.ProductVersion, sql.ProductLevel, sql.Edition, sql.ApplicationID, sql.Name,

    (CASE WHEN type.Value = 'Static' THEN port.Value ELSE 1433 END) as PortNumber

FROM Orion.APM.SqlServerApplication as sql

LEFT JOIN Orion.APM.ApplicationSettings as port ON sql.ApplicationID = port.ApplicationID

LEFT JOIN Orion.APM.ApplicationSettings as type ON sql.ApplicationID = type.ApplicationID

WHERE port.Key = 'PortNumber'

AND type.Key = 'PortType'

you should be now able to join it to alerts table, as in your first post.