public static List <AgentNotebookHistoryInfo> ConvertToAgentNotebookHistoryInfo(List <ILogEntry> logEntries, DataRow jobRow, JobStepCollection steps) { List <AgentNotebookHistoryInfo> jobs = new List <AgentNotebookHistoryInfo>(); // get all the values for a job history foreach (ILogEntry entry in logEntries) { // Make a new AgentJobHistoryInfo object var jobHistoryInfo = new AgentNotebookHistoryInfo(); 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 (LogSourceJobHistory.LogEntryJobHistory subEntry in entry.SubEntries) { if (steps.Contains(subEntry.StepName)) { var jobId = jobRow[UrnJobId].ToString(); jobSteps.Add(AgentUtilities.ConvertToAgentJobStep(steps.ItemById(Convert.ToInt32(subEntry.StepID)), logEntry, jobId)); } } jobHistoryInfo.Steps = jobSteps.ToArray(); jobs.Add(jobHistoryInfo); } return(jobs); }
/// <summary> /// Delete a particular run of the job. Deletion mainly including clearing out the notebook, /// and notebook-error. The API doesn't delete the row because some notebook runs that have job /// error in them don't have an entry in the materialized table. For keeping track of those notebook /// runs the entry is added into the table with is_delete set to 1. /// </summary> /// <param name="connInfo">connectionInfo generated from OwnerUri</param> /// <param name="agentNotebookHistory">Actual history item to be deleted</param> /// <param name="targetDatabase">database on which the notebook history is stored</param> /// <returns></returns> public static async Task DeleteMaterializedNotebook( ConnectionInfo connInfo, AgentNotebookHistoryInfo agentNotebookHistory, string targetDatabase) { string deleteMaterializedNotebookQuery = @" IF EXISTS (SELECT * FROM notebooks.nb_materialized WHERE job_id = @jobId AND run_time = @startTime AND run_date = @startDate) BEGIN UPDATE notebooks.nb_materialized SET is_deleted = 1, notebook = '', notebook_error = '' WHERE job_id = @jobId AND run_time = @startTime AND run_date = @startDate END ELSE BEGIN INSERT INTO notebooks.nb_materialized (job_id, run_time, run_date, notebook, notebook_error, is_deleted) VALUES (@jobID, @startTime, @startDate, '', '', 1) END "; List <SqlParameter> deleteMaterializedNotebookParams = new List <SqlParameter>(); deleteMaterializedNotebookParams.Add(new SqlParameter("jobID", agentNotebookHistory.JobId)); deleteMaterializedNotebookParams.Add(new SqlParameter("startTime", agentNotebookHistory.RunDate.ToString("HHmmss"))); deleteMaterializedNotebookParams.Add(new SqlParameter("startDate", agentNotebookHistory.RunDate.ToString("yyyyMMdd"))); deleteMaterializedNotebookParams.Add(new SqlParameter("materializedId", agentNotebookHistory.MaterializedNotebookId)); await AgentNotebookHelper.ExecuteSqlQueries( connInfo, deleteMaterializedNotebookQuery, deleteMaterializedNotebookParams, targetDatabase); }
/// <summary> /// Changing the pin state of materialized notebook runs. Special case is handled where new row is /// added for failed jobs which do not have an entry into the materialized table /// </summary> /// <param name="connInfo">connectionInfo generated from OwnerUri</param> /// <param name="agentNotebookHistory">actual history item to be pinned</param> /// <param name="targetDatabase">database on which the notebook history is stored</param> /// <param name="pin">pin state for the history</param> /// <returns></returns> public static async Task UpdateMaterializedNotebookPin( ConnectionInfo connInfo, AgentNotebookHistoryInfo agentNotebookHistory, string targetDatabase, bool pin) { string updateMaterializedNotebookPinQuery = @" IF EXISTS (SELECT * FROM notebooks.nb_materialized WHERE job_id = @jobId AND run_time = @startTime AND run_date = @startDate) BEGIN UPDATE notebooks.nb_materialized SET pin = @notebookPin WHERE job_id = @jobId AND run_time = @startTime AND run_date = @startDate END ELSE BEGIN INSERT INTO notebooks.nb_materialized (job_id, run_time, run_date, notebook, notebook_error, pin) VALUES (@jobID, @startTime, @startDate, '', '', @notebookPin) END "; List <SqlParameter> updateMaterializedNotebookPinParams = new List <SqlParameter>(); updateMaterializedNotebookPinParams.Add(new SqlParameter("jobID", agentNotebookHistory.JobId)); updateMaterializedNotebookPinParams.Add(new SqlParameter("startTime", agentNotebookHistory.RunDate.ToString("HHmmss"))); updateMaterializedNotebookPinParams.Add(new SqlParameter("startDate", agentNotebookHistory.RunDate.ToString("yyyyMMdd"))); updateMaterializedNotebookPinParams.Add(new SqlParameter("notebookPin", pin)); await AgentNotebookHelper.ExecuteSqlQueries( connInfo, updateMaterializedNotebookPinQuery, updateMaterializedNotebookPinParams, targetDatabase); }