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); }