April 5, 2011 at 12:47 pm
I am currently running a Powershell script on eight different servers getting file counts and sizes of a couple of directories. Right now I am saving the output to a log file (CSV) and the results from each site to a summary log file. I would like to change this to store results to a central database table. I am having a tough time finding the code samples I need to connect to a table and store the information. If anyone would provide guidance or assistance that would be great. I have copied my script below.
################################################################################
#WordRad Check
################################################################################
$StartFolder = "\\server1\d$\Results"
$LogFileDate = (Get-Date).tostring("MMddyyyy")
$DetailLogFile = "\\MainServer\C$\PowerShell\Logs\Images\BYDB_" + $LogFileDate + "_FolderCountSizeLog_Detail.txt"
$SummaryLogFile = "\\MainServer\C$\PowerShell\Logs\Images\" + $LogFileDate + "_FolderCountSizeLog_Summary.txt"
$GrandTotalCount = 0
$GrandTotalSize = 0
#cls
$colItems = (Get-ChildItem $startFolder | Where-Object {$_.PSIsContainer -eq $True} | Sort-Object -ErrorAction SilentlyContinue)
foreach ($i in $colItems)
{
$subFolderItems = (Get-ChildItem $i.FullName | Measure-Object -property length -sum -ErrorAction SilentlyContinue)
$count = @((Get-ChildItem $i.FullName -force | where {$_.length -ge 0} )).Count
$totalsize = ($subFolderItems.sum)
$output_final = $i.FullName + ",",$totalsize + " MB,",$count
$output_final | Out-File -Append $DetailLogFile -Encoding ASCII -ErrorAction SilentlyContinue
$GrandTotalSize += $TotalSize
$GrandTotalCount += $Count
}
$output_final = $startFolder + " -- Total Size: " + [math]::round($GrandTotalSize / 1GB, 2) + " GB " + "Total File Count: " + $GrandTotalCount
$output_final | Out-File -Append $SummaryLogFile -Encoding ASCII -ErrorAction SilentlyContinue
May 11, 2011 at 1:14 pm
This may help you: http://poshcode.org/2276
July 22, 2011 at 9:02 am
Hi
I tried the following and seems to be working. You can tweak the code for your requirement , need to change the servername , databasename, tablename. I picked this code from "Loading Data With Powershell By Chad Miller, 2011/05/13"
from SQL SERVER CENTRAL.com
Thanks Chad....!!!!
********************************************************************
Function out-DataTable {
$dt = new-object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
if ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
elseif ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
return @(,($dt))
}
$dataTable = ls c:\temp -r *.bak | Sort -desc lastwriteTime | SELECT Directory,Name,LastWriteTime | out-DataTable
$connectionString = "Data Source=MyDBServerName;Integrated Security=true;Initial Catalog=MyDatabase;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "MyTable"
$bulkCopy.WriteToServer($dataTable)
*****************************************************************************************
July 22, 2011 at 9:03 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply