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