$days = 60 $dbServer = "SQLBES-SRV" $db = "BESMgmt" #If you use SA account to connect database, change pwd=XXXXXX!, or use Windows Authentication, see below $connString = "Server=$dbServer;Database=$db;uid=sa;pwd=XXXXXX" #If you use Windows Authentication to connect BESMGMT SQL Database, need setup rights on the database #$connString = "Server=$dbServer;Database=$db;Integrated Security=True" $date = get-date -format s $csvoutput = ('COUNTMOBILE_' + $date + '.csv').Replace(":","_") 'Mailbox;Numberofdevices;DeviceType;Model;OSversion;LastContactTime' | Out-file $csvoutput -append $BESsmtpArray = @() #SQL Query to retrieve BB Info (only devices who recieved mail in the last $days) $Query = "Select UserConfig.DisplayName,MailboxSMTPAddr,DeviceType,ModelName,LastFwdTime,AppsVer from UserConfig,SyncDeviceMgmtSummary,UserStats where UserConfig.ID=SyncDeviceMgmtSummary.UserConfigID AND UserConfig.ID=UserStats.UserConfigID AND DeviceType <> 0 AND ModelName <> '' AND DateDiff(dd,LastFwdTime,GETDATE()) < "+$days #Connect to Database, run Query, Disconnect $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = $connString $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $bbs = $DataSet.Tables[0] foreach ($bb in $bbs) { #Array of mailboxs already processed $BESsmtpArray = $BESsmtpArray + $bb.MailboxSMTPAddr #Check if users has ActiveSyncDevice in addition to BB (used during the past $days) $acts = Get-ActiveSyncDeviceStatistics -Mailbox $bb.MailboxSMTPAddr | ?{$_.LastSuccessSync -gt (Get-Date).AddDays(-$days)} #If not user has one mobile device (one BB) if (($acts | Measure-object).count -eq 0) {$bb.MailboxSMTPAddr + ';1;BlackBerry;' + $bb.ModelName + ';' + $bb.AppsVer + ';' + $bb.LastFwdTime | Out-file $csvoutput -append} #If he has an ActiveSync Device + BB info is retrieved else {$ndevices = ($acts | Measure-object).count + 1 $bb.MailboxSMTPAddr + ';' + $ndevices + ';BlackBerry;' + $bb.ModelName + ';' + $bb.AppsVer + ';' + $bb.LastFwdTime | Out-file $csvoutput -append foreach($act in $acts){ $bb.MailboxSMTPAddr + ';'+ $ndevices + ';' + $act.DeviceType + ';' + $act.DeviceModel + ';' + $act.DeviceUserAgent + ';' + $act.LastSuccessSync | Out-file $csvoutput -append} } } #Get all users mailboxes $Mailboxes = Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited foreach ($mailbox in $Mailboxes){ $primarysmtp = $mailbox.PrimarySmtpAddress.local + '@' + $mailbox.PrimarySmtpAddress.domain #BB Mailboxes already processed are excluded if ($BESsmtpArray -notcontains $primarysmtp) {$acts = Get-ActiveSyncDeviceStatistics -Mailbox $primarysmtp | ?{$_.LastSuccessSync -gt (Get-Date).AddDays(-$days)} $ndevices = ($acts | Measure-object).count if ($ndevices -ne 0){ foreach($act in $acts){ $primarysmtp + ';'+ $ndevices + ';' + $act.DeviceType + ';' + $act.DeviceModel + ';' + $act.DeviceUserAgent + ';' + $act.LastSuccessSync | Out-file $csvoutput -append} } } }