Forum Discussion

tpuck's avatar
tpuck
Level 4
10 years ago

SQL Query

Hi to all,

I need an SQL Query that shows the archive date and the recieve date of a mail in the journalarchiving.

Any Idea.

 

Best Regards

 

Thorsten

Enterprise Vault
Information Governance
Reporting
Windows Server (2003-2008)
  • TonySterling's avatar
    TonySterling
    10 years ago

    So I use these queries for rates.

    For Archived Date it would be these:

    --Hourly Rate

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

    from saveset

    where archiveddate > dateadd("dd", -7, getdate ())

    group by left(convert (varchar, archiveddate,20),14)

    order by "Archived Date" Desc

     

     

    --Daily Rate

    select "Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where archiveddate > dateadd("dd", -30, getdate ())

    group by left(convert (varchar, archiveddate,20),10)

    order by "Archived Date" Desc

     

    --Monthly Rate

    select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where archiveddate > dateadd("mm", -12, getdate ())

    group by left(convert (varchar, archiveddate,20),7)

    order by "Archived Date" Desc

     

    --Yearly Rate

    select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where archiveddate > dateadd("YY", -10, getdate ())

    group by left(convert (varchar, archiveddate,20),4)

    order by "Archived Date" Desc

     

    For Item Date it would be these:

     

    --Hourly Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),14),"Hourly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("dd", -7, getdate ())

    group by left(convert (varchar, IDDateTime,20),14)

    order by "Item Date" Desc

     

     

    --Daily Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),10),"Daily Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("dd", -30, getdate ())

    group by left(convert (varchar, IDDateTime,20),10)

    order by "Item Date" Desc

     

    --Monthly Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),7),"Monthly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("mm", -12, getdate ())

    group by left(convert (varchar, IDDateTime,20),7)

    order by "Item Date" Desc

     

    --Yearly Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),4),"Yearly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("YY", -10, getdate ())

    group by left(convert (varchar, IDDateTime,20),4)

    order by "Item Date" Desc

5 Replies

Sort By
  • TonySterling's avatar
    TonySterling
    Moderator
    10 years ago

    What exactly are you looking for?  Are you looking for counts? 

    Just running a query to show archive date and recieve date will have a result for every single item in the journal, I doubt that is exactly what you want....

  • thpuck's avatar
    thpuck
    Level 3
    10 years ago
    We've got a customer that uses EV Journaling. The Timestamp when the EMail is recieved is important for a report.
    So I've need a SQL query that gives me the number of Emails in the Archiv with a specific Recievedate.
    Thanks and best Regards

    Thorsten Puck
  • AndrewB's avatar
    AndrewB
    Moderator
    10 years ago

    if they are journaling then they have Discovery Accelerator. since you're not asking for counts, you might be better off using eDiscovery. you can search to/from date ranges, etc, and every email has the timestamp indexed by EV which would be part of the indexed email metadata.

  • TonySterling's avatar
    TonySterling
    Moderator
    10 years ago

    So I use these queries for rates.

    For Archived Date it would be these:

    --Hourly Rate

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

    from saveset

    where archiveddate > dateadd("dd", -7, getdate ())

    group by left(convert (varchar, archiveddate,20),14)

    order by "Archived Date" Desc

     

     

    --Daily Rate

    select "Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where archiveddate > dateadd("dd", -30, getdate ())

    group by left(convert (varchar, archiveddate,20),10)

    order by "Archived Date" Desc

     

    --Monthly Rate

    select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where archiveddate > dateadd("mm", -12, getdate ())

    group by left(convert (varchar, archiveddate,20),7)

    order by "Archived Date" Desc

     

    --Yearly Rate

    select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where archiveddate > dateadd("YY", -10, getdate ())

    group by left(convert (varchar, archiveddate,20),4)

    order by "Archived Date" Desc

     

    For Item Date it would be these:

     

    --Hourly Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),14),"Hourly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("dd", -7, getdate ())

    group by left(convert (varchar, IDDateTime,20),14)

    order by "Item Date" Desc

     

     

    --Daily Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),10),"Daily Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("dd", -30, getdate ())

    group by left(convert (varchar, IDDateTime,20),10)

    order by "Item Date" Desc

     

    --Monthly Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),7),"Monthly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("mm", -12, getdate ())

    group by left(convert (varchar, IDDateTime,20),7)

    order by "Item Date" Desc

     

    --Yearly Rate

    select "Item Date" = left (convert (varchar, IDDateTime,20),4),"Yearly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024

    from saveset

    where IDDateTime > dateadd("YY", -10, getdate ())

    group by left(convert (varchar, IDDateTime,20),4)

    order by "Item Date" Desc

  • Jeff_Shotton's avatar
    Jeff_Shotton
    Level 6
    10 years ago

    One of these might do what you are after

     

    --per hour by received date

    SELECT

    datepart(yy, iddatetime) as 'year'

    ,datepart(mm, iddatetime) as 'month'

    ,datepart(dd, iddatetime) as 'day'

    ,datepart(hh, iddatetime) as 'hour'

    , sum(itemsize)/(1024) as 'MiB size'

    , count(*) as 'count'

    FROM saveset

    WHERE iddatetime > (getdate() - 8)

    GROUP BY

    datepart(yy, iddatetime)

    ,datepart(mm, iddatetime)

    ,datepart(dd, iddatetime)

    ,datepart(hh, iddatetime)

    ORDER BY

    datepart(yy, iddatetime)

    ,datepart(mm, iddatetime)

    ,datepart(dd, iddatetime)

    ,datepart(hh, iddatetime)

     

    --per day by received date

    SELECT

    datepart(yy, iddatetime) as 'year'

    ,datepart(mm, iddatetime) as 'month'

    ,datepart(dd, iddatetime) as 'day'

    , sum(cast(itemsize as bigint))/1024/1024 as 'GiB size'

    , count(*) as 'count'

    FROM saveset

    WHERE iddatetime > (getdate() - 35)

    GROUP BY

    datepart(yy, iddatetime)

    ,datepart(mm, iddatetime)

    ,datepart(dd, iddatetime)

    ORDER BY

    datepart(yy, iddatetime)

    ,datepart(mm, iddatetime)

    ,datepart(dd, iddatetime)

     

    --per month by received date

    SELECT

    datepart(yy, iddatetime) as 'year'

    ,datepart(mm, iddatetime) as 'month'

    , sum(cast(itemsize as bigint))/1024/1024 as 'GiB size'

    , count(*) as 'count'

    FROM saveset

    WHERE iddatetime > (getdate() - 400)

    GROUP BY

    datepart(yy, iddatetime)

    ,datepart(mm, iddatetime)

    ORDER BY

    datepart(yy, iddatetime)

    ,datepart(mm, iddatetime)