public JobStepsActions(
CDataContainer dataContainer,
JobData jobData,
AgentJobStepInfo stepInfo,
ConfigAction configAction)
{
this.configAction = configAction;
this.DataContainer = dataContainer;
this.jobData = jobData;
if (configAction == ConfigAction.Create)
{
this.data = new JobStepData(jobData.JobSteps);
}
else
{
JobStep jobStep = GetJobStep(this.jobData, stepInfo.StepName);
this.data = new JobStepData(jobStep, jobData.JobSteps);
}
// load properties from AgentJobStepInfo
this.data.ID = stepInfo.Id;
this.data.Name = stepInfo.StepName;
this.data.Command = stepInfo.Command;
this.data.Subsystem = AgentUtilities.ConvertToAgentSubSytem(stepInfo.SubSystem);
}
/// <summary>
/// Handle request to get Agent Job history
/// </summary>
internal async Task HandleJobHistoryRequest(AgentJobHistoryParams parameters, RequestContext <AgentJobHistoryResult> requestContext)
{
await Task.Run(async() =>
{
try
{
var result = new AgentJobHistoryResult();
ConnectionInfo connInfo;
ConnectionServiceInstance.TryFindConnection(
parameters.OwnerUri,
out connInfo);
if (connInfo != null)
{
ConnectionServiceInstance.TryFindConnection(parameters.OwnerUri, out connInfo);
CDataContainer dataContainer = CDataContainer.CreateDataContainer(connInfo, databaseExists: true);
var jobs = dataContainer.Server.JobServer.Jobs;
Tuple <SqlConnectionInfo, DataTable, ServerConnection> tuple = CreateSqlConnection(connInfo, parameters.JobId);
SqlConnectionInfo sqlConnInfo = tuple.Item1;
DataTable dt = tuple.Item2;
ServerConnection connection = tuple.Item3;
int count = dt.Rows.Count;
List <AgentJobHistoryInfo> jobHistories = new List <AgentJobHistoryInfo>();
if (count > 0)
{
var job = dt.Rows[0];
string jobName = Convert.ToString(job[AgentUtilities.UrnJobName], System.Globalization.CultureInfo.InvariantCulture);
Guid jobId = (Guid)job[AgentUtilities.UrnJobId];
int runStatus = Convert.ToInt32(job[AgentUtilities.UrnRunStatus], System.Globalization.CultureInfo.InvariantCulture);
var t = new LogSourceJobHistory(jobName, sqlConnInfo, null, runStatus, jobId, null);
var tlog = t as ILogSource;
tlog.Initialize();
var logEntries = t.LogEntries;
// Send Steps, Alerts and Schedules with job history in background
JobStepCollection steps = jobs[jobName].JobSteps;
JobScheduleCollection schedules = jobs[jobName].JobSchedules;
List <Alert> alerts = new List <Alert>();
foreach (Alert alert in dataContainer.Server.JobServer.Alerts)
{
if (alert.JobID == jobId && alert.JobName == jobName)
{
alerts.Add(alert);
}
}
jobHistories = AgentUtilities.ConvertToAgentJobHistoryInfo(logEntries, job, steps, schedules, alerts);
tlog.CloseReader();
}
result.Jobs = jobHistories.ToArray();
result.Success = true;
connection.Disconnect();
await requestContext.SendResult(result);
}
}
catch (Exception e)
{
await requestContext.SendError(e);
}
});
}
public static List <AgentJobHistoryInfo> ConvertToAgentJobHistoryInfo(List <ILogEntry> logEntries,
DataRow jobRow, JobStepCollection steps, JobScheduleCollection schedules, List <Alert> alerts)
{
List <AgentJobHistoryInfo> jobs = new List <AgentJobHistoryInfo>();
// get all the values for a job history
foreach (ILogEntry entry in logEntries)
{
// Make a new AgentJobHistoryInfo object
var jobHistoryInfo = new AgentJobHistoryInfo();
jobHistoryInfo.InstanceId = Convert.ToInt32(jobRow[UrnInstanceID], System.Globalization.CultureInfo.InvariantCulture);
jobHistoryInfo.JobId = (Guid)jobRow[UrnJobId];
var logEntry = entry as LogSourceJobHistory.LogEntryJobHistory;
jobHistoryInfo.RunStatus = entry.Severity == SeverityClass.Error ? 0 : 1;
jobHistoryInfo.SqlMessageId = logEntry.SqlMessageID;
jobHistoryInfo.Message = logEntry.Message;
jobHistoryInfo.StepId = logEntry.StepID;
jobHistoryInfo.StepName = logEntry.StepName;
jobHistoryInfo.SqlSeverity = logEntry.SqlSeverity;
jobHistoryInfo.JobName = logEntry.JobName;
jobHistoryInfo.RunDate = entry.PointInTime;
jobHistoryInfo.RunDuration = logEntry.Duration;
jobHistoryInfo.OperatorEmailed = logEntry.OperatorEmailed;
jobHistoryInfo.OperatorNetsent = logEntry.OperatorNetsent;
jobHistoryInfo.OperatorPaged = logEntry.OperatorPaged;
jobHistoryInfo.RetriesAttempted = logEntry.RetriesAttempted;
jobHistoryInfo.Server = logEntry.Server;
// Add steps to the job if any
var jobSteps = new List <AgentJobStep>();
foreach (JobStep step in steps)
{
var jobId = jobRow[UrnJobId].ToString();
jobSteps.Add(AgentUtilities.ConvertToAgentJobStepInfo(step, logEntry, jobId));
}
jobHistoryInfo.Steps = jobSteps.ToArray();
// Add schedules to the job if any
var jobSchedules = new List <AgentScheduleInfo>();
foreach (JobSchedule schedule in schedules)
{
jobSchedules.Add(AgentUtilities.ConvertToAgentScheduleInfo(schedule));
}
jobHistoryInfo.Schedules = jobSchedules.ToArray();
// Add alerts to the job if any
var jobAlerts = new List <AgentAlertInfo>();
foreach (Alert alert in alerts)
{
jobAlerts.Add(AgentUtilities.ConvertToAgentAlertInfo(alert));
}
jobHistoryInfo.Alerts = jobAlerts.ToArray();
jobs.Add(jobHistoryInfo);
}
return(jobs);
}
/// <summary>
/// Handle request to get Agent Job history
/// </summary>
internal async Task HandleJobHistoryRequest(AgentJobHistoryParams parameters, RequestContext <AgentJobHistoryResult> requestContext)
{
await Task.Run(async() =>
{
try
{
var result = new AgentJobHistoryResult();
ConnectionInfo connInfo;
ConnectionServiceInstance.TryFindConnection(
parameters.OwnerUri,
out connInfo);
if (connInfo != null)
{
Tuple <SqlConnectionInfo, DataTable, ServerConnection> tuple = CreateSqlConnection(connInfo, parameters.JobId);
SqlConnectionInfo sqlConnInfo = tuple.Item1;
DataTable dt = tuple.Item2;
ServerConnection connection = tuple.Item3;
int count = dt.Rows.Count;
List <AgentJobHistoryInfo> jobHistories = new List <AgentJobHistoryInfo>();
if (count > 0)
{
var job = dt.Rows[0];
string jobName = Convert.ToString(job[AgentUtilities.UrnJobName], System.Globalization.CultureInfo.InvariantCulture);
Guid jobId = (Guid)job[AgentUtilities.UrnJobId];
int runStatus = Convert.ToInt32(job[AgentUtilities.UrnRunStatus], System.Globalization.CultureInfo.InvariantCulture);
var t = new LogSourceJobHistory(jobName, sqlConnInfo, null, runStatus, jobId, null);
var tlog = t as ILogSource;
tlog.Initialize();
var logEntries = t.LogEntries;
jobHistories = AgentUtilities.ConvertToAgentJobHistoryInfo(logEntries, job);
tlog.CloseReader();
}
result.Jobs = jobHistories.ToArray();
result.Success = true;
connection.Disconnect();
await requestContext.SendResult(result);
}
}
catch (Exception e)
{
await requestContext.SendError(e);
}
});
}
public static List <AgentJobHistoryInfo> ConvertToAgentJobHistoryInfo(List <ILogEntry> logEntries, DataRow jobRow)
{
List <AgentJobHistoryInfo> jobs = new List <AgentJobHistoryInfo>();
// get all the values for a job history
foreach (ILogEntry entry in logEntries)
{
// Make a new AgentJobHistoryInfo object
var jobHistoryInfo = new AgentJobHistoryInfo();
jobHistoryInfo.InstanceId = Convert.ToInt32(jobRow[UrnInstanceID], System.Globalization.CultureInfo.InvariantCulture);
jobHistoryInfo.RunStatus = Convert.ToInt32(jobRow[UrnRunStatus], System.Globalization.CultureInfo.InvariantCulture);
jobHistoryInfo.JobId = (Guid)jobRow[UrnJobId];
var logEntry = entry as LogSourceJobHistory.LogEntryJobHistory;
jobHistoryInfo.SqlMessageId = logEntry.SqlMessageID;
jobHistoryInfo.Message = logEntry.Message;
jobHistoryInfo.StepId = logEntry.StepID;
jobHistoryInfo.StepName = logEntry.StepName;
jobHistoryInfo.SqlSeverity = logEntry.SqlSeverity;
jobHistoryInfo.JobName = logEntry.JobName;
jobHistoryInfo.RunDate = entry.PointInTime;
jobHistoryInfo.RunDuration = logEntry.Duration;
jobHistoryInfo.OperatorEmailed = logEntry.OperatorEmailed;
jobHistoryInfo.OperatorNetsent = logEntry.OperatorNetsent;
jobHistoryInfo.OperatorPaged = logEntry.OperatorPaged;
jobHistoryInfo.RetriesAttempted = logEntry.RetriesAttempted;
jobHistoryInfo.Server = logEntry.Server;
// Add steps to the job if any
var jobSteps = new List <AgentJobStep>();
if (entry.CanLoadSubEntries)
{
foreach (ILogEntry step in entry.SubEntries)
{
jobSteps.Add(AgentUtilities.ConvertToAgentJobStep(step, jobRow));
}
}
jobHistoryInfo.Steps = jobSteps.ToArray();
jobs.Add(jobHistoryInfo);
}
return(jobs);
}
/// <summary>
/// Handle request to get Agent job activities
/// </summary>
internal async Task HandleAgentJobsRequest(AgentJobsParams parameters, RequestContext <AgentJobsResult> requestContext)
{
await Task.Run(async() =>
{
try
{
var result = new AgentJobsResult();
ConnectionInfo connInfo;
ConnectionServiceInstance.TryFindConnection(
parameters.OwnerUri,
out connInfo);
if (connInfo != null)
{
var sqlConnection = ConnectionService.OpenSqlConnection(connInfo);
var serverConnection = new ServerConnection(sqlConnection);
var fetcher = new JobFetcher(serverConnection);
var filter = new JobActivityFilter();
this.jobs = fetcher.FetchJobs(filter);
var agentJobs = new List <AgentJobInfo>();
if (this.jobs != null)
{
foreach (var job in this.jobs.Values)
{
agentJobs.Add(AgentUtilities.ConvertToAgentJobInfo(job));
}
}
result.Success = true;
result.Jobs = agentJobs.ToArray();
sqlConnection.Close();
}
await requestContext.SendResult(result);
}
catch (Exception e)
{
await requestContext.SendError(e);
}
});
}
/// <summary>
/// a function which fetches notebooks jobs accessible to the user
/// </summary>
/// <param name="connInfo">connectionInfo generated from OwnerUri</param>
/// <returns>array of agent notebooks</returns>
public static async Task <AgentNotebookInfo[]> GetAgentNotebooks(ConnectionInfo connInfo)
{
AgentNotebookInfo[] result;
// Fetching all agent Jobs accessible to the user
var serverConnection = ConnectionService.OpenServerConnection(connInfo);
var fetcher = new JobFetcher(serverConnection);
var filter = new JobActivityFilter();
var jobs = fetcher.FetchJobs(filter);
Dictionary <Guid, JobProperties> allJobsHashTable = new Dictionary <Guid, JobProperties>();
if (jobs != null)
{
foreach (var job in jobs.Values)
{
allJobsHashTable.Add(job.JobID, job);
}
}
// Fetching notebooks across all databases accessible by the user
string getJobIdsFromDatabaseQueryString =
@"
DECLARE @script AS VARCHAR(MAX)
SET @script =
'
USE [?];
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = N''notebooks''
AND
TABLE_NAME = N''nb_template''
)
BEGIN
SELECT
[notebooks].[nb_template].job_id,
[notebooks].[nb_template].template_id,
[notebooks].[nb_template].last_run_notebook_error,
[notebooks].[nb_template].execute_database,
DB_NAME() AS db_name
FROM [?].notebooks.nb_template
INNER JOIN
msdb.dbo.sysjobs
ON
[?].notebooks.nb_template.job_id = msdb.dbo.sysjobs.job_id
END
'
EXEC sp_MSforeachdb @script";
var agentNotebooks = new List <AgentNotebookInfo>();
DataSet jobIdsDataSet = await ExecuteSqlQueries(connInfo, getJobIdsFromDatabaseQueryString);
foreach (DataTable templateTable in jobIdsDataSet.Tables)
{
foreach (DataRow templateRow in templateTable.Rows)
{
AgentNotebookInfo notebookJob =
AgentUtilities.ConvertToAgentNotebookInfo(allJobsHashTable[(Guid)templateRow["job_id"]]);
notebookJob.TemplateId = templateRow["template_id"] as string;
notebookJob.TargetDatabase = templateRow["db_name"] as string;
notebookJob.LastRunNotebookError = templateRow["last_run_notebook_error"] as string;
notebookJob.ExecuteDatabase = templateRow["execute_database"] as string;
agentNotebooks.Add(notebookJob);
}
}
result = agentNotebooks.ToArray();
return(result);
}
