Exchange Powershell Questions

Question

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?

Answer 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%