How to select detailed alert information from RedGateMonitor database for long-running queries?
Aya
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.
Tagged:
Best Answers
-
Aya
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 B
Posts: 1,157 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] 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
Answers
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
Have you visited our Help Center?
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 DESCThis helps a lot, thank you.