EV 8 to 9 report changed

[MikeM-2468]

On EV 8, I was running a report daily that would send an email showing the items archived per hour.  In EV 9, I can't get this same report.  I can get it to run once, but if it runs again, it runs based on the first time it ran so the report shows the same report every time.

Has anyone seen this?

[MikeM-2468]

.... And please don't lock this as a duplicate, because the solution provided in the other case doesn't seem like the only solution since this functionality was available in the previous version.

[JesusWept3]

what is the other thread you are referring to just as a matter of interest?
And also can you explain a little more about the issue?
Do you mean that you just hit refresh and it shows you the same set of numbers, regardless of how many hours later its been since you last loaded the report?

[MikeM-2468]

This is the previous solution:

https://www-secure.symantec.com/connect/forums/reporting-archival-report

I've setup a subscription to an Operational Report that should be sending an hourly summary of archived items.  In EV 8, I'd get the report at 12:00AM and it would list the items archived for the last 24 hours.  When I try to run the same report in EV 9, it sends the report every day, but it sends the data from the first day it was run.

[JesusWept3]

In that case open a support case and see if you can get it logged as a bug, but if anything I think they'll log it as an enhancement request

[MikeM-2468]

We already had a case open, but were getting nowhere.  I figured I'd see if there were any real-world attempts at solving the problem.  I was surprised to see that it was not available in the newer version.

[JesusWept3]

I think there were some unintended consequences with optimizing reporting now that it uses temp tables and such, rather than trawling the saveset tables and what not Unfortunately due to the way the reports are compiled you cant change it at all, so for me if I had to do it I'd just write my own reports, either through a SQL package or via the report builder in the links I posted earlier

[MikeM-2468]

How can I find what tables have the info I need to reproduce the report. 

I find it very odd that you can run the report on demand, but it can't be scheduled.

[TonySterling]

here are some examples:

-- This one gives a hourly rate:
--Runs against the VaultStore Database
select "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate > dateadd("hh", -24, getdate ())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc

-- This one gives a daily rate:
--Runs against the VaultStore Database
select "Archived Date" = left (convert (varchar, archiveddate,20),10),
"Daily Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate > dateadd("hh", -24, getdate ())
group by left (convert (varchar, archiveddate,20),10)
order by "Archived Date" desc

[MikeM-2468]

This helps.  Thanks. But I'm a bit confused by the results I'm seeing.  It yields more than 24 results and they appear to be dated in the future.  The following query was run at about 07:30 local time. 

select "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Items Archived" = count (*)
from EVJournal.dbo.saveset
where archiveddate > dateadd(hh, -24, getdate ())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date"

And this is the result.

2011-10-26 07:    181
2011-10-26 08:    293
2011-10-26 09:    646
2011-10-26 10:    1148
2011-10-26 11:    4362
2011-10-26 12:    2755
2011-10-26 13:    2259
2011-10-26 14:    1656
2011-10-26 15:    1213
2011-10-26 16:    1183
2011-10-26 17:    1208
2011-10-26 18:    1179
2011-10-26 19:    1091
2011-10-26 20:    2060
2011-10-26 21:    1155
2011-10-26 22:    704
2011-10-26 23:    793
2011-10-27 00:    964
2011-10-27 01:    530
2011-10-27 02:    496
2011-10-27 03:    342
2011-10-27 04:    830
2011-10-27 05:    578
2011-10-27 06:    670
2011-10-27 07:    636
2011-10-27 08:    408
2011-10-27 09:    702
2011-10-27 10:    1322
2011-10-27 11:    4040

My reading of this is that it's showing a number for about 4 hours in the future.  Are these times GMT?  This is odd because it reaches back 24 hours local (EDT) but shows 4 hours future GMT.  If so, how would I convert it to local time and limit the results to the last 24 hours?  If I run it at 12AM, I'd like to see from 12AM yesterday to 11PM yesterday.

[MikeM-2468 · Accepted solution]

I found a way to get what I needed.  It took some back and forth conversions, but it seems to work. 

select "Archived Date" = dateadd(hour,(DateDiff(Hour, GetUTCDate(), GetDate())),convert(datetime,(left (convert (varchar, archiveddate,20),14) + "00"),20)),
"Items Archived" = count (*)
from EVJournal.dbo.saveset
where archiveddate > DateAdd(hour, -24, GetUTCDate())
and archiveddate < DateAdd(hour, -1, GetUTCDate())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date"