Category: Powershell

Scenario:  You want a script that will perform the following:

1. Provide all device statistics in csv format attached to the email report.
2. Provide a total of Devices in the email report
3. Provide a total of mailboxes that have a device in the email report
4. The average number of devices per user in the email report
5. Provide a list of devices that have not connected in over 6 months in the email report
6. Provide a count for each ActiveSync policy applied on all mailboxes in the email report
7. Provide a list of the top 10 device types in the email report
8. Provide a list of the top 10 users based on device count in the email report

Script: 

#Define Variables
    $age = 180
    $final_DeviceCount = @()
    $report = @()
    $file = $(((get-date).ToUniversalTime()).ToString("yyyyMMddThhmmssZ"))
    $filename = "C:tempeas_stat_"+$file+".csv"
    $today = get-date
    $final = @()
    $BreakLine = "
    "
 $stats = @("FirstSyncTime",
        "LastPolicyUpdateTime",
        "LastSyncAttemptTime",
        "LastSuccessSync",
        "DeviceType",
        "DeviceID",
        "DeviceUserAgent",
        "DeviceWipeSentTime",
        "DeviceWipeRequestTime",
        "DeviceWipeAckTime",
        "LastPingHeartbeat",
        "RecoveryPassword",
        "DeviceModel",
        "DeviceImei",
        "DeviceFriendlyName",
        "DeviceOS",
        "DeviceOSLanguage",
        "DevicePhoneNumber",
        "MailboxLogReport",
        "DeviceEnableOutboundSMS",
        "DeviceMobileOperator",
        "Identity",
        "Guid",
        "IsRemoteWipeSupported",
        "Status",
        "StatusNote",
        "DeviceAccessState",
        "DeviceAccessStateReason",
        "DeviceAccessControlRule",
        "DevicePolicyApplied",
        "DevicePolicyApplicationStatus",
        "LastDeviceWipeRequestor",
        "ClientVersion",
        "NumberOfFoldersSynced",
        "SyncStateUpgradeTime",
        "ClientType",
        "IsValid",
        "ObjectState"
          )

#Define Email Variables:
    $smtp = "smtp.domain.com"
    [string[]]$to = "steve@domain.com"
    $from = "MobileDeviceStats@domain.com
    $subject = "Mobile Device Stats using ActiveSync"

#Define HTTP Style
$b = "<style>
        BODY{
        font-family: Arial; font-size: 10pt;
        }
        TABLE{
        border: 1px solid black; border-collapse: collapse;
        }
        TH{
        border: 1px solid black; background: #dddddd; padding: 5px;
        }
        TD{
        border: 1px solid black; padding: 5px;
        }
    </style>"
#=========Begin Script===========
#Calculate Mailboxes with EAS Device Partnership
$MailboxesWithEASDevices = @(Get-CASMailbox -Resultsize Unlimited -erroraction SilentlyContinue | Where {$_.HasActiveSyncDevicePartnership} | Select Identity,SAMAccountName)
$MailboxesWithEASDevices = $MailboxeswithEasDevices | Sort SAMAccountName
#Loop Through Each Mailbox
Foreach ($Mailbox in $MailboxesWithEASDevices)
{
$EASDeviceStats = @(Get-MobileDeviceStatistics -Mailbox $Mailbox.Identity)
Write-Host "$($Mailbox.SAMAccountName) has $($EASDeviceStats.Count) device(s)"
#Build the Array
   $ServerObj = New-Object PSObject
   $ServerObj | Add-Member NoteProperty -Name "Alias" -Value $Mailbox.SAMAccountName
   $ServerObj | Add-Member NoteProperty -Name "DeviceCount" -Value $EASDeviceStats.Count
   $Final_DeviceCount += $ServerObj   
$MailboxInfo = Get-Mailbox $Mailbox.Identity | Select DisplayName,PrimarySMTPAddress,Alias
Foreach ($EASDevice in $EASDeviceStats)
    {$LastSuccessSync = ($EASDevice.LastSuccessSync)
 if ($LastSuccessSync -eq $null)
        {
            $syncAge = "Never"
        }
        else
        {
            $syncAge = ($today - $LastSuccessSync).Days
        }
       $reportObj = New-Object PSObject
       $reportObj | Add-Member NoteProperty -Name "DisplayName" -Value $MailboxInfo.DisplayName
       $reportObj | Add-Member NoteProperty -Name "EmailAddress" -Value $MailboxInfo.PrimarySMTPAddress
       $reportObj | Add-Member NoteProperty -Name "Alias" -Value $MailboxInfo.Alias
       $reportObj | Add-Member NoteProperty -Name "SyncAgeInDays" -Value $syncAge
            Foreach ($stat in $stats)
            {
                $reportObj | Add-Member NoteProperty -Name $stat -Value $EASDevice.$stat
            }
            $report += $reportObj
        }    
}
$report | Export-csv $filename
$TotalDevices = $report.count
$TotalMailboxes = $MailboxesWithEASDevices.Count
$AvgPerMailbox = $TotalDevices / $TotalMailboxes
$LastSync180  = ($Report | Where SyncAgeInDays -ge $age).count
$Top10DeviceTypes = $Report | group DeviceType | Sort Count -Descending | Select Count,Name -first 10 | ConvertTo-Html -head $b
$Top10Users = $final_DeviceCount | Sort DeviceCount -Descending | Select Alias,DeviceCount -first 10 | ConvertTo-Html -head $b
$PolicyApplied = $report | Group DevicePolicyApplied | Sort Count -Descending | Select Count,Name | ConvertTo-Html -head $b
$body =""
$attachment = $filename
$body += "<Font color=black>Attached are the Mobile Device Statistics within the entire OnPremise Exchange Organization using ActiveSync.</font><br><br><br>"
$body += "<b><Font color=#00008B>Total devices:</b><Font color=black> $TotalDevices</font><br><br>"
$body += "<b><Font color=#00008B>Total mailboxes that have a device:</b><Font color=black> $TotalMailboxes</font><br><br>"
$body += "<b><Font color=#00008B>Average Devices Per User:</b><Font color=black> $AvgPerMailbox</font><br><br>"
$body += "<b><Font color=#00008B>Devices with last sync time older than $age days:</b><Font color=black> $LastSync180</font><br><br>"
$body += "<b><Font color=#00008B>ActiveSync policy applied on all mailboxes:</b> $PolicyApplied</font><br><br>"
$body += "<b><Font color=#00008B>Top 10 device types:</b></font><br><br>"
$body += "<Font color=black>$Top10DeviceTypes</font><br><br><br>"
$body += "<b><Font color=#00008B>Top 10 users by device count:</b></font><br><br>"
$body += "<Font color=#00008B>$Top10Users</font><br><br><br>"
send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Priority high -Attachments $attachment

#=========End Script===========