In my below PowerShell I am getting all first level folders in a UNC path, and make a calculation of each of these folders count of files, folders and size in MB, which I need written into a SQL Server table, but this gives me some headaches on writing the values to the SQL string.
I have 3 values that I need to write to the SQL which are:
- $TotalFileCount
- $TotalDirectoryCount
- $TotalFolderSizeInMB’
I have tried to write them out like:
- $TotalFileCount
- $($TotalFileCount)
- $($TotalFileCount.tostring()
- $($TotalFileCount.value.tostring())
But none of them returns any data when I make a Write-Output
, so I expect that it is because $TotalFileCount
is a calculation, but any effort to find a solution to write this out have been effortless, so I need some help to find a solution.
My PS script:
$SQLServer = "SQL_SERVER"
$SQLDatabase = "DATA_BASE"
#SQL function
function Invoke-SQL {
param(
[string] $sqlCommand = $(throw "Please specify a query.")
)
$connectionString = "Data Source=$SQLServer; " +
"Integrated Security=SSPI; " +
"Initial Catalog=$SQLDatabase"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$connection.Close()
$dataSet.Tables
}
Function Get-DirectoryTreeSize {
[CmdletBinding(DefaultParameterSetName="Default")]
param(
[Parameter(
Position = 0,
Mandatory = $true
)]
[string] $Path,
[Parameter(
Mandatory = $false,
ParameterSetName = "ShowRecursive"
)]
[switch] $Recurse,
[Parameter(
Mandatory = $false,
ParameterSetName = "ShowTopFolderAllItemsAndAllFolders"
)]
[switch] $AllItemsAndAllFolders
)
BEGIN {
#Adding a trailing slash at the end of $path to make it consistent.
if (-not $Path.EndsWith('\')) {
$Path = "$Path\"
}
}
PROCESS {
try {
if (-not $PSBoundParameters.ContainsKey("AllItemsAndAllFolders") -and -not $PSBoundParameters.ContainsKey("Recurse")) {
$FileStats = Get-ChildItem -Path $Path -File -ErrorAction Stop | Measure-Object -Property Length -Sum
$FileCount = $FileStats.Count
$DirectoryCount = Get-ChildItem -Path $Path -Directory | Measure-Object | select -ExpandProperty Count
$SizeMB = "{0:F3}" -f ($FileStats.Sum / 1MB) -as [decimal]
[PSCustomObject]@{
Path = $Path
FileCount = $FileCount
DirectoryCount = $DirectoryCount
FolderSizeInMB = $SizeMB
}
}
if ($PSBoundParameters.ContainsKey("AllItemsAndAllFolders")) {
$FileStats = Get-ChildItem -Path $Path -File -Recurse -ErrorAction Stop | Measure-Object -Property Length -Sum
$FileCount = $FileStats.Count
$DirectoryCount = Get-ChildItem -Path $Path -Directory -Recurse | Measure-Object | select -ExpandProperty Count
$SizeMB = "{0:F3}" -f ($FileStats.Sum / 1MB) -as [decimal]
[PSCustomObject]@{
Path = $Path
TotalFileCount = $FileCount
TotalDirectoryCount = $DirectoryCount
TotalFolderSizeInMB = $SizeMB
}
}
if ($PSBoundParameters.ContainsKey("Recurse")) {
Get-DirectoryTreeSize -Path $Path
$FolderList = Get-ChildItem -Path $Path -Directory -Recurse | select -ExpandProperty FullName
if ($FolderList) {
foreach ($Folder in $FolderList) {
$FileStats = Get-ChildItem -Path $Folder -File | Measure-Object -Property Length -Sum
$FileCount = $FileStats.Count
$DirectoryCount = Get-ChildItem -Path $Folder -Directory | Measure-Object | select -ExpandProperty Count
$SizeMB = "{0:F3}" -f ($FileStats.Sum / 1MB) -as [decimal]
[PSCustomObject]@{
Path = $Folder
FileCount = $FileCount
DirectoryCount = $DirectoryCount
FolderSizeInMB = $SizeMB
}
#clearing variables
$null = $FileStats
$null = $FileCount
$null = $DirectoryCount
$null = $SizeMB
}
}
}
} catch {
Write-Error $_.Exception.Message
}
}
END {}
}
foreach ($name in Get-ChildItem "\\FILSRV01\home dir$"| Where { $_.PSIsContainer } | Where { $_.PSIsContainer } | Select-Object name)
{
$TopPath = "\\FILSRV01\home dir$\"
$TempPath = Join-Path $TopPath $name."name"
Get-DirectoryTreeSize $TempPath -AllItemsAndAllFolders
$sqlSelectCommand = "SELECT * from EFP_HomeFolder_Stats where UserInitials = '$($name."name")'"
$sqlInsertCommand = "INSERT INTO EFP_HomeFolder_Stats (Path,UserInitials,TotalFileCount,TotalDirectoryCount,TotalFolderSizeInMB,LastUpdated) VALUES('$TempPath','$($name."name")','$($TotalFileCount)','$TotalDirectoryCount','$TotalFolderSizeInMB',GETDATE())"
$sqlUpdateCommand = "UPDATE EFP_HomeFolder_Stats SET Path = '$TempPath', UserInitials = '$($name."name")', TotalFileCount = '$TotalFileCount', TotalDirectoryCount = '$TotalDirectoryCount', TotalFolderSizeInMB = '$TotalFolderSizeInMB', LastUpdated = GETDATE()"
Invoke-SQL -sqlCommand $sqlSelectCommand
Write-host $name.ExistCoun
Invoke-SQL -sqlCommand "
if exists ($sqlSelectCommand)
BEGIN
$sqlUpdateCommand
End
else
BEGIN
$sqlInsertCommand
END
"
}