October 1, 2018 at 8:15 am
Hi
I would like to know how to use CMS to schedule a sql statement which checks for failed sql agent jobs in a registered server group from within a central management server and then email the results each day.
Thanks in advance
October 1, 2018 at 8:38 am
it's not a one line query, unfortunately.
SSMS opens a query to each server, executes it, and aggregates the results.
You will have to do the same, and it's a large-ish job, especially if the queries are going to be dynamic and change over time
This is how I do it:
I created an SSIS package that loops through each server in CMS, uses a script task to execute the queries and insert into a DataSet, and finally creates a file or email to send out.
the advantage of a script task + Dataset is the data table can discover the column names at execution time, and not be pre-established meta-data like a normal SSIS package requires.
querying CMS is fairly easy, the rest of it is the hard part. this assumes a fairly deep organization structure so you can visualize and filter better.
[
;WITH MyCTE
AS
(
SELECT
ISNULL(greatgrpz.[name],'-->') AS GreatParentGroupName,
ISNULL(ggrpz.[name],'-->') AS GParentGroupName,
ISNULL(pgrpz.[name],'-->') AS ParentGroupName,
[grpz].[name] AS GroupName,
srvz.[server_name] AS ServerName,
[grpz].Parent_ID,
[grpz].[server_group_id],
srvz.server_id,
srvz.description
FROM msdb.dbo.sysmanagement_shared_registered_servers srvz
INNER JOIN msdb.dbo.[sysmanagement_shared_server_groups] grpz
ON srvz.[server_group_id] = [grpz].[server_group_id]
LEFT JOIN msdb.dbo.[sysmanagement_shared_server_groups] pgrpz
ON pgrpz.[server_group_id] = [grpz].[Parent_ID]
LEFT JOIN msdb.dbo.[sysmanagement_shared_server_groups] ggrpz
ON ggrpz.[server_group_id] = [pgrpz].[Parent_ID]
LEFT JOIN msdb.dbo.[sysmanagement_shared_server_groups] greatgrpz
ON greatgrpz.[server_group_id] = [ggrpz].[Parent_ID]
)
SELECT DISTINCT ServerName, GroupName,
CASE
WHEN CHARINDEX('.',ServerName) > 0
THEN SUBSTRING(ServerName,1,CHARINDEX('.',ServerName) -1)
WHEN CHARINDEX('\',ServerName) > 0
THEN SUBSTRING(ServerName,1,CHARINDEX('\',ServerName) -1)
ELSE ServerName
END AS VirtualMachineName
FROM MyCTE
WHERE 1 = 1
--AND MyCTE.ParentGroupName LIKE '%NewYork%'
AND MyCTE.GroupName ='Prod' --all my "prodction server" are in a folder named "Prod"
ORDER BY ServerName
Lowell
October 1, 2018 at 9:51 am
May I suggest using powershell instead of relying on CMS for this. As Lowell mentioned, with CMS, SSMS basically opens a query to each server, executes it, and aggregates the results. See this blog post.
https://sqlpowershell.blog/2016/03/02/find-failed-sql-job-in-last-24-hours-using-powershell/
You can simply feed the input txt file with list of your SQL Instances to monitor and you can schedule to run this PS1 script as needed.
October 1, 2018 at 9:57 am
Sreekanth B - Monday, October 1, 2018 9:51 AMMay I suggest using powershell instead of relying on CMS for this. As Lowell mentioned, with CMS, SSMS basically opens a query to each server, executes it, and aggregates the results. See this blog post.
https://sqlpowershell.blog/2016/03/02/find-failed-sql-job-in-last-24-hours-using-powershell/
You can simply feed the input txt file with list of your SQL Instances to monitor and you can schedule to run this PS1 script as needed.
Or, you can query your CMS for the list of servers
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 1, 2018 at 12:45 pm
@Sreekanth B & Michael L John Thanks for the responses.
I can query the cms for the server instances in advance and put them in the .txt file.
How should that txt file format be? comma separated? like so:
server1\instance2, server5, server6\instanceb, etc, etc etc ??
October 1, 2018 at 1:29 pm
caz100 - Monday, October 1, 2018 12:45 PM@Sreekanth B & Michael L John Thanks for the responses.I can query the cms for the server instances in advance and put them in the .txt file.
How should that txt file format be? comma separated? like so:
server1\instance2, server5, server6\instanceb, etc, etc etc ??
place each instance in a new line. Something like this...
server1\instance2
server5
server6\instanceb
October 1, 2018 at 1:32 pm
here's an explicit string array declaration example for powershell:MyServers=@("VMSQL01","VMSQL02\SQL2016","AnotherSrver\ETL")
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply