How to fetch Episerver job logs

eeta

Vote:

Hello,

We are creating an API for an external health check service that we use. For that we would need to create a custom API that receives a start datetime and end datetime. The API then returns a JSON with all of the Episerver jobs run during the timeframe, with the information wether they we successfull or failed and forexample their runtime.

The issue is that the required logs don't seem to be easily available. For example the IScheduledJobRepository has only the last job run available. There is also IScheduledJobLogRepository available but that only seems to have GetAsync available that requires a start index and max count. I could seemingly use that one to fetch everything from the database and then filter the fetched logs to match the given timeframe. This however doesn't seem a smart way to do this and it will probably be slow (and get even slower with time)

How would you recommend this to be implemented

#302054

May 19, 2023 12:30

Quan Mai

Vote:

Good question (and research)! I always recommend to use the APIs if possible, but this is one of the case when direct database access might be justified. You can query data from

tblScheduledItemLog and filter by Exec column - but with a caveat. the column is not indexed so the query will be slow, and you might solve that by adding an index (which gets you into grey area). You can copy the data to another table which allows you more flexibilty to add index as you want, however

#302090

May 20, 2023 0:27

Manoj Kumawat

Vote:

You can do what Optimizely does in the background to get history of the job. It uses a storedProcedure netSchedulerListLog to fetch the results.

        public virtual Task<PagedScheduledJobLogResult> GetJobLogAsync(Guid id, long startIndex, int maxCount)
        {
            IAsyncDatabaseExecutor db = _databaseHandler();
            return db.ExecuteAsync(async delegate
            {
                using DbCommand cmd = db.CreateStoredProcedure("netSchedulerListLog");
                cmd.Parameters.Add(db.CreateParameter("pkID", id));
                cmd.Parameters.Add(db.CreateParameter("startIndex", startIndex));
                cmd.Parameters.Add(db.CreateParameter("maxCount", maxCount));
                long? totalCount = null;
                using DbDataReader dbDataReader = await cmd.ExecuteReaderAsync().ConfigureAwait(continueOnCapturedContext: false);
                List<ScheduledJobLogItem> list = new List<ScheduledJobLogItem>();
                while (dbDataReader.Read())
                {
                    if (!totalCount.HasValue)
                    {
                        totalCount = dbDataReader.GetInt64(6);
                    }

                    object obj = dbDataReader[3];
                    object obj2 = dbDataReader[4];
                    object obj3 = dbDataReader[5];
                    object obj4 = dbDataReader[1];
                    list.Add(new ScheduledJobLogItem
                    {
                        CompletedUtc = dbDataReader.GetDateTime(0),
                        Message = dbDataReader.GetStringSafe(2),
                        Status = ((obj4 != DBNull.Value) ? ((ScheduledJobExecutionStatus)Convert.ToInt32(obj4)) : ScheduledJobExecutionStatus.Unknown),
                        Duration = ((obj != DBNull.Value) ? new TimeSpan?(TimeSpan.FromTicks(Convert.ToInt64(obj))) : null),
                        Trigger = ((obj2 != DBNull.Value) ? ((ScheduledJobTrigger)Convert.ToInt32(obj2)) : ScheduledJobTrigger.Unknown),
                        Server = ((obj3 != DBNull.Value) ? Convert.ToString(obj3) : null)
                    });
                }

                return new PagedScheduledJobLogResult(list, totalCount.GetValueOrDefault());
            });
        }

You can do something similar - Add your own stored procedure with date filters on as migration step. It should almost be same procedure with one change to include date range.

I didn't test it but it should work. If nothing works at all then you can do what Quan suggested here.

#302640

May 30, 2023 8:40