by jagan at 2012-11-20 17:15:52
Gurus, I want to collect Get-Counters from remote machines and dump that data into sql table. can you please tell me how to achieve this task.by Infradeploy at 2012-11-21 01:34:43
Please respond to this thread at your earliest convenience.$SQLCon.close()
foreach ($row in $DataSet.tables["ServerName"].rows)
{
$DestinationServerName = $row.ServerName
$DestinationServerName
$GetCountersData = Get-Counter -Counter(Get-Counter -computername $DestinationServerName -ListSet PhysicalDisk).PathsWithInstances | Format-table -Auto $CapturedPerfmonData
#$GetCountersData.CounterSamples
foreach ($Counter in $CapturedPerfmonData)
{
$Path = $Counter.Path
$Instancename = $Counter.InstanceName
$CookedValue = $Coutner.CookedValue
<#$dbconn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LAIW2K8Db132P; Initial Catalog=LZDBMonitor; Integrated Security=SSPI")
$dbconn.Open()
$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = "INSERT INTO ServerOSPhysicalDiskCounterValues ([ServerName] , [CounterPath] , [InstanceName] , [CookedValue] ) VALUES ($DestinationServerName, $Path, $InstanceName, $Cookedvalues)"
$dbwrite.ExecuteNonQuery()
$dbconn.Close()#>
$Path
$Instancename
$CookedValue
}
}
The invoke-sql cmdlet is loads easier.by Klaas at 2012-11-21 03:24:13
You get that after installing the SQL management tools
Invoke-Sqlcmd would indeed be easier,by jagan at 2012-11-24 11:14:30
But it is possible your way, if
- you lose the Format cmdlet
- you use the correct variable names: $counter != $coutner, $cookedvalue != $cookedvalues
Try to debug or build your script in the shell step by step and find the step where it fails.
klass, I tried using your suggestion. I am certainly doing something wrong… can you please help me with an example code which I can use it as a guideline to build my code?by jagan at 2012-11-24 19:38:50
Thanks in advance!!
FYI… this is what I wrote:by Klaas at 2012-11-25 10:31:25#Set-ExecutionPolicy RemoteSigned
###########################################################################
# Declare ServerName, DatabaseName and TableName to Get Server List
###########################################################################
$SourceServerName = 'LAIW2k8DB132P'
$SourceDatabaseName = 'LZDBMonitorMaster'
#$SourceTablename = 'LookUp_ServerList_NonProd'
$DestinationTableName = 'CollectServerPerformanceCountersPS'
###########################################################################
# Declare Variables for Sending E-mails
###########################################################################
$ToRecipient = "@email.com"
$From = "@email.com"
$SMTPServer = "smtp.email.com"
$GetDate = get-date -format g
##########################################################################
# Function to Dump Data from Get-Counters to DataTable
##########################################################################
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject]$InputObject)
Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
if ($property.value)
{
if ($property.value -isnot [System.DBNull])
{ $Col.DataType = $property.value.gettype() }
}
$DT.Columns.Add($Col)
}
if ($property.IsArray)
{ $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
else { $DR.Item($property.Name) = $property.value }
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}
}
##########################################################################
# Function to Dump Data from DataTable to SQl Table
##########################################################################
function Write-DataTable
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$true)] [string]$Database,
[Parameter(Position=2, Mandatory=$true)] [string]$TableName,
[Parameter(Position=3, Mandatory=$true,ValueFromPipeline = $true,ValueFromPipelinebyPropertyname = $true) ] $Data,
[Parameter(Position=4, Mandatory=$false)] [string]$Username,
[Parameter(Position=5, Mandatory=$false)] [string]$Password,
[Parameter(Position=6, Mandatory=$false)] [Int32]$BatchSize=50000,
[Parameter(Position=7, Mandatory=$false)] [Int32]$QueryTimeout=0,
[Parameter(Position=8, Mandatory=$false)] [Int32]$ConnectionTimeout=15
)
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString=$ConnectionString
try
{
$conn.Open()
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.BatchSize = $BatchSize
$bulkCopy.BulkCopyTimeout = $QueryTimeOut
$bulkCopy.WriteToServer($Data)
$conn.Close()
}
catch
{
$ex = $_.Exception
Write-Error "$ex.Message"
continue
}
} #Write-DataTable
###########################################################################
# Create SqlConnection object and define connection string
###########################################################################
$SQLCon = New-Object System.Data.SqlClient.SqlConnection
$SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"
###########################################################################
# Create SqlCommand object, define command text, and set the connection
###########################################################################
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand
$SQLCmd.CommandText = "SELECT ServerName
FROM [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
WHERE [SqlPingFlag] = 1"
$SQLCmd.Connection = $SQLCon
###########################################################################
# Create SqlDataAdapter object and set the command
###########################################################################
$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlDataAdapter.SelectCommand = $SQLCmd
###########################################################################
# Create and fill the DataSet object
###########################################################################
$DataSet = New-Object System.Data.DataSet
$SqlDataAdapter.Fill($DataSet, "ServerName") | Out-Null
###########################################################################
# Close the connection
###########################################################################
$SQLCon.close()
###########################################################################
# Loop Through Each Row in Data Set and Dump to SQL Table
###########################################################################
foreach ($row in $DataSet.tables["ServerName"].rows)
{
$DestinationServerName = $row.ServerName
$DestinationServerName
$GetCounterData = Get-Counter -Counter(Get-Counter -computername $DestinationServerName -ListSet PhysicalDisk).PathsWithInstances
$GetCounterData.CounterSamples | Select $DestinationServerName, Path, Instancename, CookedValue | Out-DataTable | Write-DataTable -ServerInstance $SourceServerName<br> -Database $SourceDatabaseName
-TableName $DestinationTableName `
}
I think you mixed up the SQL SELECT and Powershell Select-Object. When you pipe countersamples to Select-Object, there is no $DestinationServerName. You can find that if you executePS> $GetCounterData = Get-Counter -Counter(Get-Counter -computername localhost -ListSet PhysicalDisk).PathsWithInstancesin the shell.
PS> $GetCounterData.CounterSamples
I would keep Chad’s functions in a separate script and dotsource that in your script. There are already 7 versions of those functions. On the next change, you would only have to replace that file and not adapt every script where you ever used out-datatable or write-datatable.
If you would have the SQLPS module you could replace about 130 lines of your script with Invoke-Sqlcmd. If not, you’re on the right track. On first glance I think you’re doing pretty good with the .Net objects sqlconnection, sqladapter, dataset except for the Select $Destinationservername.Select @{l='server';e={$DestinationServerName}}, Path, Instancename, CookedValuemight do the trick.
Besides that it’s rather hard to troubleshoot if we don’t know what errors you get or which part of the script fails.
Also, be sure to download Chad’s SQLPSX module, which has invoke-sqlcmd2 and perfcounter cmdlets in it: http://sqlpsx.codeplex.com/