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