+ Start a Discussion
Kiru535Kiru535 

Apex jobs completed and failed during the day

Hi Friends,

My requirement was I need to get completed and failed jobs during the day needs to send notification to support via email daily night @11:00PM. I have created batch apex class but I am not able to get the records via email. Please help me out...
Error:
First error: List has more than 1 row for assignment to SObject

This is my code...

global class MPE_BatchJobStatus implements Database.Batchable<sObject>,Schedulable
{
//String query;
global Database.QueryLocator start(Database.BatchableContext BC) {
    String query = 'SELECT ApexClassId,CompletedDate,Id,JobItemsProcessed,JobType,MethodName,NumberOfErrors,Status,TotalJobItems,ExtendedStatus FROM AsyncApexJob where CompletedDate =Today'; 
    
    return Database.getQueryLocator(query);
  }
   global void execute(SchedulableContext SC)
   {
     MPE_BatchJobStatus ds=new MPE_BatchJobStatus();
      database.executebatch(ds);
   }
  global void Execute(Database.BatchableContext bc,LIST<SObject> scope)
   {
     
     }
global void finish(Database.BatchableContext BC){
   //List<AsyncApexJob> a = new List<AsyncApexJob>();
   AsyncApexJob a = [SELECT ApexClassId,CompletedDate,Id,JobItemsProcessed,JobType,MethodName,NumberOfErrors,Status,TotalJobItems,ExtendedStatus FROM AsyncApexJob where CompletedDate =Today];
   Messaging.EmailFileAttachment attach = new Messaging.EmailFileAttachment();
   attach.setFileName('BatchJobs.xls');
    // blob csvBlob = Blob.valueOf(a);
     String body;
     body = '<html><body><table><tr><td>ID</td><td>' + a.Id + '</td></tr><tr><td>ApexClassId</td><td>' + a.ApexClassId + '</td></tr><tr><td>CompletedDate</td><td>' + a.CompletedDate + '</td></tr><tr><td>JobsProcessed</td><td>' + a.JobItemsProcessed + '</td></tr><tr><td>JobType</td><td>' + a.JobType + '</td></tr><tr><td>NumberOfErrors</td><td>' + a.NumberOfErrors + '</td></tr><tr><td>Error</td><td>' + a.ExtendedStatus + '</td></tr><tr><td>TotalJobItems</td><td>' + a.TotalJobItems + '</td></tr><tr><td>Status</td><td>' + a.Status + '</td></tr></table></body></html>';
     attach.body = Blob.valueOf(body);
// Send an email to the Apex job's submitter notifying of job completion.
List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
   Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
   String[] toAddresses = new String[] {'vrm674@motorolasolutions.com'};// we are getting email from custom label
   mail.setToAddresses(toAddresses);
   mail.setSubject('ApexJobs');
   mail.setHtmlBody('PFA');
   mail.setFileAttachments(new Messaging.EmailFileAttachment[] { attach });
   mails.add(mail);
   if(!mails.isEmpty())
{
    Messaging.SendEmail(mails);
}
}

}

Regards,
kk.
Jigar.LakhaniJigar.Lakhani
Hi,

I think you can do it using schedule class also, just need to create a schedule class with you code and setup the schedule for everyday at 11:00 PM.

For your batch class error, please try to replace your finish method with below code.
 
global void finish(Database.BatchableContext BC){
   
	List<AsyncApexJob> listAsyncJobs = new List<AsyncApexJob>([SELECT ApexClassId,CompletedDate,Id,JobItemsProcessed,JobType,MethodName,NumberOfErrors,
																Status,TotalJobItems,ExtendedStatus FROM AsyncApexJob WHERE CompletedDate =Today]);
	String strBody = '';
	if (listAsyncJobs != null && listAsyncJobs.size() > 0) {
		strBody = '<html><body><table>';
		for (AsyncApexJob objAsyncJob:listAsyncJobs) {
			strBody += '<tr><td>ID</td><td>' + objAsyncJob.Id + '</td></tr><tr><td>ApexClassId</td><td>' + objAsyncJob.ApexClassId + '</td></tr><tr><td>CompletedDate</td><td>' + objAsyncJob.CompletedDate + '</td></tr><tr><td>JobsProcessed</td><td>' + objAsyncJob.JobItemsProcessed + '</td></tr><tr><td>JobType</td><td>' + objAsyncJob.JobType + '</td></tr><tr><td>NumberOfErrors</td><td>' + objAsyncJob.NumberOfErrors + '</td></tr><tr><td>Error</td><td>' + a.ExtendedStatus + '</td></tr><tr><td>TotalJobItems</td><td>' + objAsyncJob.TotalJobItems + '</td></tr><tr><td>Status</td><td>' + objAsyncJob.Status + '</td></tr>';
		}
		strBody += '</table></body></html>';
	}
	Messaging.EmailFileAttachment attach = new Messaging.EmailFileAttachment();
	attach.setFileName('BatchJobs.xls');
	attach.body = Blob.valueOf(strBody);
	
	// Send an email to the Apex job's submitter notifying of job completion.
	List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
	Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
	String[] toAddresses = new String[] {'vrm674@motorolasolutions.com'};// we are getting email from custom label
	mail.setToAddresses(toAddresses);
	mail.setSubject('ApexJobs');
	mail.setHtmlBody('PFA');
	mail.setFileAttachments(new Messaging.EmailFileAttachment[] { attach });
	mails.add(mail);
	if(!mails.isEmpty()) {	
		Messaging.SendEmail(mails);
	}
}

Thanks and Cheers,
Jigar
Kiru535Kiru535
Hi Mr.Salesforce,

Thanks for your reply...the code is working fine. Issue is as per functionality I need listout to send an email today's batch jobs which are got completed and failed. But I am getting in the CSV file both today's and yesterday's ones. I want only today's batch jobs.

For Ex: I f I ran the job today i.e 11/25/2014 I am getting 11/24/2014 records also...

Please suggest the solution.

global class MPE_BatchJobStatus implements Database.Batchable<sObject>,Schedulable
{

global Database.QueryLocator start(Database.BatchableContext BC) {
    String st = 'Queued';
    String query = 'SELECT ApexClassId,CompletedDate,Id,JobItemsProcessed,JobType,MethodName,NumberOfErrors,Status,TotalJobItems,ExtendedStatus FROM AsyncApexJob where Status !=:st AND CompletedDate =Today'; 
    
    return Database.getQueryLocator(query);
  }
   global void execute(SchedulableContext SC)
   {
     MPE_BatchJobStatus ds=new MPE_BatchJobStatus();
      database.executebatch(ds);
   }
  global void Execute(Database.BatchableContext bc,LIST<SObject> scope)
   {
     
     }
global void finish(Database.BatchableContext BC){
   
    
    List<AsyncApexJob> listAsyncJobs = new List<AsyncApexJob>([SELECT ApexClassId,CompletedDate,Id,JobItemsProcessed,JobType,MethodName,NumberOfErrors,
    
                                                             Status,TotalJobItems,ExtendedStatus FROM AsyncApexJob WHERE CompletedDate =Today AND Status!='Queued']);
                                                    
    String strBody = '';
    if (listAsyncJobs != null && listAsyncJobs.size() > 0) {
        strBody = '<html><body><table>';
        strBody += '<tr><td>ID</td>' + '<td>ApexClassId</td>'  + '<td>CompletedDate</td>>'+ '<td>JobsProcessed</td>' + '<td>JobType</td>' + '<td>NumberOfErrors</td>' + '<td>Error</td>'  + '<td>TotalJobItems</td>'+ '<td>Status</td>' + '</tr>';
        for (AsyncApexJob objAsyncJob:listAsyncJobs) {
            strBody += '<tr><td>' + objAsyncJob.Id + '</td><td>' + objAsyncJob.ApexClassId + '</td><td>' + objAsyncJob.CompletedDate + '</td><td>' + objAsyncJob.JobItemsProcessed + '</td><td>' + objAsyncJob.JobType + '</td><td>' + objAsyncJob.NumberOfErrors + '</td><td>' + objAsyncJob.ExtendedStatus + '</td><td>' + objAsyncJob.TotalJobItems + '</td><td>' + objAsyncJob.Status + '</td></tr>';
        }
        strBody += '</table></body></html>';
    }
    Messaging.EmailFileAttachment attach = new Messaging.EmailFileAttachment();
    attach.setFileName('BatchJobs.xls');
    attach.body = Blob.valueOf(strBody);
    
    // Send an email to the Apex job's submitter notifying of job completion.
    List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
    String[] toAddresses = new String[] {'vrm674@motorolasolutions.com'};// we are getting email from custom label
    mail.setToAddresses(toAddresses);
    mail.setSubject('ApexJobs');
    mail.setHtmlBody('PFA');
    mail.setFileAttachments(new Messaging.EmailFileAttachment[] { attach });
    mails.add(mail);
    if(!mails.isEmpty()) {  
        Messaging.SendEmail(mails);
    }
}

}
Regards,
kk.