SQL Server Agent Job Monitoring with PowerShell
问题
若要继续该 技巧系列，此PowerShell脚本将收集信息以为已为此监视解决方案注册的所有服务器构建SQL Server代理作业清单 。
解
该模块将从所有指定的实例中收集有关SQL Server代理作业的信息。它将连接到inventory.MasterServerList表中的每个服务器实例，并捕获每个实例的数据。
与每个SQL Server实例的连接将基于清单.MasterServerList表中的设置。
先决条件
为了使用此模块，您需要 创建在此处找到的核心对象。
数据库对象
对于此特定模块，将仅创建一个表，该表用于存储每个实例中SQL Server代理作业的信息。
这是表的结构，因此您可以了解将存储哪些数据。（如果要向该表中添加更多字段，请确保在PowerShell脚本中调整结构，并调整将用于处理其他列的相应逻辑。）
表格
-
- inventory.Jobs- this will store information about SQL Agent Jobs for all servers
- serverId - serverid ties back to inventory.MasterServerList
- job_name - name of SQL Agent job
- is_enabled - if job is enabled
- owner - owner of the job
- date_created - when job was created
- date_modified - when job was last changed
- frequency- how often job is run
- days - how often job is run
- execution_time- last execution time of the job
- data_collection_timestamp - when data was last collected
- inventory.Jobs- this will store information about SQL Agent Jobs for all servers
PowerShell脚本
创建上述对象并将数据插入清单的PowerShell脚本称为：Jobs表：
- Get-MSSQL-Instance-Jobs.ps1。
该脚本包括验证，可帮助您检查是否缺少某些关键元素才能使脚本成功运行。例如，它将确认stocking.MasterServerList表存在并且至少已注册1个活动实例以便可以使用某些东西。
PowerShell脚本应存储在集中位置。在本系列中，我们一直使用“ C：\ Temp”。
如果要更改工作目录并使用“ C：\ temp”以外的其他名称，则需要编辑PowerShell脚本并修改第一行，如下所示。将来的所有模块也将是这种情况。
如何使用
导航到创建文件的文件夹，然后可以运行PowerShell脚本，如下所示：
选项1
- 右键单击Get-MSSQL-Instance-Jobs.ps1，然后选择“使用PowerShell运行”。
选项2
- 打开命令窗口，然后导航到保存上述文件的文件夹并运行：
powershell“ C：\ temp \ Get-MSSQL-Instance-Jobs.ps1”
选项3
- 将其安排为SQL Agent作业，以在设置的基础上运行PowerShell脚本。
选项4
- 将其调度为Windows Task Scheduler作业，以在设置的基础上运行PowerShell脚本。
检查数据库和对象的创建
运行PowerShell脚本后，我们可以看到新对象创建的 ventory.Jobs。
If we query the inventory.Jobs table, we can see the data that has been collected.
Important note: the PowerShell script will store only the information from the very last execution. If you’d like to keep information from previous executions, you will have to modify the script and adapt it to your particular use case.
Checking for Errors
To check for errors query the monitoring.ErrorLog table using the following query:
SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Jobs'
If you’d like to include the SQL Server instance that had errors, issue the query like this:
SELECT
CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE
CONCAT(msl.server_name,'\',msl.instance)
END AS instance,
e.script,
e.message,
e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Jobs'
Useful Queries
By collecting all the data related to the jobs across all your instances, you might answer things like below.
Find jobs that include Backup
What’s the schedule backups across all the instances. Here we are looking for the keyword "Backup" in the job name.
SELECT
CASE WHEN msl.instance = 'MSSQLSERVER'
THEN msl.server_name
ELSE CONCAT(msl.server_name,'\',msl.instance)
END AS instance,
j.job_name,
j.frequency,
j.days,
j.execution_time
FROM inventory.Jobs j
JOIN inventory.MasterServerList msl ON j.serverId = msl.serverId
WHERE j.job_name LIKE '%Backup%'
Find jobs where owner is not sa
I would like to know which jobs have owners other than sa. Here we are looking at the owner column.
SELECT
CASE WHEN msl.instance = 'MSSQLSERVER'
THEN msl.server_name ELSE
CONCAT(msl.server_name,'\',msl.instance)
END AS instance,
j.job_name,
j.owner
FROM inventory.Jobs j
JOIN inventory.MasterServerList msl ON j.serverId = msl.serverId
WHERE j.owner <> 'sa'
script block
Get-Content "C:\temp\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server = $h.Get_Item("centralServer")
$inventoryDB = $h.Get_Item("inventoryDB")
if($server.length -eq 0){
Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
exit
}
if($inventoryDB.length -eq 0){
Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
exit
}
$mslExistenceQuery = "
SELECT Count(*) FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1
"
$result = Invoke-Sqlcmd -Query $mslExistenceQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
if($result[0] -eq 0){
Write-Host "The table [inventory].[MasterServerList] wasn't found!!!" -BackgroundColor Red
exit
}
$enoughInstancesInMSLQuery = "
SELECT COUNT(*) FROM inventory.MasterServerList WHERE is_active = 1
"
$result = Invoke-Sqlcmd -Query $enoughInstancesInMSLQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
if($result[0] -eq 0){
Write-Host "There are no active instances registered to work with!!!" -BackgroundColor Red
exit
}
if ($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0) {
$username = $h.Get_Item("username")
$password = $h.Get_Item("password")
}
#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted){
if($trusted -eq 1){
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop
}
catch{
[string]$message = $_
$errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
}
}
else{
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop
}
catch{
[string]$message = $_
$errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
}
}
}
###############################
#Jobs inventory table creation#
###############################
$jobsInventoryTableQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[Jobs]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [inventory].[Jobs](
[serverId] [INT]NOT NULL,
[job_name] [VARCHAR](128) NOT NULL,
[is_enabled] [TINYINT] NULL,
[owner] [VARCHAR](32) NULL,
[date_created] [DATETIME] NULL,
[date_modified] [DATETIME] NULL,
[frequency] [VARCHAR](32) NULL,
[days] [VARCHAR](64) NULL,
[execution_time] [VARCHAR](64) NULL,
[data_collection_timestamp] [DATETIME] NOT NULL
CONSTRAINT PK_JobsInventory PRIMARY KEY CLUSTERED (serverId,job_name),
CONSTRAINT FK_JobsInventory_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ON [PRIMARY]
END
"
Execute-Query $jobsInventoryTableQuery $inventoryDB $server 1
#TRUNCATE the inventory.Jobs table to always store a fresh copy of the information from all the instances
Execute-Query "TRUNCATE TABLE inventory.Jobs" $inventoryDB $server 1
#Select the instances from the Master Server List that will be traversed
$instanceLookupQuery = "
SELECT
serverId,
trusted,
CASE instance
WHEN 'MSSQLSERVER' THEN server_name
ELSE CONCAT(server_name,'\',instance)
END AS 'instance',
CASE instance
WHEN 'MSSQLSERVER' THEN ip
ELSE CONCAT(ip,'\',instance)
END AS 'ip',
CONCAT(ip,',',port) AS 'port'
FROM inventory.MasterServerList
WHERE is_active = 1
"
$instances = Execute-Query $instanceLookupQuery $inventoryDB $server 1
#For each instance, fetch the desired information
$jobsInformationQuery = "
SELECT
SERVERPROPERTY('SERVERNAME') AS 'instance',
sysjobs.name AS 'name',
sysjobs.enabled AS 'enabled',
SUSER_SNAME(sysjobs.owner_sid) AS 'owner',
sysjobs.date_created AS 'date_created',
sysjobs.date_modified AS 'date_modified',
CASE
WHEN freq_type = 4 THEN 'Daily'
END AS 'frequency',
'Every ' + CAST (freq_interval AS VARCHAR(3)) + ' day(s)' AS 'days',
CASE
WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' seconds ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' minutes ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' hours ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
ELSE 'Starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END AS 'execution_time'
FROM msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 4
UNION
-- jobs with a weekly schedule
SELECT
SERVERPROPERTY('SERVERNAME') AS 'instance',
sysjobs.name AS 'name',
sysjobs.enabled AS 'enabled',
SUSER_SNAME(sysjobs.owner_sid) AS 'owner',
sysjobs.date_created AS 'date_created',
sysjobs.date_modified AS 'date_modified',
CASE
WHEN freq_type = 8 THEN 'Weekly'
END AS 'frequency',
CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
AS 'Days',
CASE
WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' seconds ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' minutes ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' hours ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
ELSE 'Starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END AS 'execution_time'
FROM msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 8
UNION
-- jobs with a monthly schedule
SELECT
SERVERPROPERTY('SERVERNAME') AS 'instance',
sysjobs.name AS 'name',
sysjobs.enabled AS 'enabled',
SUSER_SNAME(sysjobs.owner_sid) AS 'owner',
sysjobs.date_created AS 'date_created',
sysjobs.date_modified AS 'date_modified',
CASE
WHEN freq_type = 16 THEN 'Monthly'
END AS 'frequency',
CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
AS 'Days',
CASE
WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' seconds ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' minutes ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7))
+ ' hours ' + 'starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
ELSE 'Starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END AS 'execution_time'
FROM msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 16
ORDER BY name
"
foreach ($instance in $instances){
if($instance.trusted -eq 'True'){$trusted = 1}else{$trusted = 0}
$sqlInstance = $instance.instance
#Go grab the complementary information for the instance
Write-Host "Fetching jobs information from instance" $instance.instance
#Special logic for cases where the instance isn't reachable by name
try{
$results = Execute-Query $jobsInformationQuery "master" $sqlInstance $trusted
}
catch{
$sqlInstance = $instance.ip
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1
try{
$results = Execute-Query $jobsInformationQuery "master" $sqlInstance $trusted
}
catch{
$sqlInstance = $instance.port
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1
try{
$results = Execute-Query $jobsInformationQuery "master" $sqlInstance $trusted
}
catch{
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1
}
}
}
#Perform the INSERT in the inventory.Jobs only if it returns information
if($results.Length -ne 0){
#Build the insert statement
$insert = "INSERT INTO inventory.Jobs VALUES"
foreach($result in $results){
$insert += "
(
'"+$instance.serverId+"',
'"+$result['name']+"',
"+$result['enabled']+",
'"+$result['owner']+"',
'"+$result['date_created']+"',
'"+$result['date_modified']+"',
'"+$result['frequency']+"',
'"+$result['days']+"',
'"+$result['execution_time']+"',
GETDATE()
),
"
}
$insert = $insert -replace "''",'NULL'
$insert = $insert -replace "NULLNULL",'NULL'
Execute-Query $insert.Substring(0,$insert.LastIndexOf(',')) $inventoryDB $server 1
}
}
Write-Host "Done!"
Thanks & Best Regards！
Javi Zhu 朱佳辉
Mobile: 15900467108
Email: Javi.zhu@outlook.com
