cancel
Showing results for 
Search instead for 
Did you mean: 

Items archival rate report

CadenL
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi

With EV12.2 is there a way to get a view on how many items are being archived per hour?

I don't have SSRS so can't use the 'Items Archival Rae Report' but thought there might perhaps be an SQL query I could run to get this?

kind regards 

2 ACCEPTED SOLUTIONS

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

You can run this against the vault store databases, just adjust the WHERE clause as needed.

SELECT "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"Size" = sum(Convert(bigint,itemsize))/1024

FROM saveset
WHERE archiveddate > dateadd("hh", -24, getdate ())
GROUP BY left (convert (varchar, archiveddate,20),14)
ORDER BY "Archived Date" DESC

View solution in original post

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all,

I use a Powershell query to get this. this means you do not need to logon to SQL :)

See below.

You need 3 files. a Batchfile, to start the PS. The PS1. TXT file with VSDB's to check.

Create folder D:\Scripts\ and D:\Scripts\Logs_MBX

Copy BATFILE.TXT as BATFILE.BAT to folder d:\scripts

Copy PSQUERY.TXT as 24hrs.ps1 to folder d:\scripts

copy DBNAME.txt as MBXJNL.txt to folder d:\scripts

Edit MBXJNL.txt to have the databasenames for your environment

Edit 24hrs.ps1 to have the SQL server in $SQLServer. in the $SQLQuery, you can change the number 25 to 48 (to get 2 days hourly), or whatever many hours you need/want.

Schedule a task to run the batchfile. Reports should end up in the Logs_MBX folder. Make sure the batch file runs as VSA, so permissions to SQL are ok.

edit. can only attach 1 file, so entered required stuff in 1 txt file.

Regards. Gertjan

View solution in original post

3 REPLIES 3

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

You can run this against the vault store databases, just adjust the WHERE clause as needed.

SELECT "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"Size" = sum(Convert(bigint,itemsize))/1024

FROM saveset
WHERE archiveddate > dateadd("hh", -24, getdate ())
GROUP BY left (convert (varchar, archiveddate,20),14)
ORDER BY "Archived Date" DESC

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all,

I use a Powershell query to get this. this means you do not need to logon to SQL :)

See below.

You need 3 files. a Batchfile, to start the PS. The PS1. TXT file with VSDB's to check.

Create folder D:\Scripts\ and D:\Scripts\Logs_MBX

Copy BATFILE.TXT as BATFILE.BAT to folder d:\scripts

Copy PSQUERY.TXT as 24hrs.ps1 to folder d:\scripts

copy DBNAME.txt as MBXJNL.txt to folder d:\scripts

Edit MBXJNL.txt to have the databasenames for your environment

Edit 24hrs.ps1 to have the SQL server in $SQLServer. in the $SQLQuery, you can change the number 25 to 48 (to get 2 days hourly), or whatever many hours you need/want.

Schedule a task to run the batchfile. Reports should end up in the Logs_MBX folder. Make sure the batch file runs as VSA, so permissions to SQL are ok.

edit. can only attach 1 file, so entered required stuff in 1 txt file.

Regards. Gertjan

CadenL
Moderator
Moderator
Partner    VIP    Accredited Certified

Many thanks both

very useful info