by toybits at 2013-04-03 05:18:13
Hi all,by cmille19 at 2013-04-03 07:58:15
Not sure where to start here as I’m not a SQL person.
I’m producing out output with Exchange Server Name and Number of mailboxes.
It just creates a variable called $output that looks like this.
Name MailboxCount
---- ------------
Database1 162
Database2 167
Database3 178
Database4 183
Database5 185
Database6 185
We’ve actually got 144DB’s.
I then need to insert it into a SQL table in a database. This is where I’m a bit clueless. I’m googling my little heart out but any advice would be very appreciated.
#Get a list of mailbox databases as well as ‘Status’ information
$Databases = Get-MailboxDatabase UK-MBX-000* -Status
#Count each database mailboxes
$output = $Databases | select Name,@{n=‘MailboxCount’;e={(Get-Mailbox -Database $.Name).Count}} | Sort-Object MailboxCount
#Connect to the SQL database
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=MYSQLServer;Database=MySQLDB;trusted_connection=true;"
One way is to loop through your output and call an insert command:by toybits at 2013-04-03 08:25:16#Connect to the SQL database
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=MYSQLServer;Database=MySQLDB;trusted_connection=true;"
$Conn.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Conn
$output | foreach {
#write-host "INSERT INTO MySQLTable (Name,MailboxCount) VALUES ('$($.Name)',$($.MailboxCount))"
$Command.CommandText = "INSERT INTO MySQLTable (Name,MailboxCount) VALUES ('$($.Name)',$($.MailboxCount))"
$Command.ExecuteNonQuery() | out-null
}
$Conn.Close()
Hi,by toybits at 2013-04-03 08:30:13
Just came on to make a reply and it looks like I got pretty close to what you’ve done thanks cmile19. Only thing I’m doing is to export it to a csv first. We want to keep a copy of each import we give to the SQL team. I’m not sure what the | Out-Null does at the end though?
Thanks for your reply.
#Get a list of mailbox databases as well as ‘Status’ information
$Databases = Get-MailboxDatabase MBX* -Status
#Count each database mailboxes
$output = $Databases | select Name,@{n=‘MailboxCount’;e={(Get-Mailbox -Database $.Name).Count}} | Sort-Object MailboxCount
#Export the Output to CSV for our keeping
$csvFile = "c:\ScottTools\Exports\MBCountByDatabase.csv"
$output | Export-Csv $csvFile -NoTypeInformation
#Connect to the SQL database
$sqlsvr = "SQLServerName.uk.dev.local"
$database = "DatabaseName"
$table = "exchangeMailStores"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI"
$conn.Open()
$cmd = $conn.CreateCommand()
Import-Csv $csvFile | %{
$cmd.CommandText = "INSERT INTO $table (homemdb, availableCount) VALUES (‘$($.Name)‘,’$($.MailboxCount)’)"
#Execute Query
$cmd.ExecuteNonQuery()
}
Oh one other thing, any pointers on how I clear the table before adding the new data?by cmille19 at 2013-04-03 08:59:36
It’s giving me an exception error as the homeMDB column is set to primary key. We can take that off I know but was curious.
Thanks again.
The out-null is used because a call to ExecuteNonQuery returns the number of rows affected. Since I don’t need our want the output I’ll send it to null:by toybits at 2013-04-04 00:45:14
$Command.ExecuteNonQuery() | out-null
Other variants of sending things to null you may see in scripts (although out-null is used more in Powershell scripting):
$null = $Command.ExecuteNonQuery()
$Command.ExecuteNonQuery() > $null
You could just delete:
Connect to the SQL database$sqlsvr = "SQLServerName.uk.dev.local"
$database = "DatabaseName"
$table = "exchangeMailStores"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI"
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "DELETE $table"
#Execute Query
$cmd.ExecuteNonQuery()
Ahh OK understood. That’s wicked thanks for your help cmille19