Port3101.org : Your BES Connection

Port3101.org : Your BES Connection (http://www.port3101.org/index.php)
-   Scripts & Tools / Hints & Tricks (http://www.port3101.org/forumdisplay.php?f=12)
-   -   Powershell code to query besdatabase (http://www.port3101.org/showthread.php?t=2037)

habs3 10-19-2009 03:36 PM

Powershell code to query besdatabase
 
$dbServer = "database server"
$db = "besmgmt"
$user = Read-Host "Enter User NT Logon name"
$Query = "SELECT DisplayName, Pin,
case ServerConfigID when 2 then 'enter server name'
when 3 then 'enter second server name'
when 4 then 'enter another server if there is one'
end 'BES Server', AgentID,
substring (ServerDN,45,9) 'Exchange Server' from [besmgmt].[dbo].[USERConfig] where [MailboxSMTPAddr] = '[email protected] company.com'"
#$Query = "SELECT * from [besmgmt].[dbo].[USERstats]"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbServer;Database=$db;Integrated Security=True"
$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()
$DataSet.Tables[0] | Out-File c:\nfo.txt
Invoke-Item c:\nfo.txt

EllisLub 12-03-2009 03:20 PM

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)


All times are GMT -4. The time now is 05:05 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2019, vBulletin Solutions, Inc.


SEO by vBSEO 3.3.2 PL2