Adding the currently running SQL Agent job into the Perfstack?

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.

Parents
  • 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)
    

  • So where in Solarwinds I can copy paste that SWQL code?
    yes I do have AppInsight with SAM license.

  • 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.

    https://support.solarwinds.com/SuccessCenter/s/article/Use-Custom-Query-to-show-Node-Name-and-IP-Address?language=en_US 

Reply Children
No Data