cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query

tpuck
Level 4
Partner Accredited

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

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

5 REPLIES 5

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 3
Partner Accredited
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
Moderator
Moderator
Partner    VIP    Accredited

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
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 6
Partner Accredited Certified

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)