Developing Nightly DBA PACKAGE for MS SQL SERVER and ORACLE

Developing Nightly DBA PACKAGE for MS SQL SERVER and ORACLE

CHECK IF SQL AGENT JOB RUNNING

Scenario: 1 DBA,
200+ MSSQL instances (PM, VM), 1700+ MSSQL databases, version 2000-2016
 10 Oracle machines(Windows, Linux, HPUX), 30 Oracle Databases. version 10g-12c

to be able to manage such big amount of MSSQL and Oracle databases, an automatically monitored system is required to highlight only the instances/database to be checked every morning.

Tool to be used:
MSSQL, SSIS , SSAS, SSRS

CHECK IF SQL AGENT JOB RUNNING

it would be easy to check if the sql agent job running when you have very small amount of SQL Servers to monitor, by just check it in SSMS, but it would be difficult or impossible to check when you have 200+ SQL servers as you have many many other important things to check and fix.
We used to tool nagios to check if a Windows service is stopped until recently we have a situation as I asked https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7b85edcf-02c2-45ff-a9ac-13f350403a6b/cannot-perform-this-operation-while-sqlserveragent-is-starting-a-possible-error-in-os-level-not?forum=sqldatabaseengine#7cea05f6-3e77-49f5-8c42-b6d851ca2b01 

SQL AGENT service is running if you see it in services.msc console. However, it is not actually running in normal state. in this case, nagios failed to report this issue. Also many times, someone (like vendor) accidentally turned of SQL Agent, nagios failed simply because no one is maintain this free product. since Agent not running, the traditionally job failure SQL mail won't send out .
So I created a report to show the latest Run date for all SQL Agents. I just need to check every morning in less than a minute to see if one sql agent service not running or something wrong. 
I am using the same data I collected for checking failed SQL Agent Jobs(database name replaced with myDB to protect my database privacy.)


---Dataset Dataset_SQL_Agent_Lates_RunDate
with cte as (
SELECT   [servername]
      ,[instancename]
 ,edition,version
  FROM [myDB].[RPT].[vwActiveSqlServers]
  where edition like 'Enterprise%' or edition like 'Standard%' or edition like 'Developer%')
  --select * from cte;

SELECT  [machinename]
      ,a.[instancename]

      ,max([Last_Run_Date]) last_run_date
  ,max(edition) edition
  ,max(version) version
  FROM [myDB].[SQL].[SQL_Agent_Job_List_B_Union] a inner join cte b
  on a.machinename=b.servername
  and a.instancename=b.instancename
  group by  [machinename]
      ,a.[instancename]

 order by 3 

the report looks like below, I just need check the 3 servers why they don't run SQL agent? and found that they were installed by vendors, discovered by my monitor system, and vendor didn't put any maintenance on it, no backup, no integrity checks, no index maintenance etc. the application owners are contacted and following up with vendors.