Failed Notifications - Query Info

[Kathy_Rickertse]

We have a number of failed notifications.  I can run the report in the unity scheduler to see the recipients and the subject but I'd like to export the results or query the same info so I can get it in Excel.  When you export the results from the report it is a picture.  Does someone have a query already to do this or can someone provide the tables I need to look at?  

thanks!

[aboucher · Accepted solution]

Hi @Kathy Rickertsen,

You will find many of the OnBase tables defined here: Database Reporting Guide - OnBase Foundation EP5 Module Reference Guide.

Perhaps you will find failed notification info under LOG TABLE ACTIONS starting on page 122.

I hope this helps.

Cheers!

~Alan 

[aboucher · Accepted solution]

Hi @Kathy Rickertsen,

You will find many of the OnBase tables defined here: Database Reporting Guide - OnBase Foundation EP5 Module Reference Guide.

Perhaps you will find failed notification info under LOG TABLE ACTIONS starting on page 122.

I hope this helps.

Cheers!

~Alan 

[Kathy_Rickertse]

Thanks @Alan Boucher, 

I did try the Database Reporting Guide as it has helped me in the past but I didn't find anything that seemed like the right fit. I did give it a second look after reading your suggestion and found Scheduled Processes as a status of 40 so maybe I will check that out.  I went old school and just typed out a list based on the forms in the queues.  It wasn't such a long list when I did that as we had multiple failure emails per form and some had been processed manually without the email link.  

Thanks again!

[aboucher]

I'm glad I was able to help Kathy.

Cheers!

~Alan

[Larissa_Armand · Accepted solution]

@Kathy Rickertsen 

This should get you close to what you are looking for: 

select DH.DISTREQUESTNUM,       decode(QR.STATUS,          '0','Initial',          '1','Succeeded',          '2','Failed') as ResultStatus,       QR.SEQUENCEID as SequenceID,       DH.COMPLETIONDATE as SentDate,       rtrim(U.REALNAME) as EmailSender,       rtrim(QR.EMAILUSERPART) as SentToName,       rtrim(QR.DOMAIN) as SentToDomain,       rtrim(Q.DISTSUBJECT) as EmailSubject  from DISTRIBUTIONHIST DH,       USERACCOUNT U,       EMAILQUEUERECIP QR,       EMAILQUEUE Q  where QR.STATUS = '2'  and DH.USERNUM = U.USERNUM  and DH.DISTREQUESTNUM = Q.DISTREQUESTNUM  and DH.DISTREQUESTNUM = QR.DISTREQUESTNUM  order by DH.DISTREQUESTNUM DESC, QR.SEQUENCEID;