Persisting history of job on DB sql

[Netclick19]

Hi,

is it possibile to use a history Persistent Store with SilkierQuartz?
I only saw InProcExecutionHistoryStore

Thank you for your support.

[francoax]

Hi @Netclick19,
Persistent history isn't natively supported... so I implemented my own job listener to handle it. Here's the code if you're interested.
IJobPersister is a custom interface I defined—it just contains the methods to connect to my DB. You're free to implement it however suits your setup.
Note that I'm using Quartz and Silkier only for the dashboard. I'm passing my Quartz scheduler into Silkier's.
You just need to add a new table to store your execution history.

internal class JobExecutionListenerPersister(
    ILogger<JobExecutionListenerPersister> _logger,
    IServiceScopeFactory _scopeFactory
    ) : IJobListener
{
    public string Name => "JobExecutionListenerPersister";

    public Task JobExecutionVetoed(IJobExecutionContext context, CancellationToken cancellationToken = default)
    {
        _logger.LogWarning("Job execution vetoed for job: {JobKey}", context.JobDetail.Key);
        return Task.CompletedTask;
    }

    /// <summary>
    /// Permite loguear el trabajo encolado antes de su ejecucion. QUARTZ.NET ejecuta este codigo en el momento que el job entro para ser ejecutado.
    /// </summary>
    /// <param name="context">Contexto dado por Quartz</param>
    /// <param name="cancellationToken">CancellationToken</param>
    /// <returns>Task</returns>
    public async Task JobToBeExecuted(IJobExecutionContext context, CancellationToken cancellationToken = default)
    {
        _logger.LogInformation("Job to be executed: {JobKey}", context.JobDetail.Key);

        try
        {
            await using var scope = _scopeFactory.CreateAsyncScope();
            IJobPersister loggin = scope.ServiceProvider.GetRequiredService<IJobPersister>();

            JobExecutionLogEntity log = await loggin.LogJobToExecute(new JobToExecuteEntity
            {
                JobKey = context.JobDetail.Key.ToString(),
                JobGroup = context.JobDetail.Key.Group,
                JobData = JsonHelper.Serialize(context.MergedJobDataMap),
            });

            context.MergedJobDataMap?.Put("LogId", log.LogId.ToString());
        }
        catch (OracleException ex)
        {
            _logger.LogError(ex, "Error logging job execution on database for job: {JobKey}", context.JobDetail.Key);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error logging job to be executed for job: {JobKey}", context.JobDetail.Key);
        }
    }

    /// <summary>
    /// Quartz.Net llama este metodo al finalizar la ejecucion de un trabajo, ya sea de forma correcta o con alguna excepcion ocurrida.
    /// </summary>
    /// <param name="context"></param>
    /// <param name="jobException">Es proveido por Quartz.Net. Si no es null, significa que hubo una excepcion en la ejecucion del trabajo. Esto nos permite saber el fallo.</param>
    /// <param name="cancellationToken"></param>
    /// <returns>Task</returns>
    public async Task JobWasExecuted(IJobExecutionContext context, JobExecutionException? jobException, CancellationToken cancellationToken = default)
    {
        _logger.LogInformation("Job was executed: {JobKey}, Result: {Result} Exception: {Exception}", context.JobDetail.Key, context.Result, jobException?.Message);

        if (!context.MergedJobDataMap.TryGetInt("LogId", out int logId))
        {
            _logger.LogWarning("LogId on JobDataMap is not set for job: {JobKey}", context.JobDetail.Key);

            return;
        }

        try
        {
            await using var scope = _scopeFactory.CreateAsyncScope();
            IJobPersister loggin = scope.ServiceProvider.GetRequiredService<IJobPersister>();

            JobExecutionLogEntity log = new()
            {
                LogId = logId,
                JobKey = context.JobDetail.Key.ToString(),
                JobGroup = context.JobDetail.Key.Group,
                State = (jobException, context.Result) switch
                {
                    (not null, not null) => "WARNING",
                    (null, not null) => "EXECUTED",
                    _ => "FAILED"
                },
                ScheduledTime = context.ScheduledFireTimeUtc.GetValueOrDefault().LocalDateTime,
                FiredTime = context.FireTimeUtc.LocalDateTime,
                Runtime = context.JobRunTime.TotalMilliseconds,
                Result = string.Concat(context.Result?.ToString(),
                    "\n",
                    jobException?.Message,
                    "\n",
                    jobException?.InnerException?.ToString())
            };

            _logger.LogInformation("Logging job execution: {Log}", JsonHelper.Serialize(log));

            await loggin.LogExecutedJob(log);
        }
        catch (OracleException ex)
        {
            _logger.LogError(ex, "Error logging job execution on database for job: {JobKey}", context.JobDetail.Key);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Unhandled Error logging job execution for job: {JobKey}", context.JobDetail.Key);
        }
    }
}

[Netclick19]

Hi,

thank you so much for your code!
Mabye can you share the create table script?

IJobPersister and JobExecutionLogEntity how are defined?

Thank you!