Welcome to Port3101.org : Your BES Connection Mark forums read | View Forum Leaders
Port3101.org : Your BES Connection



Reply
LinkBack Thread Tools Display Modes
Powershell code to query besdatabase
 
  #1 (permalink)  
Old 10-19-2009, 03:36 PM
BES Activated
 
Join Date: Jul 2009
Location: N/A
Posts: 9
Lightbulb 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] = '$user@some 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
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 12-03-2009, 03:20 PM
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 = "senderaddress@yourcompany.com"
$mailmessage.To.add("recipient@yourcomany.com")
$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
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Query - for BES - count users per groups coxis Scripts & Tools / Hints & Tricks 2 10-16-2012 08:47 AM
Sql Query Messages Sent and Availabilty gtjr92 Scripts & Tools / Hints & Tricks 0 07-18-2012 12:15 PM
Need a query to see if a user is syncing a public contact folder PSCArmstrSM Scripts & Tools / Hints & Tricks 1 02-16-2012 03:45 AM
Powershell - BES Licences Used mail script ScouT Scripts & Tools / Hints & Tricks 3 04-26-2011 10:49 AM


All times are GMT -4. The time now is 04:21 AM.
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.


 

SEO by vBSEO 3.3.2 PL2