cancel
Showing results for 
Search instead for 
Did you mean: 

Daily report on item archiving rate.

FreKac2
Level 6
Partner Accredited Certified

Hi all,

Is it possible to get a daily report on item archiving rate through reporting services ?

I've created a subscription but in the settings of the subscription you have to select a start/end date for the report.

However these dates are not incremented by one each day, it's a fixed range.

So is there a way to solve this through RS or do I need to e.g. script a sql query ?

Which is kind of a pain if you want to do this through e-mail as well :)

 

Cheers

Fredrik

1 ACCEPTED SOLUTION

Accepted Solutions

FreKac2
Level 6
Partner Accredited Certified

Have been messing around with this today and have (I think) managed to get the update query to be in the correct format.

I guess it's a simple thing to create a stored procedure or using SQLCMD to update the parameters column.

USE ReportServer
GO
Update dbo.Subscriptions SET Parameters = '<ParameterValues><ParameterValue><Name>StartMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>StartYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>EndMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>EndYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>Granularity</Name><Value>1</Value></ParameterValue><ParameterValue><Name>EndDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())+1) ELSE CONVERT(VARCHAR(2),day(GETDATE())+1) END+'</Value></ParameterValue><ParameterValue><Name>StartDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())) ELSE CONVERT(VARCHAR(2),day(GETDATE())) END+'</Value></ParameterValue></ParameterValues>' WHERE SubscriptionID = '67b5bd91-aea9-4669-a839-11f6bdaabce0'

View solution in original post

4 REPLIES 4

Maverik
Level 6

I dont think by default this kinda of report exists as you probably know.

 

the only ones I know si the SQL queries as per below.

 

Use the following SQL query to show how much has been archived in a certain

period as well as time the first and last items were stored during that period.

Replace the dates and times in this example with those you want to use.

Select count(*),min(archiveddate),max(archiveddate) from saveset

where archiveddate >'2002-04-26 18:00' and archiveddate <

'2002-04-27 5:00'

Use the following SQL Query to obtain the number of different vaults processed

in a specified period.

Select distinct(vaultidentity) from saveset where

archiveddate >'2002-04-26 18:00' and

archiveddate < '2002-04-27 5:00'

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

I thought there was a way for it to send you the previous 24 hours but I could be wrong.

If you want to just set up a sql query you can use these, if you run these you do not have to put in a date range, it will give you rates for the past 24 hours:

-- 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

FreKac2
Level 6
Partner Accredited Certified

Thanks guys but I know about the queries, what I need is to be able to automaticly run a report or query every day and send the results to a mailbox.

Kind of what RS are doing except that it doesn't work in regard to just showing the last 24 hours and getting the parameters incremented every day.

I had a look at the subscription stored in Reporting Services and I'm checking now if I could create a stored procedure/job to update the parameter column every day.

So that is what I'm looking at for the time being.

If anyone have any better ideas in regard to do this in a better way then by all means :)

FreKac2
Level 6
Partner Accredited Certified

Have been messing around with this today and have (I think) managed to get the update query to be in the correct format.

I guess it's a simple thing to create a stored procedure or using SQLCMD to update the parameters column.

USE ReportServer
GO
Update dbo.Subscriptions SET Parameters = '<ParameterValues><ParameterValue><Name>StartMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>StartYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>EndMonth</Name><Value>'+CASE WHEN month(GETDate()) < 10 THEN CONVERT(VARCHAR(1),month(GETDate())) ELSE CONVERT(VARCHAR(2),month(GETDate())) END+'</Value></ParameterValue><ParameterValue><Name>EndYear</Name><Value>'+CONVERT(VARCHAR(4),year(GETDATE()))+'</Value></ParameterValue><ParameterValue><Name>Granularity</Name><Value>1</Value></ParameterValue><ParameterValue><Name>EndDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())+1) ELSE CONVERT(VARCHAR(2),day(GETDATE())+1) END+'</Value></ParameterValue><ParameterValue><Name>StartDay</Name><Value>'+CASE WHEN day(GETDate()) < 10 THEN CONVERT(VARCHAR(1),day(GETDATE())) ELSE CONVERT(VARCHAR(2),day(GETDATE())) END+'</Value></ParameterValue></ParameterValues>' WHERE SubscriptionID = '67b5bd91-aea9-4669-a839-11f6bdaabce0'