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