05-21-2015 02:32 AM
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
Solved! Go to Solution.
05-21-2015 09:45 AM
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
05-21-2015 05:26 AM
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....
05-21-2015 07:15 AM
05-21-2015 08:10 AM
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.
05-21-2015 09:45 AM
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
05-21-2015 10:51 AM
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)