Hey everyone,
I was wondering if anybody on these forums could help me with the below task?
I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).
At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:
<tbody>
</tbody>
What I’d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.
Example:
- Monday 1st Oct, 10am – email comes into inbox with subject/body containing “Job_xxx has started “ >>>> update Excel sheet to record RUNNING under column ‘Monday’ in row titled 'job_xxx'
- Monday 1st Oct, 1pm – email comes into inbox with subject/body containing “job_xxx completed successfully” >>>> update Excel sheet to record SUCCESS, still under column ‘Monday’, row titled 'job_xxx'
- As soon as it turns 00:00 Tuesday, whatever emails come into the inbox relating to the specific job, the status is recorded in the relevant cell under the column ‘Tuesday’.
The job names do NOT change – i.e. I will always have 100 jobs with the same name running each and every day.
The emails I receive contain the following:
- Subject > contains the job name (job_xxx, job_yyy, job_zzz)
- Body > contains the job status (text within the body contains values such as “the job completed successfully” or “the job failed”)
- Body > contains job details – i.e. if a job fails it gives an error code. I’d like to include these within the cell when the job fails – e.g. “Failed with error code 1234”.
Also, do the emails need to be post-processed (i.e. deleted or moved to a different folder) once the script has run in order to differentiate between emails containing a new job status and older emails? (i.e. it won’t run the email at 10am saying ‘job started’ again if a more recent email with the same job name has come in with ‘job completed successfully’).
Now if this script was to run every day of the year, it’d run up a lot of columns within the sheet, so is there a way to archive columns? For example, each month the populated columns for the month (from Oct 1st to Oct 31st) can be moved to another sheet with name “backupsjobs_october_2012″.
I’d really appreciate any help I can get with this. If you need any more info please let me know.
Thanks ever so much for your help!
Joe
I was wondering if anybody on these forums could help me with the below task?
I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).
At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:
| Job name | Monday 1st Oct | Tuesday 2nd Oct | Wednesday 3rd Oct | Thursday 4th Oct |
| Job_xxx | SUCCESS | SUCCESS | FAILED – ERROR CODE 1234 | FAILED – ERROR CODE 5678 |
| Job_yyy | SUCCESS | SUCCESS | SUCCESS | RUNNING |
| Job_zzz | MISSED | MISSED | CANCELLED | SUCCESS |
<tbody>
</tbody>
What I’d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.
Example:
- Monday 1st Oct, 10am – email comes into inbox with subject/body containing “Job_xxx has started “ >>>> update Excel sheet to record RUNNING under column ‘Monday’ in row titled 'job_xxx'
- Monday 1st Oct, 1pm – email comes into inbox with subject/body containing “job_xxx completed successfully” >>>> update Excel sheet to record SUCCESS, still under column ‘Monday’, row titled 'job_xxx'
- As soon as it turns 00:00 Tuesday, whatever emails come into the inbox relating to the specific job, the status is recorded in the relevant cell under the column ‘Tuesday’.
The job names do NOT change – i.e. I will always have 100 jobs with the same name running each and every day.
The emails I receive contain the following:
- Subject > contains the job name (job_xxx, job_yyy, job_zzz)
- Body > contains the job status (text within the body contains values such as “the job completed successfully” or “the job failed”)
- Body > contains job details – i.e. if a job fails it gives an error code. I’d like to include these within the cell when the job fails – e.g. “Failed with error code 1234”.
Also, do the emails need to be post-processed (i.e. deleted or moved to a different folder) once the script has run in order to differentiate between emails containing a new job status and older emails? (i.e. it won’t run the email at 10am saying ‘job started’ again if a more recent email with the same job name has come in with ‘job completed successfully’).
Now if this script was to run every day of the year, it’d run up a lot of columns within the sheet, so is there a way to archive columns? For example, each month the populated columns for the month (from Oct 1st to Oct 31st) can be moved to another sheet with name “backupsjobs_october_2012″.
I’d really appreciate any help I can get with this. If you need any more info please let me know.
Thanks ever so much for your help!
Joe
