Forum Discussion

CadenL's avatar
CadenL
Level 6
6 years ago

Items archival rate report

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 

  • TonySterling's avatar
    TonySterling
    6 years ago

    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's avatar
    GertjanA
    6 years ago

    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.

  • TonySterling's avatar
    TonySterling
    Moderator
    6 years ago

    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's avatar
      GertjanA
      Moderator
      6 years ago

      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.