I'm currently writing a report to assess the last backup time of full backups. What I want to do is to report any savesets/clients that have not had a successful backup in the last week/month.
I thought the the Time To Duration operator would allow me to assess the number of days since the last full backup which I could then use to filter the results - this is meant to calculate the time difference between the value of the field and the end of the window.
I use the Queue Start field (renamed to Last Full Backup) for the Time Field; I have set the Duration Field to Duration (which I understand is just the name of the output field). I have tried changing the cast of Last Full Backup from date to anything which is time related with no success. Each time I try to save the report it gives the error:
Time To Duration: please specify a valid time or integer field
I've verified that the report returns a date/time that appears to be valid (for example 17/12/10 21:59) so I'm not too sure why this operator has a problem.
There is a standard report named "Backup Last Successful Full Run of Client" that you can use as a base/template - look at the Properties of the datasource to see how the last full backup is obtained.
If you want to further calculate the elapsed time since that backup, you can use the "Report Times" datasource (under Internal category) to add in the "End Time" of the reporting window and then use a mathematical subtract to subtract the "Report End Time" from the "End Time" of the job and display the field as a Duration (all times are stored in seconds in the DPA database ie. julian time , number of seconds since 1/1/1970)
Thanks for that Elvin, I will take a look at it tomorrow.
I have a report which produces a list of last backup times so that is not the issue. What I want to do is take this information and filter out any results that occur before the start time selected; I have achieved this by having the original Backup Saveset Details run against a start time of Epoch, and then having a Join against Backup Client Config run against a start time of Start Of Window then having a condition of Queue Start is less than Start Time. It works but it take a long time to run and it fails if ran as a scheduled report or against more than one backup server.
I've not yet tried your recommendation but the report you recommended is doing fundamentally the same thing my report is doing except I am trying to run on a whole server rather than run for an individual client which is why the report takes so long to test! This report comes with a fundamental problem in that to successfully produce accurate data it needs to be ran from epoch which means a huge amount of data is returned.
Hi Elvin, I tested adding in the times, subtracting the fields and then adding a condition on the results (I had to convert this to total seconds, I couldn't add a condition of "less than 1 day" or something similar) - all worked okay. Thanks for your help again.