cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Solution

Accepted Solutions
Accepted Solution!

So I use these queries for

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

https://www.linkedin.com/in/awsterling/

View solution in original post

5 Replies

What exactly are you looking

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

https://www.linkedin.com/in/awsterling/

Re: [EVTC] SQL Query

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

if they are journaling then

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.

Accepted Solution!

So I use these queries for

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

https://www.linkedin.com/in/awsterling/

View solution in original post

RE: [EVTC] SQL Query

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)