Excel VBA : Scheduled Job Validation

Hi Experts,
In attached data sample, I have scheduled jobs.
I have get Calculate the success ratio of the Job Manager across 30000 lines per month

Rules
* When job is not successful and re-run within 24hrs/ before the next  schedule job is its counted success, Failed Job will be Exclude and successful job would be Yes
* Unsuccessful/ Successful job will counted  only once per " Policy "(Column K)

* When Scheduled Job Fails and Job is run "on-Demand" is success then its one Job count and Success(activity Type -Column (I))

*Duplicate Jobs for same day would be "DUP"

Thanks
mac
Review_Validations_2014111.xlsx
LVL 7
macentrapAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Try attached file.
The macro runs when the button on Result sheet is pressed.
The macro sort the Data to get it in ascending order for Start date and Start time.
Your original input status is in column L, and column M is the row in the original file.
Column K checks if original and program match (0=match, 1=No match), so check where K is 1.
There are small differences, mostly because the program change the first duplicate to DUP, and not the second, and some of the lines marked Yes or No should be excluded, due to a later run within 24 hours.
Review_Validations_2014111.xlsm
macentrapAuthor Commented:
Hello Ejgil,
THank you so much
the Script is missing check on "Name" hence give lot to Dups and Exclude

Also, noticed some of the jobs done have time, which is also creating DUP
Have attached file with more data
Review_Validations_2014111_v2.xlsm
macentrapAuthor Commented:
Hello Ejgjil,
When I try to do SUM in results Tab
on Columns C:G the the total is less than jobs on data sheet

Regards
Mac
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Ejgil HedegaardCommented:
When comparing 2 texts to be equal in Excel, then upper and lower case letters are equal (A=a), but in VBA it is not, and that is the reason for the missing sum.
I have fixed that in the compare for DUP and Exclude, and in the counts.
Changed so the DUP is set on the last duplicate (not important for the count, but more logical).
Added Name to Result.
Added a date conversion function, because in the new file Start Date was not ascending.
Added a form to show the progress when the program run.
Review_Validations_2014111_v2.xlsm
macentrapAuthor Commented:
Hello Ejgil,
 this worked with one glitch

Once the result is run, if you check on lines 5091,5093,5098 (on revised Macro file provided above)
there should be 1 "no" though all are counted as "exclude"
for Friday, 21 November 2014 there was no successful job run for Lhrms01-fp (Windows C and D Drive)

Further, is there a possibility, how to address the issue where there is only one job run for the day which failed and the next day job was run within 24hrs.
Lines 7039, 7040 and 7049 in new attached file


Thank you so much for ongoing support , as some of the issues I am finding while reviewing month by month data
Review_Validations_2014111_v3_1.xlsm
Ejgil HedegaardCommented:
All 3 jobs line 5091, 5093 and 5098, on Friday, 21 November 2014 are initially No.
Line 5091 excluded by line 5093, and line 5093 excluded by line 5098.
Line 5098 excluded by line 5327, because it is run within 24 hours.

Line 7039 excluded by line 7041, and line 7040 excluded by line 7042.
Both run within 24 hours.

I don't see a problem with line 7049, the result is Yes.

I don't understand what to address.
The program handles the issue with a new job within 24 hours, and if the first job result is No it will be excluded.

If that is not what you want, the 24 hours check can be removed, so check is only done for the same day.
See file for only same day check.
Review_Validations_2014111_v3_2.xlsm
macentrapAuthor Commented:
Hello Ejgil

The 24hr rule of the job is valid though it does cancels the when the job should exist for the next day too.
In jobs which ran for 21 nov,  if these are counted within 24hrs then This would lead that  are no jobs which ran on 22 nov. the job schedule would have to be accounted for both days.

So was 24Dec where its valid job was run within 24 hrs though this would lead no job run for 25 dec

The only other pattern I could relate to was in job type all of them are Schedule jobs. Once Scheduled job fails the followup rerun(within24hr) to make up of the failed Scheduled is classified as on-demand job in Job type. Not sure if this can assist with success count.
Ejgil HedegaardCommented:
If the jobs have to be counted at least once for every day, I don't see the reason for the 24 hour rule, unless something else like Job type is part of the rule.

The rules to change status to Exclude when comparing the 2 jobs in version v3_1 are:
Name and Policy are equal.
1. Both jobs are on the same day and both jobs have status Yes or No.
If second job is Yes, then first job (Yes or No) change to Exclude.
If second job is No and first job is Yes, then second job change to Exclude (none match that rule).
If both jobs are No, then first job change to Exclude.
2. The 2 jobs are on different days
If second job run is within 24 hours and first job status is No, then first job status change to Exclude.
The status for the second job remains as is (Yes, No, Cancelled), unless changed by later runs.

Please check the rules.

Once Scheduled job fails the followup rerun(within24hr) to make up of the failed Scheduled is classified as on-demand job in Job type.
That is not the case for 24 and 25 December.
The 2 jobs on 25 December, run within 24 hours after the failed jobs on 24 December, are Scheduled jobs, not On-Demand.

Must Job type be part of the rule for Exclude?
And if so, please specify how.
macentrapAuthor Commented:
hello Ejgil,

Job type as criteria was requirement on legend from initial request, apologies this was not explained in detail.
*Values to be unique job is to be determined based on: Start Date, Start time, Job type, Name, Policy , Error Summary.
Only yesterday, I only found the pattern with data where VBA was not working with job type criteria.

In a given month, a client(Name) will have  scheduled  jobs, sometimes these job fail and are run on  demand once identified the scheduled job failed.

on a given day for example, 24 December  if scheduled job is for 8pm  which fails.
there can be multiple scenarios:
1. the on demand job is run on 24 December itself, which will have this as success "yes"
2. the on demand job is run next day on 25 December, which will have this as success "yes" based on job is success within 24hrs

3.as part of the schedule, the job should still be occurring for 25 December.  If this  was "fail"  then its success would be "no" for 25 December.  The on demand run which was done under Point 2 most likely would be before this schedule job time and does not mean it was for 25 December schedule.

Yes, the problem area is, if two on demand jobs are run on 25 December
a. to cover for failed job from 24 December
b. to cover for failed job from 25 December
I am not sure if both a and b scenario's can be covered with VBA, if is not possible  i will manually review these gaps (this should be a very small number to review)

With your help I am able to save 98% time on manual reviews and can also generate success stats.

Sorry if I confused  it further
Ejgil HedegaardCommented:
In VBA everything that can be described in logic is possible.
And all possible scenarios must be defined,

It is not so that only failed Scheduled jobs are followed by an On-Demand job.
Scheduled, On-Demand, Scheduled on the same day also exist.

Use the filter and select
Policy: Windows C and D Drive
Name: Lhrms01-fp
Start dates: Saturday, 8 November 2014 and Sunday, 9 November 2014
That selects 6 rows 2056, 2057, 2061, 2290, 2294 and 2297.
On Saturday: Scheduled (Fail), Scheduled (Fail), On-Demand (Success).
Does the On-Demand job Exclude the Scheduled job?
On Sunday: Scheduled (Fail), On-Demand (Success), Scheduled (Success).
Is only the last Scheduled success to be counted, or also the On-Demand success?

For the 24 hour rule.
What if the On-Demand job on Saturday fails, and a new On-Demand job runs before the others on Sunday, fail or success.
Which ones to be kept Yes, No or excluded?

Another filter: Notes, Lhrms01-fp, Friday, 14 November 2014 and Saturday, 15 November 2014.
The Scheduled on Friday ends with success, but an On-Demand job is run on Saturday with success, followed by a Scheduled job with success.

What types of Job type exclude previous jobs.
Scheduled later the same day exclude the previous Scheduled, but does On-Demand also exclude previous Scheduled, or Scheduled exclude previous On-Demand that day, and /or previous day.
Could the last job be excluded if fail, and the previous is success?

You don't have to answer the above questions, but more to indicate what is unclear.
Instead make a description of what combination of Job type (Scheduled or On-Demand), day (same or previous) and status (Yes or No) result in Exclude.
I don't say it is easy, but it is essential to make If..Then statements like in my previous post to describe all possibilities where Exclude can occur, or make a table like attached file,
Just fill where to exclude, then I will translate that to If...Then statements.
Ejgil HedegaardCommented:
Sorry, forgot the file.
Review-Validation-Exclude-Rules.xlsx
macentrapAuthor Commented:
Hello Ejgil,

Please find attached the validation rules updated.

Calculations for same day are correct, its only 24hr which is tricky.

For Policy: Windows C and D Drive
Name: Lhrms01-fp
For rows: 2056, 2057, 2061 - VBA is correct as two initial jobs were not successful followed by on-demand success job

For 2290, 2294 and 2297: success yes would be on  first success of the job line 2294, though its same day 2297 is good too.

For 24hrs: Notes, Lhrms01-fp, Friday, 14 November 2014 and Saturday, 15 November 2014.
Yes the last job be excluded if fail or success, and if the previous is success

Logic used is for every client, there should be a daily backup based on the policy. the jobs are all scheduled through an agent. Review is done on all failed jobs and then they are run on demand. Most of the times while these fails are reviewed the review for previous day would be ongoing till next day and within next day the schedule for that day would be running too. the failed review/on-demand re-runs of previous day is to be completed before the scheduled day job.

In excel file: For Policy: Windows C and D Drive
Name: Lhrms01-fp:
Lines:5091, 5093, 5098, the scheduled job is for 21 November with VBA they are all showing exclude as within 24hrs as  the next job is counted which is only success for 22 November, there was no successful run for 21 November.

Correct result should be the last job for 21 November would be as success 'no'

Also for example: When a job is scheduled for 10pm(based on how much data to backup) its success or failure would be determined the next day hence on-demand will be on following day plus the scheduled job will also run later for the same day .hence they both be success yes. If on-demand does not occur or multiple on-demand fails to cover for the failed job of 10pm schedule then it would be success as 'no' though the scheduled job once success would be 'yes'.


Regards
Mac
Review-Validation-Exclude-Rules.xlsx
Ejgil HedegaardCommented:
I have changed the exclude check to this.

Status for the new job must be Yes or No to perform the exclude compare.
Then loop backwards until a not DUP and not Cancelled job, with the same Name and Policy is found, the same day, or the previous day.

The found (previous) job must have status Yes or No

1: The 2 jobs are on the same day:
If previous job result is No, then exclude that job, else exclude the new job

2: The 2 jobs are on different days and new job is run within 24 hours:
If new job is Scheduled or Scheduled Backup
then
    if previous job was On-Demand with result No, then exclude that job
else
    (the new job is On-Demand)
    if previous job result is No, then exclude that job, else exclude the new job

When one matching job is found and exclude compare performed or skipped, the program go to the next new job.
Review_Validations_2014111_v4.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
macentrapAuthor Commented:
Thank you Ejgil

This worked well with one file
I will just test one more and update soon
macentrapAuthor Commented:
Thank you so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.