Hi All,
Using the latest sets of Orion Platform SAM & DPA, how can I view the Perfstack of SQL agent job that is currently running and chewing up resources?
Thank you in advance.
Hi All,
Using the latest sets of Orion Platform SAM & DPA, how can I view the Perfstack of SQL agent job that is currently running and chewing up resources?
Thank you in advance.
If you are using Appinsight for SQL and want to show that info you can use below queries to create a "SQL DBA view". Add three custom query widgets and paste below SWQL code, three parts:
-- Failing SQL Jobs (and currently running) SELECT S.FullyQualifiedName,J.Name, J.LastRunDuration, J.Lastrundate, s.DetailsUrl, CASE WHEN J.Lastrunstatus=0 THEN 'Failed' WHEN J.Lastrunstatus=1 THEN 'Succeded' WHEN J.Lastrunstatus=4 THEN 'In progress' ELSE 'Retry or error' END as JobStatusText, CASE WHEN J.Lastrunstatus=0 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png' WHEN J.Lastrunstatus=1 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_Succeded.png' WHEN J.Lastrunstatus=4 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_inprogress.png' ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_retry.png' END as JobStatusIcon, J.Jobstatus, s.nodeid, s.ApplicationID FROM Orion.APM.SqlServerApplication S Inner join Orion.APM.SQLJobInfoAlert J ON S.ApplicationID=J.ApplicationID Inner join Orion.nodes N on S.NodeID=N.NodeID WHERE J.LastRunStatus=0 AND J.LastRunDate>ADDDAY(-7,GETUTCDATE()) AND Length(J.Name)<>36 --SQL Jobs - Long runners SELECT S.FullyQualifiedName,J.Name, J.LastRunDuration, J.Lastrundate, s.DetailsUrl, CASE WHEN J.Lastrunstatus=0 THEN 'Failed' WHEN J.Lastrunstatus=1 THEN 'Succeeded' WHEN J.Lastrunstatus=4 THEN 'In progress' ELSE 'Retry or error' END as JobStatusText, CASE WHEN J.Lastrunstatus=0 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png' WHEN J.Lastrunstatus=1 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_succeeded.png' WHEN J.Lastrunstatus=4 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_inprogress.png' ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_retry.png' END as JobStatusIcon, J.Jobstatus, s.nodeid, s.ApplicationID FROM Orion.APM.SqlServerApplication S Inner join Orion.APM.SQLJobInfoAlert J ON S.ApplicationID=J.ApplicationID Inner join Orion.nodes N on S.NodeID=N.NodeID WHERE --J.LastRunStatus<>1 J.LastRunDuration>2700 -- 45 MIN AND J.LastRunDate>ADDDAY(-7,GETUTCDATE()) -- Databases not taken backup on for the last 36 horus SELECT N.Caption, S.Name AS SQLInstanceName, D.Name AS Database, D.DatabaseID, D.ServerDatabaseID, D.RecoveryModel, D.LastBackup, D.DetailsUrl, D.WebUri FROM Orion.APM.SqlDatabase D Inner join Orion.APM.SqlServerApplication S ON S.ApplicationID=D.ApplicationID Inner join Orion.nodes N on S.NodeID=N.NodeID WHERE ISNULL(D.LastBackup,0)<ADDHOUR(-36,GETDATE()) AND D.databaseID NOT IN (Select databaseid From Orion.APM.SqlDatabaseMirroring where role=2)
If you are using Appinsight for SQL and want to show that info you can use below queries to create a "SQL DBA view". Add three custom query widgets and paste below SWQL code, three parts:
-- Failing SQL Jobs (and currently running) SELECT S.FullyQualifiedName,J.Name, J.LastRunDuration, J.Lastrundate, s.DetailsUrl, CASE WHEN J.Lastrunstatus=0 THEN 'Failed' WHEN J.Lastrunstatus=1 THEN 'Succeded' WHEN J.Lastrunstatus=4 THEN 'In progress' ELSE 'Retry or error' END as JobStatusText, CASE WHEN J.Lastrunstatus=0 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png' WHEN J.Lastrunstatus=1 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_Succeded.png' WHEN J.Lastrunstatus=4 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_inprogress.png' ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_retry.png' END as JobStatusIcon, J.Jobstatus, s.nodeid, s.ApplicationID FROM Orion.APM.SqlServerApplication S Inner join Orion.APM.SQLJobInfoAlert J ON S.ApplicationID=J.ApplicationID Inner join Orion.nodes N on S.NodeID=N.NodeID WHERE J.LastRunStatus=0 AND J.LastRunDate>ADDDAY(-7,GETUTCDATE()) AND Length(J.Name)<>36 --SQL Jobs - Long runners SELECT S.FullyQualifiedName,J.Name, J.LastRunDuration, J.Lastrundate, s.DetailsUrl, CASE WHEN J.Lastrunstatus=0 THEN 'Failed' WHEN J.Lastrunstatus=1 THEN 'Succeeded' WHEN J.Lastrunstatus=4 THEN 'In progress' ELSE 'Retry or error' END as JobStatusText, CASE WHEN J.Lastrunstatus=0 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png' WHEN J.Lastrunstatus=1 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_succeeded.png' WHEN J.Lastrunstatus=4 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_inprogress.png' ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_retry.png' END as JobStatusIcon, J.Jobstatus, s.nodeid, s.ApplicationID FROM Orion.APM.SqlServerApplication S Inner join Orion.APM.SQLJobInfoAlert J ON S.ApplicationID=J.ApplicationID Inner join Orion.nodes N on S.NodeID=N.NodeID WHERE --J.LastRunStatus<>1 J.LastRunDuration>2700 -- 45 MIN AND J.LastRunDate>ADDDAY(-7,GETUTCDATE()) -- Databases not taken backup on for the last 36 horus SELECT N.Caption, S.Name AS SQLInstanceName, D.Name AS Database, D.DatabaseID, D.ServerDatabaseID, D.RecoveryModel, D.LastBackup, D.DetailsUrl, D.WebUri FROM Orion.APM.SqlDatabase D Inner join Orion.APM.SqlServerApplication S ON S.ApplicationID=D.ApplicationID Inner join Orion.nodes N on S.NodeID=N.NodeID WHERE ISNULL(D.LastBackup,0)<ADDHOUR(-36,GETDATE()) AND D.databaseID NOT IN (Select databaseid From Orion.APM.SqlDatabaseMirroring where role=2)
On any summary view in orion, customize the view and add a widget, choose a "custom query" widget. Edit the widget and add the SWQL code.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.