ELMORE IT's Blog

This script is created to count how many folders exist in a NAS Share. It loads the list from SQL Server and writes the result back to the same table. You can modify it to your own needs.

# **********************************************
# Created by Sébastien Morel
# http://blog.elmore.be
# Last update: August 26th, 2010
# Version 1.00
#
# This script will loop NAS Folders
# and return number of homefolders per share
# **********************************************

# retrieve NAS Folders
$sqlsvr = '<MySQLServer>'
$database = '<MySQLDB>'
$table = 'tbl_homeshares_trees'

Clear-Host
Write-Host "Please wait a moment, this can take a while..."
Write-Host " "

#Create SQL Connection
Write-Verbose "Creating SQL Connection..."
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI")
$conn.Open()
$conn2 = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI")
$conn2.Open()

$cmd = $conn.CreateCommand()
$cmd2 = $conn2.CreateCommand()

$cmd.CommandText = "select tree_id, tree_path from $table"
$Reader = $cmd.ExecuteReader()

while ($Reader.Read()) {
	$sID = $Reader["tree_id"].Tostring()
	$sPath = $Reader["tree_path"].Tostring()
	Write-Host "Querying $sPath..."
	$sGo = Get-ChildItem $sPath
	# update table
	$cmd2.CommandText = "UPDATE $table SET tree_count = " + $sGo.Count + " WHERE tree_id = '" + $sID + "'"
	$cmd2.ExecuteNonQuery() | Out-Null
}

$conn.Close()
$conn2.Close()
$Reader.Close()

The results in the database

A client of me wants to automate the creation of exchange mailboxes but the load needs to be spread among the 5 exchange servers. Each server has 4 storegroups and 4 stores making a total of 80 locations. So I wrote a PowerShell script that queries each Exchange Server and counts mailboxes per store. This job will run each night and the results are written to a SQL Server database (company policy)

The configuration is easy and is located at the top of the script. You can choose to send the results to the screen or to SQL Server

$tempfile = "Temp.csv"
$exportfile = "Exchange_stores.txt"
$exportcsv = "Exchange.csv"
$output = "screen"; # sql - screen

Decide if you want to load a serverlist from a text-file or from the script

# Get list from array or txt-file (un-comment what you want)
# -----------------------------------------------------------
$exServers = "Dummy1","Dummy2"
#$exServers = Get-Content ExchangeServers.txt

SQL-Server Configuration

# SQL Server configuration (only needed when output = sql)
# -----------------------------------------------------------
$sqlsvr = '<mysqlserver>'
$database = '<mysqldatabase>'
$table = 'tbl_exchange_servers'

The Create-table script is included and looks like this

CREATE TABLE [dbo].[tbl_exchange_servers](
	[server_id] [int] IDENTITY(1,1) NOT NULL,
	[server_name] [varchar](100) NOT NULL,
	[server_storegroup] [varchar](100) NOT NULL,
	[server_storename] [varchar](100) NOT NULL,
	[server_mailboxcount] [int] NOT NULL
) ON [PRIMARY]

The output to the screen will look like this

Download: ExchangeStats.zip

*Update*
v1.03: fixes a bug for the export to SQL Server where the variables contained an unwanted space

All comments are welcome 🙂

Full Script

# **********************************************
# Created by Sébastien Morel
# http://blog.elmore.be
# Last update: August 24th, 2010
# Version 1.03
#
# This script will loop exchange servers
# and return number of mailboxes per store
# **********************************************

$tempfile = "Temp.csv"
$exportfile = "Exchange_stores.txt"
$exportcsv = "Exchange.csv"
$output = "screen"; # sql - screen

# Get list from array or txt-file (un-comment what you want)
# -----------------------------------------------------------
$exServers = "Dummy1","Dummy2"
#$exServers = Get-Content ExchangeServers.txt

# SQL Server configuration (only needed when output = sql)
# -----------------------------------------------------------
$sqlsvr = '<mysqlserver>'
$database = '<mysqldatabase>'
$table = 'tbl_exchange_servers'

Clear-Host
Write-Host "Please wait a moment, this can take a while..."
Write-Host " "

# Delete old output file when needed
if ([System.Io.File]::Exists($tempfile)) { [System.IO.File]::Delete($tempfile) }
if ([System.Io.File]::Exists($exportfile)) { [System.IO.File]::Delete($exportfile) }
if ([System.Io.File]::Exists($exportcsv)) { [System.IO.File]::Delete($exportcsv) } 

foreach ($server in $exServers) {
	Write-Host "Querying $server ..."
	$mbx = get-wmiobject -class Exchange_Mailbox -namespace RootMicrosoftExchangeV2 -computername $server
	$mbx | Group-Object StoreName,StorageGroupName,Servername -NoElement | Sort-Object Count -Descending | Export-Csv -NoType $tempfile -Delimiter (';')
	[System.IO.File]::ReadAllText($tempfile) | Out-File $exportfile -Append -Encoding Unicode
}

Write-Host " "
If ($output -eq "sql"){
	Write-Host "Importing results to SQL Server..."
	Write-Host -Fore Green "Updating Table"
	$myCollection = @()
}

# remove blank lines from the export-file
(get-content $exportfile) -replace '"','' | where {$_ -ne ""} | out-file $exportfile
# import results to SQL Server
$sr = [System.Io.File]::OpenText($exportfile)
$s = $sr.ReadLine()
While ($s) {
	$s = $sr.ReadLine()
	If ($s -ne $null -and $s.Contains("ArrayList")){
		$val = $s.split(";")
		$numMailbox = $val[1]
		$names = $val[3].split(",")
		$sStore = $names[0].Trim()
		$sStorageGroup = $names[1].Trim()
		$sServerName = $names[2].Trim()
		If ($output -eq "screen"){
			write-host $sServerName $sStorageGroup $sStore : $numMailbox
		} else {
			$myobj = "" | select Server,StoreGroup,StoreName,MailboxCount
			$myobj.Server = $sServerName
			$myobj.StoreGroup = $sStorageGroup
			$myobj.StoreName = $sStore
			$myobj.MailboxCount = $numMailbox
			$myCollection += $myobj
		}
	}
}
If ($output -eq "sql"){
	$myCollection | Export-Csv $exportcsv -notype
	#Create SQL Connection
	Write-Verbose "Creating SQL Connection..."
	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;
	Initial Catalog=$database; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()

	#truncate current table
	$cmd.CommandText = "TRUNCATE TABLE $table"
	$cmd.ExecuteNonQuery() | Out-Null

	Import-Csv .$exportcsv | % {
	#$_.Server
	#$_.StoreGroup
	#$_.StoreName
	#$_.MailboxCount

	#Create query string
	#Must matching the table layout (server_name, server_storegroup, server_storename, server_mailboxcount)
	$cmd.CommandText = "INSERT INTO $table (server_name, server_storegroup, server_storename, server_mailboxcount) VALUES ('$($_.Server)','$($_.StoreGroup)','$($_.StoreName)','$($_.MailboxCount)')"

	#Execute Query
	$cmd.ExecuteNonQuery() | Out-Null
	}
}

[system.Io.File]::Delete($tempfile)
$sr.close()
[system.Io.File]::Delete($exportfile)
[system.Io.File]::Delete($exportcsv)

Write-Host "Finished!"