This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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.

Reply Children