/// <summary> /// Check if a category matches given jobproperty /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckIfCategoryMatchesJob(JobActivityFilter filter, JobProperties jobProperties) { bool isCategoryMatched = false; // Apply category filter if specified if (filter.Category.Length > 0) { // // we count it as a match if the job category contains // a case-insensitive match for the filter string. // string jobCategory = jobProperties.Category.ToLower(CultureInfo.CurrentCulture); if (String.Compare(jobCategory, filter.Category.Trim().ToLower(CultureInfo.CurrentCulture), StringComparison.Ordinal) == 0) { isCategoryMatched = true; } } else { // No category filter was specified isCategoryMatched = true; } return(isCategoryMatched); }
/// <summary> /// Check if name filter specified matches given jobproperty /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckIfNameMatchesJob(JobActivityFilter filter, JobProperties jobProperties) { bool isNameMatched = false; // // job name (can be comma-separated list) // we count it as a match if the job name contains // a case-insensitive match for any of the filter strings. // if (filter.Name.Length > 0) { string jobname = jobProperties.Name.ToLower(CultureInfo.CurrentCulture); string[] jobNames = filter.Name.ToLower(CultureInfo.CurrentCulture).Split(','); int length = jobNames.Length; for (int j = 0; j < length; ++j) { if (jobname.IndexOf(jobNames[j].Trim(), StringComparison.Ordinal) > -1) { isNameMatched = true; break; } } } else { // No name filter was specified isNameMatched = true; } return(isNameMatched); }
/// <summary> /// Check if job enabled status matches job /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckIfEnabledStatusMatchesJob(JobActivityFilter filter, JobProperties jobProperties) { bool isEnabledMatched = false; // apply filter - if job was enabled or not switch (filter.Enabled) { // if All was selected, include in match case EnumThreeState.All: isEnabledMatched = true; break; // if Yes was selected, include only if job has schedule case EnumThreeState.Yes: if (jobProperties.Enabled) { isEnabledMatched = true; } break; // if Yes was selected, include only if job does not have schedule case EnumThreeState.No: if (!jobProperties.Enabled) { isEnabledMatched = true; } break; } return(isEnabledMatched); }
/// <summary> /// check if job runnable status in filter matches given job property /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckJobRunnableStatusMatchesJob(JobActivityFilter filter, JobProperties jobProperties) { bool isRunnableMatched = false; // filter based on job runnable switch (filter.Runnable) { // if All was selected, include in match case EnumThreeState.All: isRunnableMatched = true; break; // if Yes was selected, include only if job that is runnable case EnumThreeState.Yes: if (jobProperties.Runnable) { isRunnableMatched = true; } break; // if Yes was selected, include only if job is not runnable case EnumThreeState.No: if (!jobProperties.Runnable) { isRunnableMatched = true; } break; } return(isRunnableMatched); }
// // ServerConnection object should be passed from caller, // who gets it from CDataContainer.ServerConnection // public Dictionary <Guid, JobProperties> FetchJobs(JobActivityFilter filter) { string urn = server.JobServer.Urn.Value + "/Job"; if (filter != null) { urn += filter.GetXPathClause(); return(FilterJobs(FetchJobs(urn), filter)); } return(FetchJobs(urn)); }
/// <summary> /// Check if next run date for given job property is greater than the one specified in the filter /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckifNextRunDateIsGreater(JobActivityFilter filter, JobProperties jobProperties) { bool isNextRunOutDateMatched = false; // filter next run date if (filter.NextRunDate.Ticks == 0 || jobProperties.NextRun >= filter.NextRunDate) { isNextRunOutDateMatched = true; } return(isNextRunOutDateMatched); }
/// <summary> /// Check if job status filter matches given jobproperty /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckIfJobStatusMatchesJob(JobActivityFilter filter, JobProperties jobProperties) { bool isStatusMatched = false; // filter - job run status if (filter.Status == EnumStatus.All || jobProperties.CurrentExecutionStatus == (int)filter.Status) { isStatusMatched = true; } return(isStatusMatched); }
/// <summary> /// check if last run status filter matches given job property /// </summary> /// <param name="filter"></param> /// <param name="jobProperties"></param> /// <returns></returns> private bool CheckIfLastRunOutcomeMatchesJob(JobActivityFilter filter, JobProperties jobProperties) { bool isLastRunOutcomeMatched = false; // filter - last run outcome if (filter.LastRunOutcome == EnumCompletionResult.All || jobProperties.LastRunOutcome == (int)filter.LastRunOutcome) { isLastRunOutcomeMatched = true; } return(isLastRunOutcomeMatched); }
/// <summary> /// creates a shallow clone /// </summary> /// <returns></returns> object IFilterDefinition.ShallowClone() { JobActivityFilter clone = new JobActivityFilter(); clone.LastRunDate = this.LastRunDate; clone.NextRunDate = this.NextRunDate; clone.Name = this.Name; clone.Category = this.Category; clone.Enabled = this.Enabled; clone.Status = this.Status; clone.Runnable = this.Runnable; clone.Scheduled = this.Scheduled; clone.LastRunOutcome = this.LastRunOutcome; (clone as IFilterDefinition).Enabled = (this as IFilterDefinition).Enabled; return(clone); }
/// <summary> /// setup-s filter definition based on a template /// </summary> /// <param name="template"></param> void IFilterDefinition.ShallowCopy(object template) { System.Diagnostics.Debug.Assert(template is JobActivityFilter); JobActivityFilter f = template as JobActivityFilter; this.LastRunDate = f.LastRunDate; this.NextRunDate = f.NextRunDate; this.Name = f.Name; this.Category = f.Category; this.Enabled = f.Enabled; this.Status = f.Status; this.Runnable = f.Runnable; this.Scheduled = f.Scheduled; this.LastRunOutcome = f.LastRunOutcome; (this as IFilterDefinition).Enabled = (template as IFilterDefinition).Enabled; }
/// <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> /// Filter Jobs that matches criteria specified in JobActivityFilter /// here we filter jobs by properties that enumerator doesn't /// support filtering on. /// $ISSUE - - DevNote: Filtering Dictionaries can be easily done with Linq and System.Expressions in .NET 3.5 /// This requires re-design of current code and might impact functionality / performance due to newer dependencies /// We need to consider this change in future enhancements for Job Activity monitor /// </summary> /// <param name="unfilteredJobs"></param> /// <param name="filter"></param> /// <returns></returns> private Dictionary <Guid, JobProperties> FilterJobs(Dictionary <Guid, JobProperties> unfilteredJobs, JobActivityFilter filter) { if (unfilteredJobs == null) { return(null); } if (filter == null || (filter is IFilterDefinition && ((filter as IFilterDefinition).Enabled == false || (filter as IFilterDefinition).IsDefault()))) { return(unfilteredJobs); } Dictionary <Guid, JobProperties> filteredJobs = new Dictionary <Guid, JobProperties>(); // Apply Filter foreach (JobProperties jobProperties in unfilteredJobs.Values) { // If this job passed all filter criteria then include in filteredJobs Dictionary if (this.CheckIfNameMatchesJob(filter, jobProperties) && this.CheckIfCategoryMatchesJob(filter, jobProperties) && this.CheckIfEnabledStatusMatchesJob(filter, jobProperties) && this.CheckIfScheduledStatusMatchesJob(filter, jobProperties) && this.CheckIfJobStatusMatchesJob(filter, jobProperties) && this.CheckIfLastRunOutcomeMatchesJob(filter, jobProperties) && this.CheckIfLastRunDateIsGreater(filter, jobProperties) && this.CheckifNextRunDateIsGreater(filter, jobProperties) && this.CheckJobRunnableStatusMatchesJob(filter, jobProperties)) { filteredJobs.Add(jobProperties.JobID, jobProperties); } } return(filteredJobs); }
/// <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); }