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?
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?
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).
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%