How can we help you today? How can we help you today?
Aya
Activity overview Badges (0) Posts (0) Comments (3)

Comments

Sort by recent activity
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. / comments
  1. Community
  2. Redgate Monitor
  3. How to select detailed alert information from RedGateMonitor database for long-running queries?
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(MA...
0 votes
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 / comments
  1. Community
  2. Redgate Monitor
  3. How to select detailed alert information from RedGateMonitor database for long-running queries?
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 correct...
0 votes
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 / comments
  1. Community
  2. Redgate Monitor
  3. How to select detailed alert information from RedGateMonitor database for long-running queries?
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 ba...
0 votes
Total activity
4
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1