How to find back end SQL tables for Inherited Properties

If you have a working query from the API (SWQL Studio), then you can add "WITH QUERYSTATS" to get the SQL behind the scenes.  That should give you the info you need.

I'd still recommend NOT doing this work in SQL if at all possible.  What are you trying to accomplish/reference?

Setting up an alert for WPM transactions, but due to the way the ticketing integration works I need to bind on the node. I also want to include in the alert text which transaction steps have failed, but due to another limitation in the ticketing integration I can't pass across multiple rows, so need a way to output several rows as a single string. The only way I've found to do that so far is using the FOR XML function in a SQL query.

Sounds like you'd need to pull the parent node and then concatenate the error messages.  I don't think it's impossible to do, but it would be a little complex - in either SQL or SWQL.

I tend to agree with KMSigma.SWI about using the concat command. You can make complex joins in SWQL, and SWQL generally protects you from upgrade pits of doom and DB schema changes. You can always post the where are getting stuck in the forums, and usually one of us has run into that query or similar. I cannot say how often I have been able to get help on THWACK regarding SWQL. 

For my purposes I need to concatenate multiple rows into a single string. Using FOR XML ('') achieves that neatly in SQL. I've used it before to do exactly that. My problem this time is that I want to include the DisplayName property in the alert, because that's the name of the failing step, but I couldn't work out where SEUM was pulling the DisplayName property from.

Using WITH QUERYSTATS has shown me that it's a join to a sub-query, so now it should be pretty easy to finish the job.

I prefer using SWQL but sometimes the limitations stop me getting what I need out of it.

Do us all a favor and when you have the query you want, post it here as a code block.  I think we could all benefit from your process.

(SELECT CONCAT ( ' StepID:', [Steps].[TransactionStepId], '  Status:', [T3].[StatusDescription], '  Step Name:', [T4].[Name]) As Status_Of_Steps
FROM dbo.NodesData AS Nodes
INNER JOIN dbo.Dependencies AS Dependencies ON [Nodes].[NodeID] = [Dependencies].[ParentNetObjectID]
INNER JOIN dbo.SEUM_TransactionSteps AS Steps ON [Dependencies].[ChildNetObjectID] = [Steps].[TransactionId]
INNER JOIN 
(
SELECT rs.StepId,
                    rs.Name,
                    ts.TransactionStepId
                FROM SEUM_RecordingSteps rs
                JOIN SEUM_TransactionSteps ts ON rs.StepId = ts.StepId
) AS T4 ON [T4].[TransactionStepId] = [Steps].[TransactionStepId]
INNER JOIN 
(
SELECT ts.TransactionStepId, si.ShortDescription AS 'StatusDescription'
                FROM SEUM_TransactionSteps ts 
                      JOIN StatusInfo si ON ts.LastStatus = si.StatusId
) AS T3 ON [T3].[TransactionStepId] = [Steps].[TransactionStepId]
WHERE ([Nodes].[Caption] = 'www.cerberus.cop.homeoffice.gov.uk') AND [T3].[StatusDescription] != 'Up')
FOR XML PATH('')

The output isn't particularly pretty, but it gives me what I need for the alert if multiple transaction steps have failed into a single line.

(SELECT CONCAT ( ' StepID:', [Steps].[TransactionStepId], '  Status:', [T3].[StatusDescription], '  Step Name:', [T4].[Name]) As Status_Of_Steps
FROM dbo.NodesData AS Nodes
INNER JOIN dbo.Dependencies AS Dependencies ON [Nodes].[NodeID] = [Dependencies].[ParentNetObjectID]
INNER JOIN dbo.SEUM_TransactionSteps AS Steps ON [Dependencies].[ChildNetObjectID] = [Steps].[TransactionId]
INNER JOIN 
(
SELECT rs.StepId,
                    rs.Name,
                    ts.TransactionStepId
                FROM SEUM_RecordingSteps rs
                JOIN SEUM_TransactionSteps ts ON rs.StepId = ts.StepId
) AS T4 ON [T4].[TransactionStepId] = [Steps].[TransactionStepId]
INNER JOIN 
(
SELECT ts.TransactionStepId, si.ShortDescription AS 'StatusDescription'
                FROM SEUM_TransactionSteps ts 
                      JOIN StatusInfo si ON ts.LastStatus = si.StatusId
) AS T3 ON [T3].[TransactionStepId] = [Steps].[TransactionStepId]
WHERE ([Nodes].[Caption] = 'www.cerberus.cop.homeoffice.gov.uk') AND [T3].[StatusDescription] != 'Up')
FOR XML PATH('')

The output isn't particularly pretty, but it gives me what I need for the alert if multiple transaction steps have failed into a single line.