How to select detailed alert information from RedGateMonitor database for long-running queries?

Aya

I have a query that returns generic alert information but I also want to see the information that is displayed on the detailed alert page like the user name, SQL text, and database.

Aya

Hi @Alex B,

Thanks for getting back to me.
It would be nice to get the query text for more detail analysis but I was able to get the data that I wanted so far and I think I did the joins correctly based on your suggestion and what I was able to confirm through data. here is the query that I used.

DROP TABLE IF EXISTS #Alerts

SELECT CAST(SUBSTRING(ac.TargetObject, 11 + PATINDEX('%LoginTime,d%', ac.TargetObject), PATINDEX('%,9:SessionId%', ac.TargetObject) - PATINDEX('%LoginTime,d%', ac.TargetObject) - 11) AS BIGINT) AS LoginTime_ticks
	  ,CAST(SUBSTRING(ac.TargetObject, 13 + PATINDEX('%9:SessionId,I%', ac.TargetObject), PATINDEX('%,', ac.TargetObject) - PATINDEX('%9:SessionId,I%', ac.TargetObject) - 13) AS BIGINT) AS SessionID
	  ,at.Name AS AlertName
	  ,at.Description AS AlertDescription
	  ,at.ShortName AS AlertShortName
	  ,ac.SubType
	  ,ac.AlertId
	  ,ac.RootCir
	  ,ac.GroupCir
	  ,ac.TargetObject
	  ,ac.[Read]
	  ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.ReadDate), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.ReadDate) AT TIME ZONE 'Pacific Standard Time'))) AS ReadDateDate
	  ,ac.ReadDate
	  ,ac.Event
	  ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.LastUpdate), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.LastUpdate) AT TIME ZONE 'Pacific Standard Time'))) AS LastUpdateDate
	  ,ac.LastUpdate
	  ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.Raised), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.Raised) AT TIME ZONE 'Pacific Standard Time'))) AS RaisedDate
	  ,ac.Raised
	  ,ac.LastSeverity
	  ,ac.WorstSeverity
	  ,ac.Cleared
	  ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.ClearedDate), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.ClearedDate) AT TIME ZONE 'Pacific Standard Time'))) AS ClearedDate
	  ,ac.LastComment
INTO #Alerts
FROM RedGateMonitor.alert.Alert_Current ac WITH (NOLOCK)
JOIN RedGateMonitor.alert.Alert_Type at WITH (NOLOCK) ON ac.AlertType = at.AlertType
WHERE AT.Name = 'Long-running query'
AND ac.RootCir LIKE '%<servername>%'

SELECT a.AlertId,
		a.AlertName,
		a.AlertShortName,
		a.AlertDescription,
		ssuv.Cluster_Name,
		ssuv.Cluster_SqlServer_Name,
		ssi._Hostname,
		ssi._LoginName,
		ssuv.Cluster_SqlServer_SqlProcess_DatabaseName,
		ssi._ProgramName,
		ssuv.Cluster_SqlServer_SqlProcess_Command,
		a.RaisedDate,
		a.LastUpdateDate
FROM #Alerts a
LEFT JOIN RedGateMonitor.[data].[Cluster_SqlServer_SqlProcess_UnstableSamples_View] ssuv WITH (NOLOCK) ON a.LoginTime_ticks = ssuv.Cluster_SqlServer_SqlProcess_LoginTime
																				AND a.Raised = ssuv.CollectionDate
																				AND a.SessionID = ssuv.Cluster_SqlServer_SqlProcess_SessionId
LEFT JOIN [RedGateMonitor].[data].[Cluster_SqlServer_SqlProcess_Instances] ssi WITH (NOLOCK) ON ssuv.Id = ssi.Id
WHERE ssuv.Cluster_SqlServer_SqlProcess_Command NOT IN (
'BACKUP DATABASE',
'DBCC',
'DBCC TABLE CHECK',
'ALTER INDEX'
)
OR ssuv.id IS NULL
ORDER BY a.LastUpdateDate DESC,
		a.AlertId DESC

Alex B

HI @Aya,

The _QueryText is compressed and you can decompress it like this:

<div>SELECT CAST(DECOMPRESS(CAST(CAST(csstqiv.[Cluster_SqlServer_TopQueries_QueryText]&nbsp; AS varchar(MAX)) AS varbinary(MAX))) AS varchar(max)) as decompressedText</div><div>FROM data.Cluster_SqlServer_TopQueries_Instances_View AS csstqiv;</div>

Kind regards,
Alex

Alex B

Hi @Aya,

We don't currently support directly querying the data repository I'm afraid, though the team is investigating this possibility (or some other manner of accessing the data directly).

The link between the alerts and the alert data isn't straightforward in the database; it is related to the TargetObject (which contains the entity it relates to) and the Raised time (in ticks) to correlate to the sampled data.

Kind regards,
Alex

Aya

Hi @"Alex B",<br><br>Thanks for getting back to me.<br>It would be nice to extract the actual text for more detailed analysis but I was able to retrieve what I needed and I think I did that correctly as far as the joins.<br><br><pre class="CodeBlock"><code>DROP TABLE IF EXISTS #Alerts SELECT CAST(SUBSTRING(ac.TargetObject, 11 + PATINDEX('%LoginTime,d%', ac.TargetObject), PATINDEX('%,9:SessionId%', ac.TargetObject) - PATINDEX('%LoginTime,d%', ac.TargetObject) - 11) AS BIGINT) AS LoginTime_ticks ,CAST(SUBSTRING(ac.TargetObject, 13 + PATINDEX('%9:SessionId,I%', ac.TargetObject), PATINDEX('%,', ac.TargetObject) - PATINDEX('%9:SessionId,I%', ac.TargetObject) - 13) AS BIGINT) AS SessionID ,at.Name AS AlertName ,at.Description AS AlertDescription ,at.ShortName AS AlertShortName ,ac.SubType ,ac.AlertId ,ac.RootCir ,ac.GroupCir ,ac.TargetObject ,ac.[Read] ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.ReadDate), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.ReadDate) AT TIME ZONE 'Pacific Standard Time'))) AS ReadDateDate ,ac.ReadDate ,ac.Event ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.LastUpdate), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.LastUpdate) AT TIME ZONE 'Pacific Standard Time'))) AS LastUpdateDate ,ac.LastUpdate ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.Raised), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.Raised) AT TIME ZONE 'Pacific Standard Time'))) AS RaisedDate ,ac.Raised ,ac.LastSeverity ,ac.WorstSeverity ,ac.Cleared ,CONVERT(datetime, SWITCHOFFSET(RedGateMonitor.utils.TicksToDateTime(ac.ClearedDate), DATEPART(TZOFFSET, RedGateMonitor.utils.TicksToDateTime(ac.ClearedDate) AT TIME ZONE 'Pacific Standard Time'))) AS ClearedDate ,ac.LastComment INTO #Alerts FROM RedGateMonitor.alert.Alert_Current ac WITH (NOLOCK) JOIN RedGateMonitor.alert.Alert_Type at WITH (NOLOCK) ON ac.AlertType = at.AlertType WHERE AT.Name = 'Long-running query' AND ac.RootCir LIKE '%<servername>%' SELECT a.AlertId, a.AlertName, a.AlertShortName, a.AlertDescription, ssuv.Cluster_Name, ssuv.Cluster_SqlServer_Name, ssi._Hostname, ssi._LoginName, ssuv.Cluster_SqlServer_SqlProcess_DatabaseName, ssi._ProgramName, ssuv.Cluster_SqlServer_SqlProcess_Command, a.RaisedDate, a.LastUpdateDate FROM #Alerts a LEFT JOIN RedGateMonitor.[data].[Cluster_SqlServer_SqlProcess_UnstableSamples_View] ssuv WITH (NOLOCK) ON a.LoginTime_ticks = ssuv.Cluster_SqlServer_SqlProcess_LoginTime AND a.Raised = ssuv.CollectionDate AND a.SessionID = ssuv.Cluster_SqlServer_SqlProcess_SessionId LEFT JOIN [RedGateMonitor].[data].[Cluster_SqlServer_SqlProcess_Instances] ssi WITH (NOLOCK) ON ssuv.Id = ssi.Id WHERE ssuv.Cluster_SqlServer_SqlProcess_Command NOT IN ( 'BACKUP DATABASE', 'DBCC', 'DBCC TABLE CHECK', 'ALTER INDEX' ) OR ssuv.id IS NULL ORDER BY a.LastUpdateDate DESC, a.AlertId DESC

Aya

Alex B said:

HI @Aya,

The _QueryText is compressed and you can decompress it like this:

<div>SELECT CAST(DECOMPRESS(CAST(CAST(csstqiv.[Cluster_SqlServer_TopQueries_QueryText]&nbsp; AS varchar(MAX)) AS varbinary(MAX))) AS varchar(max)) as decompressedText</div><div>FROM data.Cluster_SqlServer_TopQueries_Instances_View AS csstqiv;</div>

Kind regards,
Alex

Hi @Alex B,

This helps a lot, thank you.

sbozic

Is it possilbe to get to Deadlock(xe) xml or Blocking alert details in a similar manner?