View Single Post
  #2 (permalink)  
Old 12-03-2009, 03:20 PM
EllisLub EllisLub is offline
BES Activated
 
Join Date: Jul 2009
Location: Philadelphia
Posts: 9
Send a message via AIM to EllisLub Send a message via Skype™ to EllisLub
Default

I recently had an issue where several handhelds had the Calendar Sync option turned off. There's no way to set it to "ON" via Policy and I had no idea how many were in this state beyond those who called our Help Desk. I called RIM and they weren't much help but I got a Query from HDawg and put it in a Powershell Script to find all of our affected handhelds and email the results to me and the helpdesk each morning. This was tested against a BES 5.0 DB. I thought i'd share it here:

$SmtpClient = new-object system.net.mail.smtpClient
$MailMessage = New-Object system.net.mail.mailmessage
$SmtpClient.Host = "yoursmtpserver.com"
$mailmessage.from = "[email protected]"
$mailmessage.To.add("[email protected]")
$mailmessage.Subject = “Users With Calendar Sync set to OFF"

$SQLSERVER="SQLServerName"
$Database="BESMgmt"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString =
"Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT U.DisplayName, U.MailboxSMTPAddr FROM UserConfig U, CalStateFlagV C WHERE C.UserConfigId = U.Id AND CalStateFlag = '0'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | format-table -auto
$content = ($DataSet.Tables[0] | Sort-Object DisplayName | format-table DisplayName | out-string)
$mailmessage.Body = $content
$smtpclient.Send($mailmessage)

Last edited by EllisLub; 12-03-2009 at 03:21 PM. Reason: grammar
Reply With Quote