Identify agent jobs missing on secondary replicas

I'm trying to setup a process that will check the agent jobs on all servers in an availability group and report back any that might be missing from secondary replicas.

I have CMS setup with all servers from the AG and can run a query for agent jobs across the replicas, but I only want to return jobs that are on some servers but not all of them.

I wrote a query to get this information by using COUNT() but because the query results are grouped by server, this isn't working. I also tried to put the results in a temporary table to see if this would help, but that doesn't seem to have made much of a difference.

There are four servers in the AG and the following query should return the name of any jobs that aren't on all four servers, but because it seems to return the server name too it isn't recognising them as the same Job.

CREATE TABLE #jobs (
JobName NVARCHAR(128))

INSERT INTO #jobs
SELECT name JobName
FROM sysjobs

SELECT *
FROM #jobs
GROUP BY JobName
HAVING COUNT(*) < 4
ORDER BY JobName

DROP TABLE #jobs
The query as it stands would throw out a result set such as below:

+------------+------------------------------------------+
| ServerName | JobName |
+------------+------------------------------------------+
| ServerA | DBA DatabaseBackup - AG_DATABASES - FULL |
| ServerA | OutputFile Cleanup |
| ServerB | DBA DatabaseBackup - AG_DATABASES - FULL |
| ServerB | IndexOptimize - USER_DATABASES |
| ServerC | DBA DatabaseBackup - AG_DATABASES - FULL |
| ServerB | IndexOptimize - USER_DATABASES |
| ServerD | DBA DatabaseBackup - AG_DATABASES - FULL |
| ServerD | Output File Cleanup |
+------------+------------------------------------------+

The job DBA DatabaseBackup - AG_DATABASES - FULL exists for all four servers so I don't want this one returned, but because of the implicit servername column which comes from the CMS connection it doesn't recognise them as the same value.

So want a query that returns all the `JobName`s that have less than 4 entries. There is not point creating a temporary table, just query directly the `sysjobs` table, like :

SELECT name
FROM sysjobs
GROUP BY name
HAVING COUNT(*) < 4
ORDER BY name

If your CMS is always inserting the server name followed by the job name delimited with a dash surrounded by spaces... you could parse out the server names with something like this:

INSERT INTO #jobs
SELECT REPLACE(SUBSTRING(name, CHARINDEX(' - ', name),LEN(name)) , ' - ', '') JobName
FROM sysjobs

As GMB mentioned you will need to extract part of the "name" field.

I suggest splitting the string into ServerName and JobName.

Assuming the name is seperated by - like in your example your code could look like this.

CREATE TABLE #jobs (
ServerName NVARCHAR(128)
,JobName NVARCHAR(128))

INSERT INTO #jobs
SELECT
LEFT(name,CHARINDEX('-',name)-1) AS ServerName --Takes the Part left of the first -
, RIGHT(name,LEN(name) - CHARINDEX('-',name)) AS JobName --Takes the Part right of the first -
FROM sysjobs

SELECT JobName
FROM #jobs
GROUP BY JobName
HAVING COUNT(*) < 4
ORDER BY JobName

DROP TABLE #jobs

You can use the STUFF() function to put all the Server Names in one Column and only count the Job Names.

SELECT JobName, STUFF((
SELECT ',' + CAST([ServerName] AS VARCHAR(MAX))
FROM sysjobs
WHERE results.JobNames = sysjobs.JobNames
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,1,'') AS ServerNames
FROM #jobs results
GROUP BY JobName
HAVING COUNT(*) < 4
ORDER BY JobName

I've managed to get around it another way. I've now setup a table on my CMS server and I have a stored procedure that will populate the table with details on agent jobs from all servers in the AG.
From there I can then run the following query and it works like a charm:

SELECT ServerName
,JobName
FROM AgentJobs
WHERE JobName IN(
SELECT JobName
FROM AgentJobs
WHERE JobCategory = 'Production'
GROUP BY JobName
HAVING COUNT(*) < 4)