0

Goal: Find all mailboxes which have not sent (or received possibly?) mail in the past 90 days.

I've seen a few guides on powershell commands but I'm not positive I have the right order (Get-Mailbox | etc) so I don't want to risk executing it?

  • So what exactly is your question? What have you tried? You really should try to reduce the scope of this question. – Ramhound Feb 6 '15 at 19:56
  • @Abraxas Good question. Exchange Server Version? – STTR Feb 6 '15 at 20:03
  • @STTR Thanks. We're on Exchange 2010 in an AD environment on Server 2012 R2. Let me know if you need anything else. Ramhound - I haven't tried anything because I am not confident in my understanding of PowerShell yet. My question is what is a way to use PS to get a listing of mailboxes which have not sent and/or received email in the past 90 days (possibly export to a csv?) – Abraxas Feb 6 '15 at 20:08
1
add-pssnapin *exchange* -EA 0
Get-Mailbox -ResultSize Unlimited |Get-MailboxStatistics |?{$_.Lastlogontime -lt (Get-Date).AddDays(-90)}|Select DisplayName, LastLoggedOnUserAccount, LastLogonTime |Export-csv .\NotAccess90.csv -noType

Way 2: Log Exchange to SQL Server Express edition.

This is a way to create quick reporting. Only need to create indexes fields that will be searched. Then the query processing speed will be about 1-3 M records per second. That is the speed of file creation - will not be as fast. Ideal job done through the driver csv. In general, the improvement infinitely).

But, stop log (SQL Server Express) - Exchange Server stop too!

Need sql function:

ConvertCharIP - convert string IP address to BIGINT

CREATE FUNCTION [dbo].[ConvertCharIP] (@IP AS VARCHAR(20)) 
RETURNS BIGINT 
AS 
BEGIN 
RETURN (CONVERT(BIGINT, PARSENAME(@IP,1)) + 
         CONVERT(BIGINT, PARSENAME(@IP,2)) * 256 + 
         CONVERT(BIGINT, PARSENAME(@IP,3)) * 65536 + 
         CONVERT(BIGINT, PARSENAME(@IP,4)) * 16777216)    
END 

ConvertLongIP - convert BIGINT to string IP address.

CREATE FUNCTION [dbo].[ConvertLongIP] (@LongIP bigint)

RETURNS varchar(15) AS BEGIN 
DECLARE @DotIP varchar(15), @bin varbinary(4) 
select @bin = cast(@LongIP as varbinary(4)) 

select @DotIP = cast(convert(int,substring(@bin,1,1)) as varchar(3)) 
+ '.' + cast(convert(int,substring(@bin,2,1)) as varchar(3)) 
+ '.' + cast(convert(int,substring(@bin,3,1)) as varchar(3)) 
+ '.' + cast(convert(int,substring(@bin,4,1)) as varchar(3)) 

RETURN @DotIP END

getCurrentTime - return now time.

CREATE FUNCTION [dbo].[getCurrentTime]() 
RETURNS DATETIME 
AS  
BEGIN  
    RETURN (SELECT CurrentTime FROM CurrentDateTime)  
END  

Use pattern:

MailAddress.sql:

SELECT 
   SUM(bytesrecvd)/1024 AS [Upload, Kb],
   SUM(bytessent)/1024 AS [Download, Kb],
   ...
   <other log column name>,
   master.[dbo].[ConvertLongIP]([SourceIP]) AS [SourceIP],
   master.[dbo].[ConvertLongIP]([DestinationIP]) AS [DestinationIP]

 FROM [<Table name>]

WHERE
<other bound>
AND master.[dbo].[ConvertLongIP]([SourceIP])=@DotIP

GROUP BY [SourceIP],[DestinationIP]

SourcePort
,DestinationPort

ORDER BY [Upload, Kb] DESC

QueryMail.cmd:

@echo off
SET SQLSERVER=.\SQL2K5
SET DBName=Exch2010
SET QueryFile=MailAddress.sql
SET CSVLog=C:\MailLogCSV

@echo %date% %time%
sqlcmd -S %SQLSERVER% -d %DBName% -E -h-1 -i %QueryFile% -o %CSVLog%\MailAddress-%DBName%.csv -s ";" -W
@echo %date% %time%
  • 1
    @Abraxas If you need to more accurately handle logs mail, do not rely on the Exchange service's tracking.tomsitpro.com/articles/… – STTR Feb 6 '15 at 21:59
  • 1
    @Abraxas Update. – STTR Feb 6 '15 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.