function get-dbdata {
[CmdletBinding()]
param (
[string]$connectionString,
[string]$query,
[switch]$isSQLServer
)
#Credit for Function goes to Don Jones
if ($isSQLServer) {
Write-Verbose 'in SQL Server mode'
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
} else {
Write-Verbose 'in OleDB mode'
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
}
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
if ($isSQLServer) {
$adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
} else {
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
}
$SQLdataset = New-Object -TypeName 'System.Data.DataSet'
$adapter.Fill($SQLdataset)|out-null
$SQLdataset.Tables[0]
}
$query = @'
SELECT
[sJOB].[name] AS [JobName]
, CASE [sJOB].[enabled]
WHEN 1 THEN '1'
WHEN 0 THEN '0'
END AS [IsEnabled]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN '0'
ELSE '1'
END AS [IsScheduled]
, [sSVR].[name] AS [OriginatingServerName]
, CASE WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration]
, [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [freq_type]
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPUs become idle'
END [Occurrence]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
Where Sjstp.command like '%SmsqlJobLauncher.exe%'
'@
$D ="."+ [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().name
$instances= gwmi -ComputerName $server -Class win32_service -Filter "Name='MSSQLSERVER' or name like '%MSSQL$%'"|%{
if($_.name -like "*`$*"){($_.name -split '\$')[1]}
else{$_.name}
}
$serverfromsnapmgr = (Get-ItemProperty -Path 'HKLM:\software\Network Appliance\SnapManager For SQL Server\Client' -Name MountSQLServer).MountSQLServer
Home
Show References: