How do you use cloud databases? Take the survey.
Options

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

AyaAya Posts: 4 New member
in Redgate Monitor
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.
Tagged:
· Share on Twitter

Best Answers

  • Options
    AyaAya Posts: 4 New member
    Answer ✓
    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

    · Share on Twitter
  • Options
    Alex BAlex B Posts: 1,132 Diamond 4
    Answer ✓
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter

Answers

  • Options
    Alex BAlex B Posts: 1,132 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • Options
    AyaAya Posts: 4 New member
    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
    · Share on Twitter
  • Options
    AyaAya Posts: 4 New member
    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.
    · Share on Twitter
  • Options
    sbozicsbozic Posts: 1 New member
    Is it possilbe to get to Deadlock(xe) xml or Blocking alert details in a similar manner?
    · Share on Twitter
Sign In or Register to comment.