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

AyaAya Posts: 4 New member
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.

Best Answers

  • AyaAya Posts: 4 New member
    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 BAlex B Posts: 1,153 Diamond 4
    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?

Answers

  • Alex BAlex B Posts: 1,153 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?
  • 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
  • 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.
  • sbozicsbozic Posts: 1 New member
    Is it possilbe to get to Deadlock(xe) xml or Blocking alert details in a similar manner?
Sign In or Register to comment.